Select common values when using group by [Postgres]

Question

I have three main tables meetings, persons, hobbies with two relational tables.

Table meetings
+---------------+
| id | subject  |
+----+----------+
|  1 | Kickoff  |
|  2 | Relaunch |
|  3 | Party    |
+----+----------+

Table persons
+------------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Anna  |
|  3 | Linda |
+----+-------+

Table hobbies
+---------------+
| id | name     |
+----+----------+
|  1 | Soccer   |
|  2 | Tennis   |
|  3 | Swimming |
+----+----------+

Relation Table meeting_person
+-----------------+-----------+
| id | meeting_id | person_id |
+----+------------+-----------+
|  1 |          1 |         1 |
|  2 |          1 |         2 |
|  3 |          1 |         3 |
|  4 |          2 |         1 |
|  5 |          2 |         2 |
|  6 |          3 |         1 |
+----+------------+-----------+

Relation Table person_hobby
+----------------+----------+
| id | person_id | hobby_id |
+----+-----------+----------+
|  1 |         1 |        1 |
|  2 |         1 |        2 |
|  3 |         1 |        3 |
|  4 |         2 |        1 |
|  5 |         2 |        2 |
|  6 |         3 |        1 |
+----+-----------+----------+

Now I want to to find the common hobbies of all person attending each meeting. So the desired result would be:

+------------+-----------------+------------------------+
| meeting_id | persons         | common_hobbies         |
|            | (Aggregated)    | (Aggregated)           |
+------------+-----------------+------------------------+
|          1 | John,Anna,Linda | Soccer                 |
|          2 | John,Anna       | Soccer,Tennis          |
|          3 | John            | Soccer,Tennis,Swimming |
+------------+-----------------+------------------------+

My current work in progress is:

select
    m.id as "meeting_id", 
    (
        select string_agg(distinct p.name, ',')
        from meeting_person mp
        inner join persons p on mp.person_id = p.id
        where m.id = mp.meeting_id
    ) as "persons",
    string_agg(distinct h2.name , ',') as "common_hobbies"
from meetings m
inner join meeting_person mp2 on m.id = mp2.meeting_id 
inner join persons p2 on mp2.person_id = p2.id
inner join person_hobby ph2 on p2.id = ph2.person_id 
inner join hobbies h2 on ph2.hobby_id = h2.id 
group by m.id

But this query lists not the common_hobbies but all hobbies which are at least once mentioned.

+------------+-----------------+------------------------+
| meeting_id | persons         | common_hobbies         |
+------------+-----------------+------------------------+
|          1 | John,Anna,Linda | Soccer,Tennis,Swimming |
|          2 | John,Anna       | Soccer,Tennis,Swimming |
|          3 | John            | Soccer,Tennis,Swimming |
+------------+-----------------+------------------------+

Does anyone have any hints for me, on how I could solve this problem?

Cheers

Hint: Invert how you are doing this. Start with meeting, join only once into each table until you get to a result that looks like meeting.subject, hobby.name, person.name. No subqueries should be necessary for this step. — Mike Organek
– Mike Organek, Commented Aug 4, 2020 at 15:12
@Slava Rozhnev: Wrote the post on my office pc: will post the script tomorrow. — overflow
– overflow, Commented Aug 4, 2020 at 15:35
@Mike Organek: I know that the subquery isn‘t really necessary, but my question is a simplified version of a coworker of mine who used subqueries. I tried to stick to the way he started. The ‚persons‘ column is not really necessary for my problem. Or did I misunderstand you? — overflow
– overflow, Commented Aug 4, 2020 at 15:40
I am giving you a hint how to start. In a single query, inner join the five tables exactly once each to end up with meeting.subject, hobby.name, person.name as the result of your query. If this is a learning exercise, then it is critically important to approach SQL problems from a declarative rather than imperative angle. — Mike Organek
– Mike Organek, Commented Aug 4, 2020 at 15:53

Slava Rozhnev · Accepted Answer · 2020-08-04 21:25:25Z

This problem can be solved by implement custom aggregation function (found it here):

create or replace function array_intersect(anyarray, anyarray)
returns anyarray language sql
as $$
    select 
        case 
            when $1 is null then $2
            when $2 is null then $1
            else
                array(
                    select unnest($1)
                    intersect
                    select unnest($2))
        end;
$$;

create aggregate array_intersect_agg (anyarray)
(
    sfunc = array_intersect,
    stype = anyarray
);

So, the solution can be next:

select 
    meeting_id, 
    array_agg(ph.name) persons, 
    array_intersect_agg(hobby) common_hobbies
from meeting_person mp
join (
    select p.id, p.name, array_agg(h.name) hobby
    from person_hobby ph
    join persons p on ph.person_id = p.id
    join hobbies h on h.id = ph.hobby_id
    group by p.id, p.name
) ph on ph.id = mp.person_id
group by meeting_id;

Look the example fiddle

Result:

meeting_id |    persons            | common_hobbies
-----------+-----------------------+--------------------------
1          |    {John,Anna,Linda}  | {Soccer}
3          |    {John}             | {Soccer,Tennis,Swimming}
2          |    {John,Anna}        | {Soccer,Tennis}

Collectives™ on Stack Overflow

Select common values when using group by [Postgres]

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related