Case/When seems to ungroup the results of a values().annotate() query

[Eric Palmitesta]

I've been reading ​https://docs.djangoproject.com/en/1.11/topics/db/aggregation/ and ​https://docs.djangoproject.com/en/1.11/ref/models/conditional-expressions/ and I can't tell if this is a bug, or if I'm just using annotate() incorrectly, but I've asked on ​StackOverflow, ​django-users and #django on freenode, and so far no one seems to have any suggestions.

Let's say I have an Order model, and I want a payment breakdown grouped by user.

Here's the Order model:

    class Order(models.Model):    
        CASH = 'c'
        CARD = 'a'
        PAYMENT_TYPES = (
            (CASH, 'Cash'),
            (CARD, 'Card'),
        )
        user = models.ForeignKey(User, on_delete=models.PROTECT, null=True, blank=True)
        payment_type = models.CharField(max_length=1, choices=PAYMENT_TYPES)
        grand_total = models.DecimalField(max_digits=8, decimal_places=2)

Here's a values() + annotate() query showing me the total per user:

    query = Order.objects.values(
        'user'
    ).annotate(
        total=Sum('grand_total'),
    )

The result, so far so good:

    User     Total
    --------------
    User 1   300
    User 2   250

However, when I add Case/When conditions to the query:

    query = Order.objects.values(
        'user'
    ).annotate(
        cash=Case(When(payment_type=Order.CASH, then=Sum('grand_total')), default=Value(0)),
        card=Case(When(payment_type=Order.CARD, then=Sum('grand_total')), default=Value(0)),
        total=Sum('grand_total'),
    )

I get this result, which is not what I want:

    User     Cash      Card      Total
    ----------------------------------
    User 1   300       0         300
    User 2   200       0         200
    User 2   0         50        50

Of course, this is what I want:

    User     Cash      Card      Total
    ----------------------------------
    User 1   300       0         300
    User 2   200       50        250

Is the Case/When undoing the GROUP BY that values() is giving me?

Note: The Order model doesn't have default ordering, but just in case, upon reading ​https://docs.djangoproject.com/en/1.11/topics/db/aggregation/#interaction-with-default-ordering-or-order-by when using values(), I've tried adding .order_by() and .order_by('user') to my query, which did not change the result.

[shangdahao]

The annotate() method will call query.set_group_by(), ​code

So,

Order.objects.values(
        'user'
    ).annotate(
        cash=Case(When(payment_type=Order.CASH, then=Sum('grand_total')), default=Value(0)),
        card=Case(When(payment_type=Order.CARD, then=Sum('grand_total')), default=Value(0)),
        total=Sum('grand_total'),
    )

will add payment_type to query.group_by.

I think it is good idea to a add support to annotate(), let it not group_by its value.

[Simon Charette]

Hi I think you want to refer to ​the conditional aggregation section of the documentation in this this case.

Your aggregate functions should wrap your Case statement and not the other way around.

Order.objects.values(
    'user',
).annotate(
    cash=Sum(Case(When(payment_type=Order.CASH, then=F('grand_total')))),
    card=Sum(Case(When(payment_type=Order.CARD, then=F('grand_total')))),
    total=Sum('grand_total'),
)

Also, you might be interested to learn that ​you'll be able to remove a lot of this boiler plat in Django 2.0

Order.objects.values(
    'user',
).annotate(
    cash=Sum('grand_total', filter=Q(payment_type=Order.CASH)),
    card=Sum('grand_total', filter=Q(payment_type=Order.CARD)),
    total=Sum('grand_total'),
)

Which will also perform slightly faster on database support FILTER (WHERE) expression.

[Eric Palmitesta]

Oh I see, that makes sense. Thanks Simon.