28

Consider two QuerySet objects of the same class. Is there a simple way to unify them into a single QuerySet by calculating the union? Also, is there a simple way to subtract them? Removing all elements that appear in both sets from one of the sets?

Jonathan Livni
  • 101,334
  • 104
  • 266
  • 359
  • Can we get some examples of how you generate the initial two QuerySets and how they're used? With more information you may be able to simply optimize your original QuerySet instead of having to add/subtract? – Jack M. Jun 04 '10 at 15:38
  • qs1 = MyObj1.objects.filter(some_field__gte=value) - qs2 on the other hand is actually an M2M relation between a certain MyObj2 instance and MyObj1 instances I figured I'll ask about QuerySets, as I think the answer would be applicable to myobj2_instance.myobj1 as well. – Jonathan Livni Jun 04 '10 at 19:01

6 Answers6

29

Since Django 1.11, QuerySets have union(), intersection() and difference() methods.

It's also possible to use & and | infix operators with QuerySets (I could not find a reference to this in the docs, so I guess union() and intersection() is the preferred way to combine two querysets.

qs3 = qs1.union(qs2)         # or qs3 = qs1 | qs2
qs3 = qs1.intersection(qs2)  # or qs3 = qs1 & qs2
qs3 = qs1.difference(qs2)    # no operator for this

You can also use Q() objects which like QuerySets implement | and &, and additionally the invert prefix operator ~.

Neither class implement the xor / symmetric difference infix operator ^.

Håken Lid
  • 22,318
  • 9
  • 52
  • 67
19

Subtract a QuerySet from another QuerySet using the same model.

This works - but is probably slowly

queryset_with_hello = Blog.objects.filter(name__icontains='hello')
queryset_without_hello = Blog.objects.exclude(pk__in=queryset_with_hello)

Read the performance considerations in django documentation:

https://docs.djangoproject.com/en/dev/ref/models/querysets/#in

Demokrates
  • 191
  • 1
  • 3
  • This only works while constructing QuerySets, the question is about **existing** QuerySets. How would one go about doing qs1- qs2 when they already exist as querysets. – Curtwagner1984 Sep 15 '16 at 13:38
  • Modifying an existing queryset is a notion that doesn't make much sense in django. Do this: `q = Blog.objects.filter(pk__in=q1).exclude(pk__in=q2)` – DylanYoung Nov 15 '17 at 17:49
  • @DylanYoung why? AFAIK it makes sense, because queryset is evaluated only when u are iterating over it or if you explicitly call one of those methods https://docs.djangoproject.com/en/2.1/ref/models/querysets/#when-querysets-are-evaluated So this answer makes much sense. – Qback Nov 08 '18 at 16:18
  • @Qback Yes the answer makes sense; please take a look at the comment I was replying to. The point is: it is NOT modifying an existing QuerySet (a concept which doesn't make a lot of sense: Most every call to an existing queryset, returns a *new* queryset). @Curtwagner1984 That said, with existing querysets `q1` and `q2`, you can get a queryset representing the difference using essentially the same code as this answer gives: `q_diff = q1.exclude(pk__in=q2)` – DylanYoung Nov 08 '18 at 18:33
16

Going back to django's documentation, you can:

new_query_set = query_set_1 | query_set_2

This works as a logical OR which is actually addition without duplicates. This answers the addition aspect and AFAIK does not hit the db at all!

new_query_set = query_set_1 & query_set_2

This works as a logical AND.

Still missing how to subtract QuerySets. It's hard for me to believe this has not been dealt with elegantly by the community...

Jonathan Livni
  • 101,334
  • 104
  • 266
  • 359
4

You can use the Q object.

The syntax could be something like this:

added_query_set = YourModel.objects.\
         filter(Q(id__in=old_query_set_1)|Q(id__in=old_query_set_2))

You probably can optimize based on your actual needs and get the amount of db hits down (right now it's 3), but this should get you started.

Zach
  • 18,594
  • 18
  • 59
  • 68
  • +1 for doing it nicely with querysets, but i guess it's even hitting the db one time more than adding lists! i guess which method you prefer depends on if you want an unevaluated qs or lesser db hits! – Bernhard Vallant Jun 04 '10 at 15:12
  • 1
    Given the actual parameters of the two original `QuerySet`s you should be able to include those parameters in the `Q` objects and get down to one db hit – Zach Jun 04 '10 at 15:31
1
qs3 = qs1.union(qs2) # union
qs4 = qs1.difference(qs2) # subtraction
Headmaster
  • 2,008
  • 4
  • 24
  • 51
0

I think for operations as this you need to evalute them. So you can call list() on them and work on them with the common python list operations!

Bernhard Vallant
  • 49,468
  • 20
  • 120
  • 148