PostgreSQL – Find Groups with Same Members

postgresqlrelational-division

Given the following schema:

User
  id

Group
  id

Membership
  group_id
  user_id

(With a unique index on group_id, user_id)

How would I select the group(s) of which the same two users are a member?

What I have now:

SELECT   "Membership"."group_id" 
FROM     "Membership" 
WHERE    "Membership"."user_id" IN (1, 2) 
GROUP BY "Membership"."group_id" 
HAVING   COUNT("Membership"."group_id") >= 2

Which seems to work, (it only retuns the group ids of which both users are a member), but looks rather confusing.

Is there a better way to express this query?

EDIT

So given the following data:

Users
-----
id
1
2
3

Group
-----
id
1
2
3

Membership
----------
group_id user_id
1        1
1        2
2        1
2        3
3        2
3        3

I'd like to find groups of which both user 1 and 2 are a member.

So the result of the query with user_id 1 and 2 should be group 1.
The same query for user_id 1 and 3 should return group 2.
And the query for user_id 2 and 3 should return group 3.

Best Answer

This is called relational division and your query is pretty much what you can do with standard SQL.

A different way to write this in Postgres would be to use arrays:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) = array[1,3];

It's important to also order the IDs inside the array[1,3] constant because array[1,3] is a different array than array[3,1];

The above returns those groups that consist exactly of those two members. Your query returns those that consists at least of those two members. That can be written with an array as well:

select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) @> array[1,3];

That uses the "contains" operator for arrays which tests if the array on the left side contains all elements of the array on the right side. But the array on the left side is allowed to have more elements.

The check for an exact match is probably faster using the array (because it would require an additional sub-query with standard SQL), but I don't think that the "at least" query makes a big difference in performance.

Related Solutions

Postgresql – Tightly coupling tables in postgresql

The only sensible solution is to normalize your model and get rid of those dreaded comma separated values. Then you can use a proper foreign key.

Something like:

create table users
(
    user_id   serial not null primary key,
    user_name text not null
);

create table groups
(
    group_id   serial not null primary key,
    group_name text not null
);

create table members  
(
    user_id  integer not null,
    group_id integer not null,
    primary key (user_id, group_id),
    foreign key (user_id) references users (user_id),
    foreign key (group_id) references groups (group_id)
);

If for some reason you would like to have the comma separated lists (for display convenience) you can always create a view that returns this:

create view v_members
as
select g.group_id, 
       g.group_name, 
       string_agg(u.user_name) as group_members
from members m
  join users u on u.user_id = m.user_id
  join groups g on g.group_id = m.group_id
group by g.group_id, g.group_name
;

PostgreSQL join subquery can’t restrict query

You want to filter out groups of rows rather than individual rows. That is, you want to keep only the groups that have ue.user_id = 1. Therefore use HAVING, rather than WHERE or ON, to add that condition, because HAVING is used for group filtering:

SELECT 
    SUM(owe) 
FROM (
    SELECT 
        (expenses.amount/count(*))
    AS owe 
    FROM expenses 
    LEFT JOIN user_expenses 
    ON (
        expenses.id = user_expenses.expense_id
        ) 
    WHERE expenses.paid_by != 1
    GROUP BY expenses.id
    HAVING COUNT(ue.user_id = 1 OR NULL) > 0
    ) 
AS total;

Best Answer

Related Solutions

Postgresql – Tightly coupling tables in postgresql

PostgreSQL join subquery can’t restrict query

Related Question