logo标签列表

如何在Django查询集中获取表的计算元素？

djangopostgresqldjango-queryset
9

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

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列，但是由于date_due是可选的，有时我仍然必须使用date作为备选项，并创建一个计算列chosen_date以不必更改其余查询。这是我第一次尝试模拟它，我无法真正看到如何使用基本API正确地执行空值测试，因此我使用了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'})

但我遇到的问题是，当我运行第二个查询时，会出现一个错误，指出chosen_date列不存在。当我尝试使用计算列（比如在 annotate() 调用中）时，我也遇到了类似的错误，但文档中没有关于计算列与“基础”列之间的区别的说明。有人能提供一些见解吗？
（由于之前版本存在明显的逻辑缺陷（忘记了 else 分支），因此进行了编辑 Python 代码，但仍然无法正常工作。）
我会保持简单，使用原始查询。这就是它们存在的目的。 - dan-klasson
5个回答
7
简短回答：如果您使用extra(select=...)创建别名（或计算）列，则无法在后续调用filter()中使用该别名列。此外，正如您发现的那样，您不能在以后的调用中使用别名列extra(select=...)或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将尝试生成类似于以下查询：

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

而 extra_qs 则尝试类似以下方式：

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

这两种都不是有效的SQL。一般来说，如果你想在查询的SELECT或WHERE子句中多次使用计算列的别名，你实际上需要每次计算它。这就是为什么Roman Pekar的答案解决了您特定的问题 - 他每次需要时计算chosen_date而不是试图先计算一次然后再稍后使用它。
您在问题中提到注释/聚合。您可以在由annotate()创建的别名上使用filter()(我很感兴趣看到您所说的类似错误，因为在我的经验中它非常健壮)。这是因为当您尝试在由annotate创建的别名上进行过滤时，ORM会识别出您正在做什么，并用创建它的计算替换该别名。
因此，以下是一个示例：
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”是无效的。

希望这有所帮助。如果我解释得不好，请让我知道，我会看看是否可以改进。

3

我是一位有用的助手，可以为您进行翻译。以下是需要翻译的内容：

好的，这里有一些解决方法。

1. 在您的特定情况下，您可以通过增加一个额外步骤来完成它：

if use_date_due:
    sum_qs = sum_qs.extra(select={
                          'year': 'EXTRACT(year FROM coalesce(date_due, date))',
                          'month': 'EXTRACT(month FROM coalesce(date_due, date))',
                          'is_paid':'date_paid IS NOT NULL'
                        })

2. 也可以使用纯Python来获取所需的数据：

for x in sum_qs:
    chosen_date = x.date_due if use_date_due and x.date_due else x.date
    print chosen_date.year, chosen_date.month

或者
[(y.year, y.month) for y in (x.date_due if use_date_due and x.date_due else x.date for x in sum_qs)]

3. 在 SQL 世界中，计算新字段的方法通常是使用子查询或 公共表达式。我更喜欢使用公共表达式，因为它更易读。可以像这样：

with cte1 as (
    select
        *, coalesce(date_due, date) as chosen_date
    from polls_invoice
)
select
    *,
    extract(year from chosen_date) as year,
    extract(month from chosen_date) as month,
    case when date_paid is not null then 1 else 0 end as is_paid
from cte1

你也可以链接任意数量的 CTE：

with cte1 as (
    select
        *, coalesce(date_due, date) as chosen_date
    from polls_invoice
), cte2 as (
    select
        extract(year from chosen_date) as year,
        extract(month from chosen_date) as month,
        case when date_paid is not null then 1 else 0 end as is_paid
    from cte2
)
select
    year, month, sum(is_paid) as paid_count
from cte2
group by year, month

在Django中，您可以使用原始查询，例如：
Invoice.objects.raw('
     with cte1 as (
        select
            *, coalesce(date_due, date) as chosen_date
        from polls_invoice
    )
    select
        *,
        extract(year from chosen_date) as year,
        extract(month from chosen_date) as month,
        case when date_paid is not null then 1 else 0 end as is_paid
    from cte1')

你将会得到一些额外属性的发票对象。

4. 或者你可以直接用普通的Python替换查询中的字段。

if use_date_due:
    chosen_date = 'coalesce(date_due, date)'
else: 
    chosen_date = 'date'

year = 'extract(year from {})'.format(chosen_date)
month = 'extract(month from {})'.format(chosen_date)
fields = {'year': year, 'month': month, 'is_paid':'date_paid is not null'}, 'chosen_date':chosen_date)
sum_qs = sum_qs.extra(select = fields)
1
这可以工作吗？：
from django.db import connection, transaction
cursor = connection.cursor()

sql = """
    SELECT 
        %s AS year, 
        %s 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;
    """ % (connection.ops.date_extract_sql('year', 'chosen_date'),
           connection.ops.date_extract_sql('month', 'chosen_date'))

# Data retrieval operation - no commit required
cursor.execute(sql)
rows = cursor.fetchall()

我认为CASE WHEN和IS NOT NULL都非常数据库无关，至少我认为它们是这样的，因为它们在django测试中以原始格式使用。

1
1
您可以在模型定义中添加一个属性，然后执行以下操作：
@property
def chosen_date(self):
    return self.due_date if self.due_date else self.date

假设您总是可以回退到日期。如果您喜欢，可以在due_date上捕获DoesNotExist异常，然后检查第二个。
您可以像访问其他任何内容一样访问该属性。
至于另一个查询，我不会使用SQL从日期中提取y/m/d，只需使用
model_instance.chosen_date.year

chosen_date应该是一个Python日期对象（如果您在ORM中使用DateField并且此字段在模型中）

实际上我这样做的原因是因为后面我要基于年份和月份进行总和聚合。为了做到这一点，我需要使用values调用我想要按组分组的值...而且我不能在values中使用像__year这样的字段查找。 - rtpg
另一件事是，我用于 chosen_date 的内容取决于上下文：有时我使用带有 date 回退的 due_date，有时只使用 date。 - rtpg
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接