Django equivalent of SQL not in

Question

I have a very simple query: select * from tbl1 where title not in('asdasd', 'asdasd').

How do I translate that to Django? It's like I want the opposite of: Table.objects.filter(title__in=myListOfTitles)

score 195 · Accepted Answer · answered Jan 25 '12 at 13:31

195

Table.objects.exclude(title__in=myListOfTitles)

answered Jan 25 '12 at 13:31

JamesO

17

This does not work with related fields. Negating the is not the same as not in. For example `Publisher.objects.filter(book__author__in=XXX`). – Alex Rothberg May 29 '15 at 20:41
Maybe you should do it step by step then. Here is a line from an application of mine : `User.objects.exclude(id__in=Group.objects.get(id=kwargs['group_id']).user_ids.all())` which returns all the users that are not in the group which has `group_id` as id. – lbris Jan 28 '20 at 10:08
3

be aware, that exclude places "or" between conditions. – Voilin Apr 19 '21 at 17:15

score 33 · Answer 2 · answered Jan 12 '21 at 12:33

33

(this thread is old but still could be googled)

you can use models.Q with "~" as follows:

Table.objects.filter(~Q(title__in=myListOfTitles))

this method specially is helpful when you have multiple conditions.

answered Jan 12 '21 at 12:33

omid

score 27 · Answer 3 · answered Jan 25 '12 at 13:31

27

Table.objects.exclude(title__in=myListOfTitles)

answered Jan 25 '12 at 13:31

Alexey Savanovich

score 3 · Answer 4 · answered Nov 16 '21 at 08:33

3

Django provides two options.

exclude(<condition>)
filter(~Q(<condition>))

Method 2 using Q() method

>>> from django.db.models import Q
>>> queryset = User.objects.filter(~Q(id__lt=5))

answered Nov 16 '21 at 08:33

Muhammad Abdullah

4 Answers4