Django in / not in request

Question

I am trying to figure out how to write a non-style query in django. For example, the query structure that I think 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?

Answer 1

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

The exclude function works like the Not operator in which you request. The flat = True attribute tells table2 to return a value_list as a list of one level. So ... at the end you get a list of IDs from table2, which you are going to define by the user in table1 , which will be rejected by the exclude function.

Answer 2

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:

 table1.objects.exclude(table2=some_param) 

Answer 3

 table1.objects.extra(where=["table1.id NOT IN (SELECT table2.key_to_table1 FROM table2 WHERE table2.id = some_parm)"]) 

Answer 4

You can write your own search for Django queries:

From the documentation : “Let's start with a simple user search. We will write a user search ne that works against the exact one . Author.objects.filter (name__ne = 'Jack') will translate into 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 

Answer 5

 [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]