Now, to answer myself, I found the solution:
qs =
Reference.objects.all().extra(select={'title_count'
:'COUNT(*)'}).values('title',
'title_count')
qs.query.group_by = ['title']
This gives me exactly the aggregated list of titles with the number of
occurrences. The trick with group_by is undocumented, but mentioned
in this blog post by Eric Florenzano:
http://www.eflorenzano.com/blog/post/secrets-django-orm/
Chris
On Nov 11, 7:43 am, chris <[EMAIL PROTECTED]> wrote:
> Thanks Malcolm,
>
> This brings me one step closer, but not quite there.
>
> On Nov 10, 8:21 pm, Malcolm Tredinnick <[EMAIL PROTECTED]>
> wrote:
>
> > > SELECT title, COUNT (*) AS count FROM persons_reference GROUP BY title
> > > ORDER BY count DESC
>
> > > Trying to get the same from the Django ORM, I do the following:
>
> > > r = Reference.objects.values('title').extra(select={'title_count' :
> > > 'COUNT(*)'}).distinct()
> > You've asked for a queryset that *only* includes the "title" field, so
> > that's what you're given. If you want it to also include the title_count
> > field, you need to describe that to the queryset first and then also
> > include it in the values() call:
>
> > Reference.objects.extra(select={'title_count'
> > :'COUNT(*)'}).distinct().values('title', 'title_count')
>
> This query returns only one row, the total count of all titles. What
> I am trying to achieve is what the SQL above does, namely aggregating
> the titles and give the count for the occurrence of each title.
> I wonder if this could be done with the Django ORM?
>
> Chris
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en
-~----------~----~----~----~------~----~------~--~---
