Postgres sql intersect with except relational division

I have a table called groups. I want to return the group number that only John and Mary are part of.

Think of a chat app, I want to see if these two existing people already have a group with ONLY them.

Group | user
1     | john
1     | mary
1     | dave
2     | john
2     | mary

How can I do this?

Here is the actual model

class Conversation(models.Model):
    attendee = models.ManyToManyField(User)
    guid = models.CharField(max_length=16)

Answers


An asnwer based on @arocks's approach, since @rocks's annotation is wrong.

Conversation.objects.annotate(user_count=Count('attendee')).filter(user_count=2, attendee__username="john").filter(attendee__username="mary")

That will return you a QuerySet of Conversation objects which have 2 members and members are mary and john

The reason you must you 2 separate filter is, your database management system need to create temporary subtables since you need to use same database column username twice for filtering. Your data


Need Your Help

"SCRIPT5: Access is denied" error when accessing web sites using LocalStorage

javascript internet-explorer local-storage windows-8.1 internet-explorer-11

One of our client is getting ""SCRIPT5: Access is denied" error when accessing web sites on Windows 8.1/IE11. Only one client is having this issue not all.

Is the verbosity really worth it when passing read only vector in function? const vector<const string>&

c++ c++11

Is the verbosity really worth it when passing read only vector in function?