Users In Specific Group(s) BUT NOT In Other Group(s)

data & reporting
1

Users In Specific Group(s) BUT NOT In Other Group(s)

Info: The Parameters have to be written as arrays. For example: {1,2,3}

-- [params]  
-- string :opt_in_groups
-- string :opt_out_groups
SELECT
   u.id AS user_id,
   g.id AS GROUP_ID 
FROM
   users u 
   join
      group_users gu 
      ON gu.user_id = u.id 
   join
      GROUPS g 
      ON g.id = gu.group_id 
WHERE
   g.id = ANY (:opt_in_groups::int[]) 
   AND u.id NOT IN 
   (
      SELECT
         u.id AS user_id 
      FROM
         users u 
         join
            group_users gu 
            ON gu.user_id = u.id 
         join
            GROUPS g 
            ON g.id = gu.group_id 
      WHERE
         g.id = ANY (:opt_out_groups::int[]) 
   )
ORDER BY
   u.primary_group_id
8 Likes