How to fetch those records who appears in many records in many to many relationship in PostgreSQL?

Question

I have the following three tables:

create table users(id int primary key, name varchar);
create table groups(id int primary key, name varchar);
create table user_groups(uid int references users(id), gid int references groups(id));  

Following are the records in the respective tables:

insert into users values (1, 'John'), (2, 'Bob'), (3, 'Maria'), (4, 'Jin');

insert into groups values (1, 'Persons - A'), (2, 'Persons - B'), 
(3, 'Persons - C'), (4, 'Persons - D'), (5, 'Pay - A'), (6, 'Pay - B'), (7, 'Pay - C');

insert into user_groups values (1, 1), (1, 2), (2, 1), (2, 4), (2, 6), (2, 7),
(3, 1), (3, 7), (4, 1), (4, 3);

Note: There are two types of groups, Persons and Pay

Now I want to find those users who have access to both types of groups (Persons and Pay), below is the desired output:

id        name       groups_user_has_access_to
2         Bob        Persons - A, Persons - D, Pay - B, Pay - C
3         Maria      Persons - A, Pay - C

The result should have only those users with their groups that have access to both types of groups.

Sorry for the bad English and appreciate your help in advance.

Answer 1

Here's my sample query for your question, the query could be slow depending on your table size

SELECT t.uid, u.name, t.groups_user_has_access_to
FROM (
  -- Each of GID, finding its big group (PERSON, PAY) --> called: group_type. 
  -- Using distinct to remove duplicated element in group_type 
  SELECT ug.uid, 
    array_agg (distinct 
      case when g.name like 'Person%' then 'Person' 
           when g.name like 'Pay%' then 'Pay' end) as group_type, 
      array_agg(g.name) as groups_user_has_access_to
  FROM user_groups ug 
  JOIN groups g on ug.gid = g.id 
  GROUP BY ug.uid
)t 
JOIN users u on t.uid = u.id 
WHERE array_length(t.group_type, 1) > 1 -- Reject any UID which belong to 1 group.
ORDER BY t.uid; 

I would suggest you to read the array functions in the query above.