I know Django 1.1 has some new aggregation methods. However I couldn't figure out equivalent of the following query:
SELECT player_type, COUNT(*) FROM players GROUP BY player_type;
Is it possible with Django 1.1's Model Query API or should I just use plain SQL?
If you are using Django 1.1 beta (trunk):
values('player_type')- for inclusion only
order_by()- for exclusion possible default ordering that can cause not needed fields inclusion in
Django 1.1 does support aggregation methods like count. You can find the full documentation here.
To answer your question, you can use something along the lines of:
from django.db.models import Count q = Player.objects.annotate(Count('games')) print q print q.games__count
This will need slight tweaking depending on your actual model.
Edit: The above snippet generates aggregations on a per-object basis. If you want aggregation on a particular field in the model, you can use the
from django.db.models import Count q = Player.objects.values('playertype').annotate(Count('games')).order_by() print q print q.games__count
order_by() is needed because fields that are in the default ordering are automatically selected even if they are not explicitly passed to
values(). This call to
order_by() clears any ordering and makes the query behave as expected.
Also, if you want to count the field that is used for grouping (equivalent to
COUNT(*)), you can use:
from django.db.models import Count q = Player.objects.values('playertype').annotate(Count('playertype')).order_by() print q print q.playertype__count