Django M2M QuerySet filtering on multiple foreign keys

Question

I have these two classes for a messaging module I'm working on. The idea is that a conversation is represented by a group of participants (two or more). I'm struggling to find a way to look up a conversation by the logic saying that the desired conversation I'm trying to find has the following participants. I tried Conversation.objects.filter(participants__in=[p1, p2]) however this does an OR style query, p1 is a participant or p2 is a participant. I want p1 and p2 and... pN is a participant. Any help out there?

class Conversation(models.Model):
    date_started = models.DateTimeField(auto_now_add=True)
    participants = models.ManyToManyField(User)

    def get_messages(self):
        return Message.objects.filter(conversation=self)

    def new_message(self, sender, body):
        Message.objects.create(sender=sender, body=body, conversation=self)
        self.save()


class Message(models.Model):
    sender = models.ForeignKey(User)
    body = models.TextField()
    date = models.DateTimeField(auto_now_add=True)
    conversation = models.ForeignKey(Conversation)

    def __unicodde__(self):
        return body + "-" + sender

score 4 · Accepted Answer · edited May 23 '17 at 12:23

I think you just need to iteratively filter. This could be utter nonsense as I'm a bit sleep deprived, but maybe a manager method like so:

class ConversationManager(models.Manager):
    def has_all(self, participants):
        # Start with all conversations
        reducedQs = self.get_query_set()
        for p in participants:
            # Reduce to conversations that have a participant "p" 
            reducedQs = reducedQs.filter(participants__id=p.id)
        return reducedQs

Generally speaking, you should get in the habit of making table-level queries manager methods, as opposed to class methods. And by doing it this way, you're left with a queryset that you can filter further if need be.

Inspired by the query of all Groups that have a member name Paul in the documentation and this answer.

Actual best answer, just lacks didactic introduction to chained filters on the same M2M relation :-) — vincent, Dec 02 '12 at 13:04
I like this, it seems like the proper way to do it not a hacked together solution. Thanks! — Mike, Dec 02 '12 at 17:34

score 2 · Answer 2 · answered Dec 02 '12 at 10:21

If you chain several times filter() on the same related model, the generated query will have an additional JOIN to the same table.

So you have : Conversation.objects.filter(participants=p1).filter(participants=p2)

You can confirm this behavior by looking at the generated query print Conversation.objects.filter(participants=p1).filter(participants=p2).query

See : https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships

Since it's still fairly simple and efficient I would avoid using python logic after the query, which would require bringing too much data from the database and then filter again by iterating.

Ohad · Answer 3 · 2012-12-02T06:56:42.280

0

First, I would add a related name to the participants field:

participants = models.ManyToManyField(User, related_name='conversations')

This is not necessary but more readable IMO.

Then you can do something like:

p1.conversations.filter(participants__in=p2)

This will return all p1's conversations where p2 is also participating.

I'm not sure about the DB efficiency of this filtering method, and perhaps using some other kind of database (maybe a graph DB such as Neo4j) is more suitable.

edited Dec 02 '12 at 06:56

answered Dec 01 '12 at 21:30

Ohad

1,719
1
16
20

You don't need to add a related_name arg you could directly access it using p1.conversation_set.all(). What happens if there are more than 2 participants? – Raunak Agarwal Dec 01 '12 at 23:37
You can do more and more `filter()` operations on the resulting queryset - one per each additional participant. I like your solution better, but why not use `Q` objects? – Ohad Dec 02 '12 at 06:55
There are actually quite a few ways of doing it either one could just loop through participants and filter the conversations or create Q filter and pass it in a query – Raunak Agarwal Dec 02 '12 at 07:32

score 0 · Answer 4 · answered Dec 01 '12 at 23:32

One way of doing it could be using python sets:

#Get the set of conversation ids for each participant
    p1_conv_set = set(Converstation.objects.filter(participants = p1).values_list('id', flat=True))
    p2_conv_set = set(Converstation.objects.filter(participants = p2).values_list('id', flat=True))
    .
    .
    pn_conv_set = set(Converstation.objects.filter(participants = pN).values_list('id', flat=True))
    #Find the common ids for all participants
    all_participants_intersection = p1_conv_set & p2_conv_set & ....pN_conv_set
    #Get all the conversation for all the calculated ids
    Conversation.objects.filter(id__in = all_participants_intersection)

Django M2M QuerySet filtering on multiple foreign keys

4 Answers4