How to put Django query filter in ON clause instead of WHERE clause?

Question

In SQL, the placement of a condition, whether in the ON clause or the WHERE clause, can affect the results. filter() in Django seems to put conditions in the WHERE clause.

Say I have these models:

class Nations(models.Model):
    deleted = models.BooleanField(default=False)


class People(models.Model):
    nation = models.ForeignKey(Nations, on_delete=models.CASCADE)
    deleted = models.BooleanField(default=False)

And I want to find the nations with no people in them, I could do:

SELECT nations.id, nations.deleted
FROM nations
LEFT JOIN people
   ON people.nation_id = nations.id
WHERE people.id IS NULL;

But let's say people can be soft deleted, and I want to find nations with no undeleted people in them. I would do:

SELECT nations.id, nations.deleted
FROM nations
LEFT JOIN people
   ON people.nation_id = nations.id
   AND people.deleted IS FALSE
WHERE people.id IS NULL;

However, in Django, if I do:

Nations.objects.filter(people__id__isnull=True, people__deleted=False)

This is equaivalent to the query:

SELECT nations.id, nations.deleted
FROM nations
LEFT JOIN people
   ON people.nation_id = nations.id
WHERE people.id IS NULL
   AND people.deleted IS FALSE;

Unlike the desired query, this will not include nations that have deleted people only! How can I get Django to move the soft delete check into the ON clause?

I'm surely overlooking something, but both queries look identical to me, and they do not even seem to make sense. `people.id IS NULL` means there is no matching row in on the right hand side (as you say, nations without people). What sense does it make to further filter the non-existing people on `people.deleted IS FALSE`? Did you actually intend an `OR` instead of `AND`? — Endre Both, Feb 14 '19 at 19:12
You are right that the `people.deleted IS FALSE` unnecessary in the last query. It is very necessary in the second to last query, which is the desired output. I kept it included because that is what Django runs and to emphasize the different location of the `people.deleted IS FALSE`. — Noam, Feb 14 '19 at 22:00

score 3 · Accepted Answer · answered Mar 23 '19 at 16:58

There is a little-known feature since Django 2.0 named FilteredRelation which allows adding additional filters to a relationship. The result is an additional criterion in the ON clause of the join:

nations = (Nations.objects
    .annotate(no_deleted=FilteredRelation(
        'people',
        condition=Q(people__deleted=False)))
    .filter(no_deleted=None)
)

print(str(nations.query))

The resulting SQL looks pretty much like what you are after:

SELECT "app_nations"."id",
       "app_nations"....
FROM "app_nations"
LEFT OUTER JOIN "app_people" no_deleted ON 
    ("app_nations"."id" = no_deleted."nations_id"
     AND (no_deleted."deleted" = FALSE))
WHERE no_deleted."id" IS NULL

score 0 · Answer 2 · answered Feb 14 '19 at 19:52

0

Reading you last sentence it seems you want to include nations with no people and nations with only deleted people. To get that, you simply exclude those nations that have undeleted people:

qs = Nations.objects.exclude(people__deleted=False)

answered Feb 14 '19 at 19:52

Endre Both

5,540
1
26
31

You're right that this will work for this specific use case, which does help me. However, I'm hoping for a more generic answer about how to add conditions to an ON clause using Django. – Noam Feb 14 '19 at 22:02

How to put Django query filter in ON clause instead of WHERE clause?

2 Answers2

Linked