id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28599	Case/When seems to ungroup the results of a values().annotate() query	Eric Palmitesta	nobody	"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 [https://stackoverflow.com/questions/45802068 StackOverflow], [https://groups.google.com/forum/#!topic/django-users/Q276DNBY5vc 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."	Bug	closed	Database layer (models, ORM)	1.11	Normal	invalid			Unreviewed	0	0	0	0	0	0