Opened 2 years ago

Closed 2 years ago

Last modified 2 years 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 2 years 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 2 years 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 2 years ago by Eric Palmitesta

Oh I see, that makes sense. Thanks Simon.

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