long-query.sql

SELECT *
FROM (
    SELECT DISTINCT ON (tsv.created_at,tsv.id,ts.id) ts.id, ts.team_id, ts.archived_at, ts.type, ts.created_at, ts.updated_at,tsv.id, tsv.version, tsv.public_version, tsv.title, tsv.description, tsv.status, tsv.author_id, tsv.templates, tsv.created_at, tsv.updated_at, tsv.published_version, tsv.attributes, tsv.original_author_id
    FROM template_sets ts

    INNER JOIN (
        SELECT DISTINCT ON (id) id, version, public_version, title, description, status, author_id, templates, created_at, updated_at, published_version, attributes, original_author_id
        FROM template_set_versions
        ORDER BY id DESC, version DESC
    ) tsv
    ON tsv.id = ts.id

    INNER JOIN (
        SELECT ts.id, ts.team_id, ts.archived_at, ts.type, ts.created_at, ts.updated_at
        FROM template_sets ts
        INNER JOIN actor_attribute_values aav
        ON aav.actor_id=ts.id
        INNER JOIN attribute_values av
        ON av.id=aav.attribute_value_id
        INNER JOIN attributes a
        ON a.id=av.attribute_id
        WHERE (
            a.archived_at=0 AND av.archived_at=0 AND aav.archived_at=0
        ) AND (
            a.label='Location'
        ) AND (
            av.label=ANY('{"Malaysia"}'::text[])
        )
    ) subquery0
    ON subquery0.id = ts.id

    INNER JOIN job_templates jt
    ON (
        jt.metadata->'9834f929-e683-423e-847a-c258281b1e65' @> '{"__isref__":false,"__type__":"list","__value__":"7fd92d20-cf61-4c90-ba46-f85ecad2bbfd"}'::jsonb
    ) AND (
        jt.status = 'Published'
    ) AND (
        jt.team_id = ts.team_id
    ) AND (
        (jt.id, jt.published_version) IN (
            select id, MAX(published_version)
            FROM job_templates mjt
            WHERE mjt.team_id = ts.team_id
            GROUP BY id
        )
    ) AND (
        tsv.templates @> json_build_array(jt.id)::jsonb
    ) WHERE (
        ts.team_id = '641cab72-e1fe-40ae-ac89-afe4aa7cbdf0'
    ) AND (
        ts.type = ANY('{0}'::integer[])
    ) AND (
        ts.archived_at = 0
    ) ORDER BY tsv.created_at desc, tsv.id desc
) tsv   LIMIT 76;