Django most efficient way to count same field values in a query

djangodjango-modelsdjango-queryset

Lets say if I have a model that has lots of fields, but I only care about a charfield. Lets say that charfield can be anything so I don't know the possible values, but I know that the values frequently overlap. So I could have 20 objects with "abc" and 10 objects with "xyz" or I could have 50 objects with "def" and 80 with "stu" and i have 40000 with no overlap which I really don't care about.

How do I count the objects efficiently? What I would like returned is something like:

{'abc': 20, 'xyz':10, 'other': 10,000}

or something like that, w/o making a ton of SQL calls.

EDIT:

I dont know if anyone will see this since I am editing it kind of late, but…

I have this model:

class Action(models.Model):
    author = models.CharField(max_length=255)
    purl = models.CharField(max_length=255, null=True)

and from the answers, I have done this:

groups = Action.objects.filter(author='James').values('purl').annotate(count=Count('purl'))

but…

this is what groups is:

{"purl": "waka"},{"purl": "waka"},{"purl": "waka"},{"purl": "waka"},{"purl": "mora"},{"purl": "mora"},{"purl": "mora"},{"purl": "mora"},{"purl": "mora"},{"purl": "lora"}

(I just filled purl with dummy values)

what I want is

{'waka': 4, 'mora': 5, 'lora': 1}

Hopefully someone will see this edit…

EDIT 2:

Apparently my database (BigTable) does not support the aggregate functions of Django and this is why I have been having all the problems.

Best Answer

You want something similar to "count ... group by". You can do this with the aggregation features of django's ORM:

from django.db.models import Count

fieldname = 'myCharField'
MyModel.objects.values(fieldname)
    .order_by(fieldname)
    .annotate(the_count=Count(fieldname))

Previous questions on this subject: