deferred.sql

SELECT
    p0."id",
    p0."table_id",
    p0."deleted_at",
    b1."id",
    b1."subject_id",
    b1."verb_id",
    b1."object_id",
    b13."id",
    b13."is_sensitive",
    b19."id",
    b19."reply_to_id",
    b19."thread_id",
    b19."direct_replies_count",
    b19."nested_replies_count",
    b19."total_replies_count",
    b19."path",
    p7."id",
    p7."table_id",
    p7."deleted_at",
    b17."id",
    b17."name",
    b17."summary",
    b17."website",
    b17."location",
    b17."icon_id",
    b17."image_id",
    b18."id",
    b18."creator_id",
    b18."path",
    b18."file",
    b18."size",
    b18."media_type",
    b18."metadata",
    b18."deleted_at",
    b16."id",
    b16."username",
    b16."username_hash",
    b16."outbox_id",
    b16."inbox_id",
    b16."notifications_id",
    b8."id",
    b8."creator_id",
    p9."id",
    p9."table_id",
    p9."deleted_at",
    b11."id",
    b11."name",
    b11."summary",
    b11."website",
    b11."location",
    b11."icon_id",
    b11."image_id",
    b12."id",
    b12."creator_id",
    b12."path",
    b12."file",
    b12."size",
    b12."media_type",
    b12."metadata",
    b12."deleted_at",
    b10."id",
    b10."username",
    b10."username_hash",
    b10."outbox_id",
    b10."inbox_id",
    b10."notifications_id",
    b15."id",
    b15."peer_id",
    b15."canonical_uri",
    b14."id",
    b14."name",
    b14."summary",
    b14."html_body",
    p3."id",
    p3."table_id",
    p3."deleted_at",
    b5."id",
    b5."name",
    b5."summary",
    b5."website",
    b5."location",
    b5."icon_id",
    b5."image_id",
    b6."id",
    b6."creator_id",
    b6."path",
    b6."file",
    b6."size",
    b6."media_type",
    b6."metadata",
    b6."deleted_at",
    b4."id",
    b4."username",
    b4."username_hash",
    b4."outbox_id",
    b4."inbox_id",
    b4."notifications_id"
FROM
    "pointers_pointer" AS p0
    LEFT OUTER JOIN "bonfire_data_social_activity" AS b1 ON b1."id" = p0."id"
    INNER JOIN (
        SELECT
            sp0."id" AS "id",
            sp0."table_id" AS "table_id",
            sp0."deleted_at" AS "deleted_at"
        FROM
            "pointers_pointer" AS sp0
            LEFT OUTER JOIN "bonfire_data_social_activity" AS sb1 ON sb1."id" = sp0."id"
            INNER JOIN (
                SELECT DISTINCT
                    ON (ssb1."id") ssb0."id" AS "id"
                FROM
                    "bonfire_data_social_feed_publish" AS ssb0
                    INNER JOIN "bonfire_data_social_activity" AS ssb1 ON ssb1."id" = ssb0."id"
                    INNER JOIN "pointers_pointer" AS ssp2 ON (
                        (ssp2."id" = ssb1."id")
                        AND (ssp2."deleted_at" IS NULL)
                    )
                    AND NOT (
                        ssp2."table_id" = ANY ('{6R1VATEMESAGEC0MMVN1CAT10N}')
                    )
                    INNER JOIN "pointers_pointer" AS ssp3 ON (
                        (ssp3."id" = ssb1."object_id")
                        AND (ssp3."deleted_at" IS NULL)
                    )
                    AND (
                        (ssp3."table_id" IS NULL)
                        OR NOT (
                            ssp3."table_id" = ANY ('{6R1VATEMESAGEC0MMVN1CAT10N}')
                        )
                    )
                    LEFT OUTER JOIN "pointers_pointer" AS ssp4 ON ssp4."id" = ssb1."subject_id"
                    LEFT OUTER JOIN "bonfire_data_identity_character" AS ssb5 ON ssb5."id" = ssp4."id"
                    LEFT OUTER JOIN "bonfire_data_activity_pub_peered" AS ssb6 ON ssb6."id" = ssb5."id"
                    LEFT OUTER JOIN "bonfire_data_activity_pub_peered" AS ssb7 ON ssb7."id" = ssp3."id"
                WHERE
                    (
                        NOT (
                            ssb1."verb_id" = ANY (
                                '{71AGSPAM0RVNACCEPTAB1E1TEM,20SVBSCR1BET0THE0VTPVT0F1T,11KES1ND1CATEAM11DAPPR0VA1,1P1NN1NNG1S11KEH1GH11GHT1T,40NTACTW1THAPR1VATEMESSAGE,7PDATETHESTATVS0FS0METH1NG}'
                            )
                        )
                    )
                    AND (
                        (
                            (ssb0."feed_id" = '3SERSFR0MY0VR10CA11NSTANCE')
                            OR (
                                (ssb6."id" IS NULL)
                                AND (ssb7."id" IS NULL)
                            )
                        )
                        AND (ssb1."subject_id" != '1ACT1V1TYPVBREM0TESFETCHER')
                    )
            ) AS ss2 ON ss2."id" = sp0."id"
        WHERE
            (sp0."deleted_at" IS NULL)
        ORDER BY
            sb1."id" DESC
    ) AS s2 ON s2."id" = p0."id"
    LEFT OUTER JOIN "pointers_pointer" AS p3 ON NOT (
        b1."subject_id" = ANY ('{01J6SP6JMVM7AQP593NS1Y91NE}')
    )
    AND (b1."subject_id" = p3."id")
    LEFT OUTER JOIN "bonfire_data_identity_character" AS b4 ON b4."id" = p3."id"
    LEFT OUTER JOIN "bonfire_data_social_profile" AS b5 ON b5."id" = p3."id"
    LEFT OUTER JOIN "bonfire_files_media" AS b6 ON b6."id" = b5."icon_id"
    LEFT OUTER JOIN "pointers_pointer" AS p7 ON p7."id" = b1."object_id"
    LEFT OUTER JOIN "bonfire_data_social_created" AS b8 ON b8."id" = p7."id"
    LEFT OUTER JOIN "pointers_pointer" AS p9 ON (
        (b8."creator_id" != b1."subject_id")
        AND NOT (
            b8."creator_id" = ANY ('{01J6SP6JMVM7AQP593NS1Y91NE}')
        )
    )
    AND (b8."creator_id" = p9."id")
    LEFT OUTER JOIN "bonfire_data_identity_character" AS b10 ON b10."id" = p9."id"
    LEFT OUTER JOIN "bonfire_data_social_profile" AS b11 ON b11."id" = p9."id"
    LEFT OUTER JOIN "bonfire_files_media" AS b12 ON b12."id" = b11."icon_id"
    LEFT OUTER JOIN "bonfire_data_social_sensitive" AS b13 ON b13."id" = b1."object_id"
    LEFT OUTER JOIN "bonfire_data_social_post_content" AS b14 ON b14."id" = p7."id"
    LEFT OUTER JOIN "bonfire_data_activity_pub_peered" AS b15 ON b15."id" = p7."id"
    LEFT OUTER JOIN "bonfire_data_identity_character" AS b16 ON b16."id" = p7."id"
    LEFT OUTER JOIN "bonfire_data_social_profile" AS b17 ON b17."id" = p7."id"
    LEFT OUTER JOIN "bonfire_files_media" AS b18 ON b18."id" = b17."icon_id"
    LEFT OUTER JOIN "bonfire_data_social_replied" AS b19 ON b19."id" = b1."object_id"
WHERE
    (p0."deleted_at" IS NULL)
    AND (
        exists (
            (
                SELECT
                    count(ss0."subject_id"),
                    ss0."object_id"
                FROM
                    (
                        SELECT
                            ssp0."id" AS "subject_id",
                            ssb1."id" AS "object_id",
                            ssb2."id" AS "verb_id",
                            agg_perms (ssb3."value") AS "value"
                        FROM
                            "pointers_pointer" AS ssp0
                            CROSS JOIN "bonfire_data_access_control_controlled" AS ssb1
                            CROSS JOIN "bonfire_data_access_control_verb" AS ssb2
                            LEFT OUTER JOIN "bonfire_data_access_control_grant" AS ssb3 ON (ssb1."acl_id" = ssb3."acl_id")
                            AND (ssb3."verb_id" = ssb2."id")
                            LEFT OUTER JOIN "bonfire_data_access_control_circle" AS ssb4 ON ssb3."subject_id" = ssb4."id"
                            LEFT OUTER JOIN "bonfire_data_access_control_encircle" AS ssb5 ON (ssb5."circle_id" = ssb4."id")
                            AND (ssb5."subject_id" = ssp0."id")
                        WHERE
                            (
                                (ssb3."subject_id" = ssp0."id")
                                OR NOT (ssb5."id" IS NULL)
                            )
                        GROUP BY
                            ssp0."id",
                            ssb1."id",
                            ssb2."id"
                    ) AS ss0
                WHERE
                    (ss0."object_id" = b1."object_id")
                    AND (
                        ss0."subject_id" = ANY (
                            '{3SERSFR0MY0VR10CA11NSTANCE,01J6SP6JMVM7AQP593NS1Y91NE}'
                        )
                        AND ss0."verb_id" = ANY (
                            '{0BSERV1NG11ST1NGSEX1STENCE,0EAD1NGSVTTER1YFVNDAMENTA1}'
                        )
                    )
                GROUP BY
                    ss0."object_id"
                HAVING
                    (agg_perms (ss0."value"))
            )
        )
    )
ORDER BY
    b1."id" DESC
LIMIT
    21