How can I find the union of two Django querysets?

Question

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: http://stackoverflow.com/questions/431628/how-to-combine-2-or-more-querysets-in-a-django-view — rnevius, Mar 21 '16 at 13:20
Starting from version 1.11, django query sets have a builtin union method. I have added it as an answer for future reference — Jose Cherian, Aug 09 '17 at 01:44

score 219 · Accepted Answer · edited Mar 28 '17 at 10:08

219

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()

edited Mar 28 '17 at 10:08

Flimm

136,138
45
251
267

answered Dec 10 '10 at 18:56

Jordan Reiter

20,467
11
95
161

8

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. – Krystian Cybulski Jan 09 '13 at 15:10
5

Due to a Django bug, this construction can sometimes return incorrect results when dealing with `ManyToManyField`s. For example, you will sometimes see that `records.count()` will be greater than `query1.count() + query2.count()`, which is clearly incorrect. – Jian Apr 24 '13 at 00:00
4

@Jian can you clarify django version with the bug and a link to the djangoproject issue? – IMFletcher May 03 '13 at 01:59
1

@IMFletcher: I have 1.4.5 and all = my_instance.forward_many_to_many.all() | my_instance.reverse_many_to_many.all() ; all has duplicate entry where I thought `union` should eliminate the duplciate. – eugene May 31 '13 at 06:30
11

records = query1 | query2 ; records = records.distinct() would give me the correct result – eugene May 31 '13 at 06:34
how does this work? we know '|' is bitwise OR. So how does Query1 | Query2 work? Does it take a bit representation of query1 and query2? – Jay Sep 30 '13 at 10:50
5

You can overload operators in Python. See http://docs.python.org/2/library/operator.html. So what Django does is create special methods for the QuerySet object. See the code here: https://github.com/django/django/blob/master/django/db/models/query.py#L178 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). – Jordan Reiter Oct 07 '13 at 18:11
1

Does this union respect the original order of the querysets? if I do `final_query = query1 | query2` will final_query be a queryset with the elements of query1 first and followed by the elements of query2 (that are not in query1)? – diegopau Jun 04 '15 at 10:45
2

I'm pretty sure you can't expect it to respect the existing order. However, you can call `order_by` on `final_query` afterwards. If it is vital that each part keep its existing order, then you should combine them manually. – Jordan Reiter Jun 04 '15 at 15:19
2

I think that comment from @JordanReiter deserves more votes. It is important to understand that the ORM will create an OR query not a UNION query so that method cannot be used to achieve custom ordering. – Peter Kilczuk Nov 10 '15 at 18:17
@eugene's comment about needing to call `queryset.distinct()` to get distinct results should be added to the answer. I had a bug where results weren't distinct, so this needed to be called. – Aaron Lelevier Oct 26 '16 at 18:16
this is not in django docs (https://docs.djangoproject.com/en/1.10/topics/db/queries/), is it deprecated? – mehmet Feb 01 '17 at 13:55
@mehmet no clue why it isn't in the docs. It still works in Django 1.10 as far as I know. Here's the direct link to the source that implements it: https://github.com/django/django/blob/master/django/db/models/query.py#L298 – Jordan Reiter Feb 16 '17 at 21:58
@Quazer This is technically true, but in most cases the output will match the desired outcome, which is a combination of all records that match the conditions in query 1 with the records that match the conditions in query 2. `select ... from tt where name='foo' or name='goo'` is similar output to `select .. from tt where name='foo' union select ... from tt where name='goo'`. – Jordan Reiter May 27 '20 at 17:04

score 69 · Answer 2 · edited Jan 14 '21 at 18:28

69

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.

edited Jan 14 '21 at 18:28

jidicula

3,454
1
17
38

answered Aug 09 '17 at 01:42

Jose Cherian

7,207
3
36
39

I couldn't get all=True to work. Ended up doing casting my queryset to a set before returning it to the client. – Braden Holt Mar 15 '19 at 23:32
1

@BradenHolt, all=True, means it will contain duplicate records. You can simply remove all=True to avoid casting it to a set. – Jose Cherian Mar 16 '19 at 20:26
after this doesnt work DjangoFilterBackend, how i can use union and DjangoFilterBackend ? – nesalexy Aug 29 '19 at 17:59
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." – jrial Nov 15 '19 at 14:29

Xianxing · Answer 3 · 2019-11-19T12:08:43.663

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?

3 Answers3

Linked