如何将Django查询过滤器放在ON子句而不是WHERE子句中？ [英] How to put Django query filter in ON clause instead of WHERE clause?

问题描述

在SQL中，条件的放置（无论是在ON子句中还是WHERE子句中）都会影响结果。 Django中的 filter（）似乎将条件放在WHERE子句中。

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.

说我有以下模型：

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;

但是，在Django中，如果我这样做：

However, in Django, if I do:

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

这等效于查询：

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?

推荐答案

自Django以来，有一个鲜为人知的功能2.0名为 FilteredRelation ，它允许向关系中添加其他过滤器。结果是联接的 ON 子句中的附加条件：

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))

生成的SQL看起来很像您所追求的：

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

这篇关于如何将Django查询过滤器放在ON子句而不是WHERE子句中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！