gistfile1.sql

SELECT *
FROM   (SELECT raw_sql_.*,
               ROWNUM raw_rnum_
        FROM   (SELECT   activities.*
                FROM     activities
                         JOIN groups_user_profiles other_groups_user_profiles
                           ON activities.user_profile_id = other_groups_user_profiles.user_profile_id
                         JOIN groups_user_profiles my_groups_user_profiles
                           ON other_groups_user_profiles.group_id = my_groups_user_profiles.group_id
                              AND my_groups_user_profiles.user_profile_id = 78197
                         LEFT OUTER JOIN posts
                           ON activities.subject_id = posts.id
                              AND subject_type = 'Post'
                WHERE    (activities.user_profile_id <> '78197'
                          AND ((posts.id IS NULL
                                 OR (posts.id IS NOT NULL
                                     AND posts.parent_id IS NULL
                                     AND (NOT EXISTS (SELECT 1
                                                      FROM   groups_posts
                                                      WHERE  posts.id = groups_posts.post_id)
                                           OR EXISTS (SELECT 1
                                                      FROM   groups_posts,
                                                             GROUPS
                                                      WHERE  groups_posts.post_id = posts.id
                                                             AND groups_posts.group_id = GROUPS.id
                                                             AND GROUPS.private = 0)
                                           OR EXISTS (SELECT 1
                                                      FROM   groups_posts,
                                                             GROUPS,
                                                             groups_user_profiles
                                                      WHERE  groups_posts.post_id = posts.id
                                                             AND groups_posts.group_id = GROUPS.id
                                                             AND GROUPS.id = groups_user_profiles.group_id
                                                             AND groups_user_profiles.user_profile_id = 78197))))
                                OR (posts.id IS NOT NULL
                                    AND posts.parent_id IS NOT NULL
                                    AND (NOT EXISTS (SELECT 1
                                                     FROM   groups_posts
                                                     WHERE  posts.parent_id = groups_posts.post_id)
                                          OR EXISTS (SELECT 1
                                                     FROM   groups_posts,
                                                            GROUPS
                                                     WHERE  groups_posts.post_id = posts.parent_id
                                                            AND groups_posts.group_id = GROUPS.id
                                                            AND GROUPS.private = 0)
                                          OR EXISTS (SELECT 1
                                                     FROM   groups_posts,
                                                            GROUPS,
                                                            groups_user_profiles
                                                     WHERE  groups_posts.post_id = posts.parent_id
                                                            AND groups_posts.group_id = GROUPS.id
                                                            AND GROUPS.id = groups_user_profiles.group_id
                                                            AND groups_user_profiles.user_profile_id = 78197)))))
                ORDER BY activities.created_at DESC,
                         activities.id,
                         activities.id DESC) raw_sql_
        WHERE  ROWNUM <= 20)
WHERE  raw_rnum_ > 0