How to put Django query filter in ON clause instea

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?

Answer 1

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)

Answer 2

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