stories_filter.sql

EXPLAIN ANALYSE (
SELECT
    stories.id,
    stories.slug,
    stories.title,
    stories.image,
    stories.date,
    stories.updated_at,
    CAST(users_favorite_stories.created_at IS NOT NULL AS boolean) AS is_favorite,
    stories.languages,
    stories.subscription_levels,
    stories.views_count,
    stories.rating_count,
    stories.rating_result,
    (
        SELECT
            rating
        FROM
            users_rating_stories
        WHERE
            users_rating_stories.user_id = '1979fa3a-c2be-4ba7-8b91-3658ef2b0b33'
            AND users_rating_stories.story_id = stories.id
        LIMIT 1) AS user_rating,
(
    SELECT
        COALESCE(array_agg(DISTINCT stories_authors.author_id), ARRAY[]::uuid[])
    FROM
        stories_authors
    WHERE
        stories_authors.story_id = stories.id) AS authors,
(
    SELECT
        COALESCE(array_agg(DISTINCT stories_categories.category_id), ARRAY[]::uuid[])
    FROM
        stories_categories
    WHERE
        stories_categories.story_id = stories.id) AS categories,
(
    SELECT
        type
    FROM
        highlights
    WHERE
        highlights.story_id = stories.id
    LIMIT 1) AS highlight_type,
(
    SELECT
        COALESCE(array_agg(DISTINCT stories_related.related_story_id), ARRAY[]::uuid[])
    FROM
        stories_related
    WHERE
        stories_related.story_id = stories.id) AS related_stories,
(
    SELECT
        json_agg(json_build_object('id', promos.id, 'type', 'link', 'badge', json_build_object('type', promos.badge_type), 'link', promos.link, 'image', promos.image, 'title', promos.title, 'subtitle', promos.subtitle))
    FROM
        promos
    WHERE
        stories.id = promos.story_id
        AND promos.entity_type = 'stories') AS details_promos,
main_track.id,
main_track.title,
main_track.image,
main_track.image_author,
main_track.authors,
main_track.subscription_levels,
main_track.duration,
main_track.file_size_value,
main_track.file_size_unit,
main_track.is_listened,
override_track.id,
override_track.title,
override_track.image,
override_track.image_author,
override_track.authors,
override_track.subscription_levels,
override_track.duration,
override_track.file_size_value,
override_track.file_size_unit,
override_track.is_listened
FROM
    stories
    LEFT JOIN users_favorite_stories ON users_favorite_stories.story_id = stories.id
        AND users_favorite_stories.user_id = '1979fa3a-c2be-4ba7-8b91-3658ef2b0b33'
    LEFT JOIN (
        SELECT
            stories_tracks.story_id AS story_id,
            stories_tracks.is_main AS is_main,
            tracks.id AS id,
            tracks.title AS title,
            tracks.type AS type,
            tracks.image AS image,
            tracks.image_author AS image_author,
            tracks.text_full AS text_full,
            tracks.text_preview AS text_preview,
            tracks.duration AS duration,
            tracks.estimated_time_reading AS estimated_time_reading,
            tracks.source_link AS source_link,
            tracks.link_external AS link_external,
            tracks.file_size_value AS file_size_value,
            tracks.file_size_unit AS file_size_unit,
            tracks.subscription_levels AS subscription_levels,
            CAST(users_mark_tracks.user_id IS NOT NULL AS boolean) AS is_read,
            CAST(users_mark_tracks.user_id IS NOT NULL AS boolean) AS is_listened,
            (
                SELECT
                    array_agg(tracks_authors.author_id)
                FROM
                    tracks_authors
                WHERE
                    tracks_authors.track_id = tracks.id) AS authors
            FROM
                tracks
                JOIN stories_tracks ON tracks.id = stories_tracks.track_id
                LEFT JOIN users_mark_tracks ON users_mark_tracks.track_id = tracks.id
                AND users_mark_tracks.user_id = '1979fa3a-c2be-4ba7-8b91-3658ef2b0b33'
            WHERE
                tracks.disabled = FALSE
                AND stories_tracks.is_main = TRUE
                AND tracks.type = 'audio') AS main_track ON main_track.story_id = stories.id
    LEFT JOIN (
        SELECT
            stories_tracks.story_id AS story_id,
            stories_tracks.is_main AS is_main,
            tracks.id AS id,
            tracks.title AS title,
            tracks.type AS type,
            tracks.image AS image,
            tracks.image_author AS image_author,
            tracks.text_full AS text_full,
            tracks.text_preview AS text_preview,
            tracks.duration AS duration,
            tracks.estimated_time_reading AS estimated_time_reading,
            tracks.source_link AS source_link,
            tracks.link_external AS link_external,
            tracks.file_size_value AS file_size_value,
            tracks.file_size_unit AS file_size_unit,
            tracks.subscription_levels AS subscription_levels,
            CAST(users_mark_tracks.user_id IS NOT NULL AS boolean) AS is_read,
            CAST(users_mark_tracks.user_id IS NOT NULL AS boolean) AS is_listened,
            (
                SELECT
                    array_agg(tracks_authors.author_id)
                FROM
                    tracks_authors
                WHERE
                    tracks_authors.track_id = tracks.id
                    AND tracks_authors.author_id = 'a419c5f0-c7a0-4542-8c21-7dda75a31267') AS authors
            FROM
                tracks
                JOIN stories_tracks ON tracks.id = stories_tracks.track_id
                LEFT JOIN users_mark_tracks ON users_mark_tracks.track_id = tracks.id
                AND users_mark_tracks.user_id = '1979fa3a-c2be-4ba7-8b91-3658ef2b0b33'
            WHERE
                tracks.disabled = FALSE
                AND stories_tracks.is_main = FALSE
                AND tracks.type = 'audio') AS override_track ON override_track.story_id = stories.id
    AND override_track.id != main_track.id
WHERE
    stories.disabled = FALSE
)