17

Django annotations is great for average, min/max, etc. It also does Count. So does that generate the same SQL as if I used the older .count() on the queryset? Or does it generate more efficient SQL in some cases? Or worse SQL?

Sorry, to clarify, I meant to compare the count() operation against something like aggregate(Count('id')) where id is the PK of the table.

So with that, I believe Brian has the correct answer. In short, count() is simply a special case of aggregate().

Xerion
  • 3,141
  • 5
  • 30
  • 46

2 Answers2

20

Calling the queryset's .count() method ultimately calls Count().

Specifically: django.db.models.QuerySet.count() calls django.db.models.sql.Query.get_count(), which calls django.db.models.sql.Query.add_count_column(), which adds django.db.models.sql.aggregates.Count to the query.

The main difference between the two is that when you use Count directly, you specify the fields you want to count, whereas when you call .count() on the queryset, this will result in SELECT COUNT(*)... (except when you also use distinct() or when you limit the fields in the select clause, in which case it's more complicated).

Brian from QuantRocket
  • 5,268
  • 1
  • 21
  • 18
13

Apples and oranges. .count() does a SQL count on the current queryset. The Count aggregate, however, runs a count on relationships you specify on the queryset.

Pizza.objects.count() # Total amount of pizzas

Pizza.objects.aggregate(topping_count=Count('toppings')) # Total amount of toppings associated to a pizza

Pizza.objects.annotate(topping_count=Count('toppings')) # Total amount of toppings on each pizza
Chris Pratt
  • 232,153
  • 36
  • 385
  • 444
  • Assuming Pizza and Topping have a m2m relationship, calling aggregate on Pizza as in your second line will not return the total number of toppings for each pizza; it will outer join Pizza to Topping and basically count the number of joins. To get the number of toppings for each pizza you need to use annotate() instead of aggreate(). – Brian from QuantRocket Oct 31 '11 at 18:51
  • Sorry. I was off on my description of what's returned by aggregate. Updated the description and added `annotate` version to highlight the difference – Chris Pratt Oct 31 '11 at 18:58
  • 1
    If a topping is tied to more than one pizza, it will get joined to multiple pizzas in `Pizza.objects.aggregate(topping_count=Count('toppings'))` and thus this count will return more than the total number of toppings. To get the total number of toppings tied to a pizza (without duplicate counting), you'd probably want to do something like `Topping.objects.exclude(pizza_set=None).count()`. – Brian from QuantRocket Oct 31 '11 at 19:06