Untitled

SELECT
  channel.id as channelId,
  subscribed_channel.start AS S_start,
  subscribed_channel.end AS S_end,
  channel_configuration.id AS CC_id,
  channel_configuration.position AS CC_position,
  channel_configuration.favorite AS CC_favorite,
  channel_configuration.hidden AS CC_hidden,
  channel_representation.id AS CHR_id,
  content_representation.contentType AS CHR_contentType,
  content_representation.url AS CHR_url,
  channel_representation.definition AS CHR_definition,
  content_representation.contentProviderId AS CHR_contentProviderId,
  content_profile.profileId AS CHR_profileId,
  1 AS CHR_source
  /* SUBSCRIPTION */
FROM
  (
    SELECT
      /* channels from subscriber_channel */
      channelId,
      start,
      end
    FROM
      subscriber_channel
      LEFT JOIN channel ON subscriber_channel.channelId = channel.id
    WHERE
      subscriber_channel.subscriberId = #{subscriberId} AND
      channel.zoneId = #{zoneId} AND (
      (
        "subscriber_channel"."start" IS NULL
        OR "subscriber_channel"."start" & lt;= NOW()
      )
      AND (
        "subscriber_channel"."end" IS NULL
        OR "subscriber_channel"."end" & gt;= NOW()
      )
  )
UNION
SELECT
  /* channels from subscriber_channel_group */
  channelId,
  start,
  end
FROM
  channel_group_membership AS cgm
  JOIN subscriber_channel_group ON cgm.channelGroupId = subscriber_channel_group.channelGroupId
  AND subscriber_channel_group.subscriberId = #{subscriberId}
  JOIN channel_group ON subscriber_channel_group.channelGroupId = channel_group.id
  AND channel_group.zoneId = #{zoneId}
WHERE
  (
    (
      "subscriber_channel_group"."start" IS NULL
      OR "subscriber_channel_group"."start" & lt;= NOW()
    )
    AND (
      "subscriber_channel_group"."end" IS NULL
      OR "subscriber_channel_group"."end" & gt;= NOW()
    )
  )
UNION
SELECT
  /* channels from package_channel */
  channel.id AS channelId,
  CASE subscription_package_channel.start WHEN 0 THEN NULL WHEN 1 THEN subscriber_subscription_package.start END AS "start",
  CASE subscription_package_channel.end WHEN 0 THEN NULL WHEN 1 THEN subscriber_subscription_package.end END AS "end"
FROM
  subscriber_subscription_package
  JOIN subscription_package ON subscriber_subscription_package.subscriptionPackageId = subscription_package.id
  JOIN subscription_package_channel ON subscriber_subscription_package.subscriptionPackageId = subscription_package_channel.subscriptionPackageId
  JOIN channel ON subscription_package_channel.channelId = channel.id
  AND channel.zoneId = #{zoneId}
WHERE
  subscriber_subscription_package.subscriberId = #{subscriberId} AND
  subscriber_subscription_package.state = 2
  /* ACTIVATED */
  AND subscription_package."type" = 1
  /* SUBSCRIPTION */
HAVING
  (
    (
      "start" IS NULL
      OR "start" & lt;NOW()
    )
    AND (
      "end" IS NULL
      OR "end" & gt;= NOW()
    )
  )
UNION
SELECT
  /* channels from package_channel_group */
  channel_group_membership.channelId,
  CASE subscription_package_channel_group.start WHEN 0 THEN NULL WHEN 1 THEN subscriber_subscription_package.start END AS "start",
  CASE subscription_package_channel_group.end WHEN 0 THEN NULL WHEN 1 THEN subscriber_subscription_package.end END AS "end"
FROM
  subscriber_subscription_package
  JOIN subscription_package ON subscriber_subscription_package.subscriptionPackageId = subscription_package.id
  JOIN subscription_package_channel_group ON subscriber_subscription_package.subscriptionPackageId = subscription_package_channel_group.subscriptionPackageId
  JOIN channel_group ON subscription_package_channel_group.channelGroupId = channel_group.id
  AND channel_group.zoneId = #{zoneId}
  JOIN channel_group_membership ON channel_group.id = channel_group_membership.channelGroupId
WHERE
  subscriber_subscription_package.subscriberId = #{subscriberId} AND
  subscriber_subscription_package.state = 2
  /* ACTIVATED */
  AND subscription_package."type" = 1
  /* SUBSCRIPTION */
HAVING
  (
    (
      "start" IS NULL
      OR "start" & lt;NOW()
    )
    AND (
      "end" IS NULL
      OR "end" & gt;= NOW()
    )
  )
) AS subscribed_channel
JOIN channel ON subscribed_channel.channelId = channel.id
LEFT JOIN channel_configuration ON channel.id = channel_configuration.channelId
AND channel_configuration.userId = #{userId}
LEFT JOIN channel_representation ON channel_representation.channelId = channel.id
LEFT JOIN content_representation ON channel_representation.contentRepresentationId = content_representation.id
LEFT JOIN content_profile ON content_representation.id = content_profile.representationId
WHERE
  content_profile.profileId = #{profileId} AND
  channel.disabled = 0