Django Annotate() Multiple Times Causes Wrong Answers

Django has the great new annotate() function for querysets. However I can't get it to work properly for multiple annotations in a single queryset. For example, tour_list = Tour.ob

Solution 1:

Thanks for your comment. That didn't quite work but it steered me in the right direction. I was finally able to solve this by adding distinct to both Count() calls:

Count('tourcomment', distinct=True)

Solution 2:

tour_list = Tour.objects.all().annotate(tour_count=Count('tourcomment',distinct=True) ).annotate(history_count=Count('history',distinct=True) )

You have to add distinct=True to get the proper result else it will return the wrong answer.

Solution 3:

I can't guarantee that this will solve your problem, but try appending .order_by() to your call. That is:

tour_list = Tour.objects.all().annotate(Count('tourcomment')).annotate(Count('history')).order_by()

The reason for this is that django needs to select all the fields in the ORDER BY clause, which causes otherwise identical results to be selected. By appending .order_by(), you're removing the ORDER BY clause altogether, which prevents this from happening. See the aggregation documentation for more information on this issue.