如何在django查询集中获取表的计算元素？ [英] How can I get computed elements of a table in a django queryset?

问题描述

我正在尝试使用django的queryset API来模拟以下查询：

I'm trying to use django's queryset API to emulate the following query:

SELECT EXTRACT(year FROM chosen_date) AS year, 
EXTRACT(month FROM chosen_date) AS month,
 date_paid IS NOT NULL as is_paid FROM 
    (SELECT (CASE WHEN date_due IS NULL THEN date_due ELSE date END) AS chosen_date,* FROM invoice_invoice) as t1;

这个想法主要是在某些情况下，我宁愿使用在某些情况下，date_due 列而不是日期列，但是由于 date_due 可选，我有时必须使用 date 作为回退，并创建一个计算列 selected_date 不必更改其余的查询。

The idea is mainly that in certain situations, I'd rather use the date_due column rather than the date column in some situations, but that , since date_due is optional, I sometimes have to use date as a fallback anyways, and create a computed column chosen_date to not have to change the rest of the queries.

这是我第一次尝试模拟这个，我无法真正看到如何正确地由于基础api的空测试我去了额外的：

Here was a first stab I did at emulating this, I was unable to really see how to properly due the null test with the base api so I went with extra:

if(use_date_due):
    sum_qs = sum_qs.extra(select={'chosen_date': 'CASE WHEN date_due IS NULL THEN date ELSE date_due END'})
else: 
    sum_qs = sum_qs.extra(select={'chosen_date':'date'})
sum_qs = sum_qs.extra(select={'year': 'EXTRACT(year FROM chosen_date)',
                              'month': 'EXTRACT(month FROM chosen_date)',
                              'is_paid':'date_paid IS NOT NULL'})

但是我遇到的问题是当我运行第二个查询时，我得到一个错误，如何 selected_date 列不存在。稍后我尝试使用计算列（例如从 annotate（）调用中）时遇到类似的错误，但在文档中没有找到有关计算列的方式与基不同。有没有人对此有任何见解？

But the issue I'm having is when I run the second query, I get an error on how the chosen_date column doesn't exist. I've had similar errors later on when trying to use computed columns (like from within annotate() calls), but haven't found anything in the documentation about how computed columns differ from "base" ones. Does anyone have any insight on this?

（编辑的python代码，因为以前的版本有一个明显的逻辑缺陷（忘记了其他分支）仍然不起作用） p>

(edited python code because previous version had an obvious logic flaw (forgot the else branch). still doesn't work)

推荐答案

简答案
如果您使用 extra（select = ...）
那么你不能在后续调用$ code filter（）。
另外，正如您所发现的，您不能在以后调用
extra（select = ...）中使用别名列或 extra（where = ...）。

Short answer: If you create an aliased (or computed) column using extra(select=...) then you cannot use the aliased column in a subsequent call to filter(). Also, as you've discovered, you can't use the aliased column in later calls to extra(select=...) or extra(where=...).

试图解释为什么：

例如：

qs = MyModel.objects.extra(select={'alias_col': 'title'})

#FieldError: Cannot resolve keyword 'alias_col' into field...
filter_qs = qs.filter(alias_col='Camembert')

#DatabaseError: column "alias_col" does not exist
extra_qs = qs.extra(select={'another_alias': 'alias_col'})

filter_qs 将尝试生成一个查询，如：

filter_qs will try to produce a query like:

SELECT (title) AS "alias_col", "myapp_mymodel"."title"
FROM "myapp_mymodel"
WHERE alias_col = "Camembert";

和 extra_qs 尝试像： p>

And extra_qs tries something like:

SELECT (title) AS "alias_col", (alias_col) AS "another_alias",
        "myapp_mymodel"."title"
FROM "myapp_mymodel";

这两个都不是有效的SQL。一般来说，如果要在查询的SELECT或WHERE子句中多次使用计算列的别名，您实际上每次都需要计算它。这就是为什么罗马派卡尔的答案解决了你的具体问题 - 而不是试图计算 selected_date 一次，然后再次使用它，以便每次需要时计算它。

Neither of those is valid SQL. In general, if you want to use a computed column's alias multiple times in the SELECT or WHERE clauses of query you actually need to compute it each time. This is why Roman Pekar's answer solves your specific problem - instead of trying to compute chosen_date once and then use it again later he computes it each time it's needed.

您在问题中提及注释/聚合。您可以使用 annotate（）创建的别名使用 filter（）（所以我有兴趣看到你所说的类似的错误，在我的经验中相当强大）。这是因为当您尝试过滤由annotate创建的别名时，ORM会识别您正在执行的操作，并使用创建别名的计算来替换别名。

You mention Annotation/Aggregation in your question. You can use filter() on the aliases created by annotate() (so I'd be interested in seeing the similar errors you're talking about, it's been fairly robust in my experience). That's because when you try to filter on an alias created by annotate, the ORM recognizes what you're doing and replaces the alias with the computation that created it.

所以例如：

qs = MyModel.objects.annotate(alias_col=Max('id'))
qs = qs.filter(alias_col__gt=0)

产生如下：

SELECT "myapp_mymodel"."id", "myapp_mymodel"."title",
        MAX("myapp_mymodel"."id") AS "alias_col"
FROM "myapp_mymodel"
GROUP BY "myapp_mymodel"."id", "myapp_mymodel"."title"
HAVING MAX("myapp_mymodel"."id") > 0;

使用HAVING MAX alias_col> 0将无法正常工作。

Using "HAVING MAX alias_col > 0" wouldn't work.

我希望这是有帮助的。如果有什么我已经解释不好让我知道，我会看看是否可以改善它。

I hope that's helpful. If there's anything I've explained badly let me know and I'll see if I can improve it.

这篇关于如何在django查询集中获取表的计算元素？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！