Opened 21 months ago

Closed 21 months ago

Last modified 20 months ago

#28599 closed Bug (invalid)

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

Reported by: Eric Palmitesta Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (3)

comment:1 Changed 21 months ago by 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.

comment:2 Changed 21 months ago by Simon Charette

Resolution: invalid
Status: newclosed

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.

comment:3 Changed 20 months ago by Eric Palmitesta

Oh I see, that makes sense. Thanks Simon.

Note: See TracTickets for help on using tickets.
Back to Top