[Django]-Coalesce must take at least two expressions

by

3👍

✅

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

F('votes') — expression.

Subquery(...) — expression too.

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

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

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,
        )
)
👤imposeren

Leave a comment