How to get the difference of two querysets in Django?

Question

I have to querysets. alllists and subscriptionlists

alllists = List.objects.filter(datamode = 'A')
subscriptionlists = Membership.objects.filter(member__id=memberid, datamode='A')

I need a queryset called unsubscriptionlist, which possess all records in alllists except the records in subscription lists. How to achieve this?

The two querysets in your example appear to use different models. They would need to be using the same model for your question to make sense. — Aram Dulyan, May 10 '11 at 08:04
This is a related answer which is much more concise- http://stackoverflow.com/questions/8867743/how-does-one-find-the-entities-in-a-django-query-set-that-are-not-in-another-spe — Devang, Nov 02 '13 at 08:07

score 35 · Answer 1 · edited Feb 13 '20 at 13:35

35

Since Django 1.11, QuerySets have a difference() method amongst other new methods:

# Capture elements that are in qs_all but not in qs_part
qs_diff = qs_all.difference(qs_part)

Also see: https://stackoverflow.com/a/45651267/5497962

edited Feb 13 '20 at 13:35

funnydman

9,083
4
40
55

answered Oct 05 '17 at 10:03

markus-hinsche

1,372
15
26

How can I do it at Django 1.10? – Artem Dumanov Mar 24 '18 at 14:39
1

This method can't be used with SQLITE, which raises `django.db.utils.DatabaseError: ORDER BY not allowed in subqueries of compound statements.`. – Dzhuang May 21 '22 at 12:28
What if one of the qs is `NoneType`? – alias51 Aug 13 '22 at 16:54

score 22 · Answer 2 · answered May 10 '11 at 07:06

22

You should be able to use the set operation difference to help:

set(alllists).difference(set(subscriptionlists))

answered May 10 '11 at 07:06

Brian Fisher

23,519
15
78
82

6

Way too complicated of a solution when you can simply use `exclude` instead of `filter` – Chris Pratt May 10 '11 at 21:26
Yeah its definitely not optimal if the desired result can be obtained with a single queryset, but if for some reason it can't this is a viable solution. – Brian Fisher May 10 '11 at 21:36
True. Hadn't considered that. Good information to leave around for those with complicated queryset math going on. – Chris Pratt May 10 '11 at 21:47
And another way to do the same thing: set(alllists) - set(ssubscriptionlists) – lurknobserve Jan 23 '17 at 16:29
this is a good solution when using database backends that don't support `queryset.difference(another_queryset)` – Chris Sep 23 '22 at 11:28

score 11 · Answer 3 · answered May 10 '11 at 06:20

Well I see two options here.

1. Filter things manually (quite ugly)

diff = []
for all in alllists:
    found = False
    for sub in subscriptionlists:
        if sub.id == all.id:
            found = True 
            break
    if not found:
        diff.append(all)

2. Just make another query

diff = List.objects.filter(datamode = 'A').exclude(member__id=memberid, datamode='A')

score 4 · Answer 4 · answered May 10 '11 at 08:19

4

How about:

subscriptionlists = Membership.objects.filter(member__id=memberid, datamode='A')
unsubscriptionlists = Membership.objects.exclude(member__id=memberid, datamode='A')

The unsubscriptionlists should be the inverse of subscription lists.

Brian's answer will work as well, though set() will most likely evaluate the query and will take a performance hit in evaluating both sets into memory. This method will keep the lazy initialization until you need the data.

answered May 10 '11 at 08:19

Gevious

3,214
3
21
42

`unsubscriptionlists = Membership.objects.exclude(member__id=memberid, datamode='A')` should be: `unsubscriptionlists = Membership.objects.filter(datamode='A').exclude(member__id=memberid)` – Chris Pratt May 10 '11 at 21:28

score 0 · Answer 5 · answered Nov 23 '22 at 19:24

In case anyone's searching for a way to do symmetric difference, such operator is not available in Django.

That said, it's not that hard to implement it using difference and union, and it'll all be done in a single query:

q1.difference(q2).union(q2.difference(q1))

How to get the difference of two querysets in Django?

5 Answers5

1. Filter things manually (quite ugly)

2. Just make another query

Linked