Django Count of Items in a Field
<p>models.py</p>

<pre><code>class Event(models.Model):
    name = models.CharField(max_length=20, unique=True)
    distance = models.IntegerField()
    date = models.DateField()

class Category(models.Model):
    name = models.CharField(max_length=20, unique=True)
    description = models.CharField(max_length=20, unique=True)
    isnew = models.BooleanField(default=False)

class Result(models.Model):
    event = models.ForeignKey(Event)
    category = models.ForeignKey(Category)
    score = models.IntegerField()
</code></pre>

<p>I want to do a query to return a count of each unique Category in the Result table, for a given Event.</p>

<p>What I'm doing now is something like:</p>

<pre><code>results = Result.objects.filter(event=myevent)
categorycountdict = {}
for r in results:
    if r.category in categorycountdict:
        categorycountdict[r.category] += 1
    else:
        categorycountdict[r.category] = 1
</code></pre>

<p>Is there a better way, perhaps by query instead of python.</p>

<p>There is <a href="https://docs.python.org/2/library/collections.html#collections.Counter" rel="nofollow">collections.Counter</a> in python standard library:</p>

<pre><code>results = Result.objects.filter(event=myevent).select_related('category')
c = Counter(r.category for r in results)
</code></pre>

<p>Now c is a dict-like object where keys are Category instances, and values are counts.</p>

<p>This option is not suitable for large datasets though, since it doesn't use database features. So if you have a lot of data, approaches that use values, annotate and Count are more suitable.</p>

<p><a href="https://docs.djangoproject.com/en/1.10/ref/models/querysets/#select-related" rel="nofollow">select_related</a> is used here in order to eliminate database hit for every result. Basically it makes 1 query with join and generates python objects for categories.</p>

<hr>

<hr>

<p>It turns out, that ManyToManyField keeps track of unique records only, so below answer is incorrect.</p>

<p>Your Event and Category models expose many-to-many connection through Result model.</p>

<p>You can express it using <a href="https://docs.djangoproject.com/en/1.10/ref/models/fields/#django.db.models.ManyToManyField.through" rel="nofollow">through</a> in Django (note categories ManyToManyField):</p>

<pre><code>class Event(models.Model):
    name = models.CharField(max_length=20, unique=True)
    distance = models.IntegerField()
    date = models.DateField()
    categories = models.ManyToManyField(Category, through='Result', related_name='events')
</code></pre>

<p>In your code, for given event you could query like this:</p>

<pre><code>event.categories.count()
</code></pre>

<p>Taking Alasdair note into account:</p>

<pre><code>event.categories.values('pk').annotate(count=Count('pk'))
</code></pre>

python django django-models