CODEWITHSUNDEEP
pythondjango
Joey Coder
Joey Coder

Reputation: 3489

Django: .annotate gives back unexpected results

Currently, I have these two query sets:

(
Event.objects.filter(organizer=1)
.values('pk', 'organizer')
.annotate(
    sold_tickets=Count('attendees', filter=Q(attendees__canceled=False))
)
.order_by('organizer')
)


(
Event.objects.filter(organizer=1)
.values('pk', 'organizer')
.annotate(available_tickets=Coalesce(Sum('tickets__quantity'), 0))
.order_by('organizer')
)

Results are:

<EventQuerySet [{'pk': 6, 'organizer': 1, 'sold_tickets': 1}, {'pk': 1, 'organizer': 1, 'sold_tickets': 529}, {'pk': 5, 'organizer': 1, 'sold_tickets': 1}, {'pk': 4, 'organizer': 1, 'sold_tickets': 2}]>
<EventQuerySet [{'pk': 1, 'organizer': 1, 'available_tickets': 1721}, {'pk': 4, 'organizer': 1, 'available_tickets': 30}, {'pk': 5, 'organizer': 1, 'available_tickets': 10}, {'pk': 6, 'organizer': 1, 'available_tickets': 20}]>

Now my idea was to combine these. However, I always get unexpected and wrong numbers in my query:

(
Event.objects.filter(organizer=1)
.values('pk', 'organizer')
.annotate(
    available_tickets=Coalesce(Sum('tickets__quantity'), 0),
    sold_tickets=Count('attendees', filter=Q(attendees__canceled=False))
)

.order_by('organizer')
)

Here the result

<EventQuerySet [{'pk': 6, 'organizer': 1, 'available_tickets': 20, 'sold_tickets': 2}, {'pk': 1, 'organizer': 1, 'available_tickets': 1765746, 'sold_tickets': 2116}, {'pk': 5, 'organizer': 1, 'available_tickets': 10, 'sold_tickets': 1}, {'pk': 4, 'organizer': 1, 'available_tickets': 60, 'sold_tickets': 4}]>

Is there something about .annotate that I don't understand?

Upvotes: 1

Views: 43

Answers (1)

Diya Li
Diya Li

Reputation: 1088

It seems I got the same problem before, but I not sure if it is exactly your. Try add distinct=True to your annotate, which should be:

.annotate(
    available_tickets=Coalesce(Sum('tickets__quantity'), 0)
).annotate(
    sold_tickets=Count('attendees', filter=Q(attendees__canceled=False), distinct=True)
)

Or you can try add .distinct() like this:

.annotate(
    available_tickets=Coalesce(Sum('tickets__quantity'), 0),
    sold_tickets=Count('attendees', filter=Q(attendees__canceled=False))
).distinct('available_tickets','sold_tickets')

Here is what I found my solution: https://docs.djangoproject.com/en/dev/topics/db/aggregation/#combining-multiple-aggregations

Upvotes: 2

Related Questions