Postgresql – How to fetch those records who appears in many records in many to many relationship in PostgreSQL

many-to-manypostgresql

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.

Best Answer

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.

Related Question