Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Logical or of Django many to many queries returns duplicate results

I have models with many to many relationships like this:

class Contact(models.Model):
    name = models.TextField()
    address = models.TextField()

class Mail(models.Model):
    to = models.ManyToManyField(Contact, related_name='received_mails')
    cc = models.ManyToManyField(Contact, related_name='cced_mails')

I want to obtain the set of contacts that are in either the to field or the cc field for a given email. Let's try:

>>> Contact.objects.filter(received_mails__id=111)
[<Contact: [email protected]>]
>>> Contact.objects.filter(cced_mails__id=111)
[<Contact: [email protected]>]

So far so good. We have one contact for each relationship. But it would be nice to get them both into the same QuerySet.

>>> Contact.objects.filter(Q(received_mails__id=111) | Q(cced_mails__id=111))
[<Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, <Contact: [email protected]>, '...(remaining elements truncated)...']

What happened? I have a feeling it is something to do with joining tables in SQL, but I don't really understand what is happening under the hood with many to many relationships. It could be that what I am trying to do is stupid, or that there is an easy way to do it. Either way, I'm happy to be set on the right path.

Edit: this is the query of the QuerySet:

SELECT `mailshareapp_contact`.`id`, `mailshareapp_contact`.`name`,
`mailshareapp_contact`.`address` FROM `mailshareapp_contact`
LEFT OUTER JOIN `mailshareapp_mail_to`
ON (`mailshareapp_contact`.`id` = `mailshareapp_mail_to`.`contact_id`)
LEFT OUTER JOIN `mailshareapp_mail_cc`
ON (`mailshareapp_contact`.`id` = `mailshareapp_mail_cc`.`contact_id`)
WHERE (`mailshareapp_mail_to`.`mail_id` = 111
OR `mailshareapp_mail_cc`.`mail_id` = 111 )
like image 852
Rob Fisher Avatar asked Oct 12 '11 12:10

Rob Fisher


1 Answers

As SQL returns all matching records, Django dutifully maps them to objects. What you're looking for is the .distinct() queryset method that makes SQL collapse all duplicate rows into one.

like image 60
patrys Avatar answered Oct 22 '22 19:10

patrys