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: fred@foo.com>]
>>> Contact.objects.filter(cced_mails__id=111)
[<Contact: joe@bar.com>]
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: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, <Contact: joe@bar.com>, '...(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 开发者_JAVA技巧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 )
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.
加载中,请稍侯......
精彩评论