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 28 · Answer 1 · edited Feb 13 '20 at 13:35

28

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

4,408
3
22
42

answered Oct 05 '17 at 10:03

markus-hinsche

1,242
15
26

How can I do it at Django 1.10? – Artem Dumanov Mar 24 '18 at 14:39
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

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

17

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

22,731
15
76
81

5

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

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,103
2
19
41

`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

How to get the difference of two querysets in Django?

4 Answers4

1. Filter things manually (quite ugly)

2. Just make another query

Linked