CODEWITHSUNDEEP
sqldjangodjango-queryset
abaldwin99
abaldwin99

Reputation: 913

Filter results if not contained in another column

Is there an equivalent way to do the following SQL command with Django's QuerySet API?

select id, childid from mysite_nodetochild
where childid NOT IN (Select "Nodeid" from mysite_nodetochild)

I would prefer not to use raw sql if possible but I can't get a clean working version using Django's Queryset.

Upvotes: 0

Views: 37

Answers (1)

Maciej Gol
Maciej Gol

Reputation: 15854

Try

nodetochild.objects.exclude(childid=nodetochild.objects.values_list('Nodeid', flat=True)).only('id', 'childid')

This should evaluate to, more or less:

SELECT "mysite_nodetochild"."id", "mysite_nodetochild"."childid" FROM "mysite_nodetochild" WHERE NOT ("mysite_nodetochild"."childid" =  (SELECT U0."nodeid" FROM "mysite_nodetochild" U0))

Or, if you need the IN condition:

nodetochild.objects.exclude(childid__in=nodetochild.objects.values_list('Nodeid', flat=True)).only('id', 'childid')

Would evaluate to:

SELECT "mysite_nodetochild"."id", "mysite_nodetochild"."childid" FROM "mysite_nodetochild" WHERE NOT ("mysite_nodetochild"."childid" IN  (SELECT U0."nodeid" FROM "mysite_nodetochild" U0))

Upvotes: 1

Related Questions