122

I'm trying to figure out how to write a 'not in' style query in django. For example, the query structure I'm thinking of would look like this.

select table1.* 
from table1
where table1.id not in 
(
  select table2.key_to_table1
  from table2 
  where table2.id = some_parm 
)

What would the django syntax look like assuming models called table1 and table2?

Turbo
  • 2,490
  • 4
  • 25
  • 30

6 Answers6

193
table1.objects.exclude(id__in=
    table2.objects.filter(your_condition).values_list('id', flat=True))

The exclude function works like the Not operator you where asking for. The attribute flat = True tells to table2 query to return the value_list as a one level list. So... at the end you are obtaining a list of IDs from table2, which you are going to user to define the condition in table1, that will be denied by the exclude function.

Olivier Pons
  • 15,363
  • 26
  • 117
  • 213
Harph
  • 2,250
  • 2
  • 17
  • 16
  • 3
    I also had troubles with the list constructor [table2...] -> list(table2...) worked for me. – RickyA Nov 23 '11 at 10:30
  • 3
    correction: table1.objects.exclude(id__in = table2.objects.filter(your_condition).values_list('id', flat=True) ) – Richard Oct 25 '12 at 08:45
  • 1
    Was trying to use this solution and ran into a problem, so if it happens to anyone else... `Objs=Tbl1.objects.filter(...); IDs=Objs.values_list('id', flat=True); Objs.delete(); Tbl2.objects.filter(id__in=IDs')` This did not work because IDs is actually a QuerySet object. When I deleted the rows it originated from, it no longer worked with other queries. The solution is `Tbl2.objects.filter(id__in=list(IDs))` -- turn it into a list – Dakusan Mar 03 '16 at 22:26
  • 1
    Depending on the context, if the filter is like "having count(xx)==yy" it's more than 100x faster to use `annotate()` (timeit gave me 1.0497902309998608 vs 0.00514069400014705) – Olivier Pons Apr 11 '19 at 08:18
11

with these models:

class table1(models.Model):
    field1 = models.CharField(max_length=10)      # a dummy field

class table2(models.Model):
    key_to_table1 = models.ForeignKey(table1)

you should get what you want using:

table1.objects.exclude(table2=some_param)
Sergio Morstabilini
  • 2,035
  • 21
  • 28
5
table1.objects.extra(where=["table1.id NOT IN (SELECT table2.key_to_table1 FROM table2 WHERE table2.id = some_parm)"])
ibz
  • 44,461
  • 24
  • 70
  • 86
4

The accepted answer is ok but I'm going to provide a new approach which is more operable.

from django.db.models import Q

query = Q(id__in=table2.objects.filter(your_condition).values_list('id'))
table1.objects.filter(~query)

If you are using the primary key the call to values_list() is not necessary.

The "~" on Q() object acts like "not" operator.

I guess this approach makes the code more reusable and allows you to do "any" sort of dynamic stuff just by storing your Q() objects on vars.

xpeiro
  • 733
  • 5
  • 21
1

You can write a custom lookup for Django queries:

From the documentation: "Let’s start with a simple custom lookup. We will write a custom lookup ne which works opposite to exact. Author.objects.filter(name__ne='Jack') will translate to the SQL: "author"."name" <> 'Jack'"

from django.db.models import Lookup

class NotEqual(Lookup):
    lookup_name = 'ne'

    def as_sql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return '%s <> %s' % (lhs, rhs), params
Blairg23
  • 11,334
  • 6
  • 72
  • 72
-17
[o1 for o1 in table1.objects.all() if o1.id not in [o2.id for o2 in table2.objects.filter(id=some_parm)]]

Or better

not_in_ids = [obj.id for obj in table2.objects.filter(id=some_parm)]
selected_objects = [obj for obj in table1.objects.iterator() if obj.id not in not_in_ids]
Blue Peppers
  • 3,718
  • 3
  • 22
  • 24