合并必须至少包含两个表达式 [英] Coalesce must take at least two expressions

问题描述

我正在使用Django和Python 3.7.我想写一个Coalesce表达式来帮助我写一个更大的Django查询.我有

I'm using Django and Python 3.7. I want to write a Coalesce expression to help me write a larger Django query. I have

            Coalesce(
                F("votes")
                -
                Subquery(relevant_hour_stats.values('votes_threshold')[:1]),
                output_field=models.FloatField())

这是上下文中的表达式...

Here is the expression in context ...

qset = (
    ArticleStat.objects
        .all()
        .annotate(
        shifted_article_create_hour=ExtractHour(ExpressionWrapper(
            F('article__created_on')
            +
            timedelta(seconds=avg_fp_time_in_seconds),
            output_field=models.DateTimeField()
        ))
    )
        .annotate(
        votes_above_threshold=(
                Coalesce(
                    F("votes")
                    -
                    Subquery(relevant_hour_stats.values('votes_threshold')[:1]),
                    output_field=models.FloatField())
        ),
    )
        .filter(
        votes_above_threshold__gt=0,
    )
)

但这会导致

Coalesce must take at least two expressions

抱怨这条线

output_field=models.FloatField()

据我所知，我有两个表达式.该错误还能指的是什么?

as far as I can tell, I have two expressions. What else could the error be referring to?

推荐答案

django中的表达式"是django.db.models.expressions.Expression的实例.

"Expression" in the terms of django are instances of django.db.models.expressions.Expression.

F('votes') —表达式.

Subquery(...) —表达式.

但是expression+expression == combined_expression因此F(...) - Subquery(...)是单个复杂"表达式.

But expression+expression == combined_expression so F(...) - Subquery(...) is a single "complex" expression.

您需要的是Coalesce的第二个 POSITIONAL 参数:

What you need is a second POSITIONAL argument for Coalesce:

Coalesce(
    (F(...) - Subquery(relevant_hour_stats.values('votes_threshold')[:1])),  # First expression
    Value(0.0),  # Second expression
    output_field=models.FloatField()
)

而且我认为子查询可能会导致NULL(不是F())，因此最好只在Coalesce中包装子查询:

And I think that subquery can potentially result in NULL (not F()), so it's better to wrap only Subquery in the Coalesce:

qset = (
    ArticleStat.objects
        .all()
        .annotate(
            shifted_article_create_hour=ExtractHour(
                ExpressionWrapper(
                    F('article__created_on') + timedelta(seconds=avg_fp_time_in_seconds),
                    output_field=models.DateTimeField()
                )
            ),
        )
        .annotate(
            votes_above_threshold=(  
                # Single expression F()-Coalesce(...) wrapped in parenthesis
                F("votes") - Coalesce(
                    # 2 expressions as arguments to Coalesce:
                    Subquery(relevant_hour_stats.values('votes_threshold')[:1]),
                    Value(0.0),
                    output_field=models.FloatField(),
                )
            ),
        )
        .filter(
            votes_above_threshold__gt=0,
        )
)

这篇关于合并必须至少包含两个表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！