Django Models Group By

Question

I have this simple SQL query -

SELECT pid, COUNT(*) AS docs FROM xml_table WHERE suid='2' GROUP BY pid;

How do I get this using Django ORM (i.e. django models). Basically I am not getting how to do GROUP BY?

score 6 · Accepted Answer · answered May 10 '11 at 08:51

6

XML_table.objects.filter(suid='2').values('pid').annotate(docs=Count('pid')).order_by()

answered May 10 '11 at 08:51

DrTyrsa

score 1 · Answer 2 · answered May 10 '11 at 10:45

1

This works very nicely.

from collections import defaultdict
count = defaultdict( int )
for doc in XML_Table.objects.filter(suid='2'):
    count[doc.pid] += 1

It's not SQL. Often it's faster than SQL because it doesn't impose a sort on a large table or join result.

answered May 10 '11 at 10:45

S.Lott

I'd quibble with "often". Anybody about to use this should test first. I came here because I needs counts for over a million rows. Your queryset would have to return all million. Additionally, you're returning the entire row and instantiating it -- a .values() should be a lot quicker if you go this route. – James S Mar 28 '14 at 18:01

2 Answers2