CODEWITHSUNDEEP
djangopostgresqlrelational-division
user3109719
user3109719

Reputation: 1

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)

Upvotes: 0

Views: 226

Answers (1)

Mp0int
Mp0int

Reputation: 18737

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

Upvotes: 1

Related Questions