How can I find the union of two Django querysets?

python django django-models

I’ve got a Django model with two custom manager methods. Each returns a different subset of the model’s objects, based on a different property of the object.

Is there any way to get a queryset, or just a list of objects, that’s the union of the querysets returned by each manager method?

(From a deleted answer) See this question for a variation that works with QuerySets from different Models: stackoverflow.com/questions/431628/…

Starting from version 1.11, django query sets have a builtin union method. I have added it as an answer for future reference

Flimm

This works and looks a bit cleaner:

records = query1 | query2

If you don't want duplicates, then you will need to append .distinct():

records = (query1 | query2).distinct()

While the accepted answer returns a union iterable (list to be exact), like OP has asked, this method returns a true union of querysets. This queryset can be operated on further, which is desired in many circumstances.

Due to a Django bug, this construction can sometimes return incorrect results when dealing with ManyToManyFields. For example, you will sometimes see that records.count() will be greater than query1.count() + query2.count(), which is clearly incorrect.

@Jian can you clarify django version with the bug and a link to the djangoproject issue?

records = query1 | query2 ; records = records.distinct() would give me the correct result

You can overload operators in Python. See docs.python.org/2/library/operator.html. So what Django does is create special methods for the QuerySet object. See the code here: github.com/django/django/blob/master/django/db/models/… the QuerySet class provides methods for __and__ and __or__ that are called when the & or | operators are used between two QuerySet objects (also used for the Q class as well).

jidicula

Starting from version 1.11, django querysets have a builtin union method.

q = q1.union(q2) #q will contain all unique records of q1 + q2
q = q1.union(q2, all=True) #q will contain all records of q1 + q2 including duplicates
q = q1.union(q2,q3) # more than 2 queryset union

See my blog post on this for more examples.

I couldn't get all=True to work. Ended up doing casting my queryset to a set before returning it to the client.

@BradenHolt, all=True, means it will contain duplicate records. You can simply remove all=True to avoid casting it to a set.

after this doesnt work DjangoFilterBackend, how i can use union and DjangoFilterBackend ?

Unfortunately, this does not seem to work for models with a default ordering defined in the model's Meta. Whenever I try to combine these with .union, I receive the following error: "ORDER BY not allowed in subqueries of compound statements."

Xianxing

I would suggest using 'query1.union(query2)' instead of 'query1 | query2'; I got different results from the above two methods and the former one is what I expected. The following is what I had come across:

print "union result:"
for element in query_set1.union(query_set2):
    print element

print "| result:"
for element in (query_set1 | query_set2):
    print element

result:

union result:
KafkaTopic object
KafkaTopic object
KafkaTopic object
KafkaTopic object
KafkaTopic object

| result:
KafkaTopic object
KafkaTopic object

How can I find the union of two Django querysets?

Follow WeChat

Want to stay one step ahead of the latest teleworks?

相似问题

Platform

Support

Contact US