How to count foreign key attribute values in Django

Question

Given models set up like this:

class Author(models.Model):
    name = models.CharField(max_length=255)

class Genre(models.Model):
    name = models.CharField(max_length=255)

class Book(models.Model):
    title = models.CharField(max_length=255)
    author = models.ForeignKey(Author, on_delete=models.CASCADE)
    genre = models.ForeignKey(Genre, on_delete=models.CASCADE)

How can I count the number of genres each author has written for? I would ideally like a dictionary of genres and book counts for each author.

[{id: author.id, genres: {genre.id: number_of_books}}]

This can easily be achieved with a query to Author for each genre, however this would result in [number of authors]*[number of genres] queries. I would like to find a more efficient method if possible.

https://docs.djangoproject.com/en/2.2/topics/db/aggregation/ — bruno desthuilliers, Dec 11 '19 at 12:56
https://stackoverflow.com/questions/15306897/django-reverse-lookup-of-foreign-keys — Niladry Kar, Dec 11 '19 at 13:22

Willem Van Onsem · Accepted Answer · 2019-12-12T08:35:06.790

We can query on the Book object, like:

from django.db.models import Count, F

qs = Book.objects.annotate(
    author_id=F('author__pk'),
    genre_id=F('genre__pk')
).values('author_id', 'genre_id').annotate(
    num=Count('pk')
).order_by('author_id', 'genre_id')

But that will not give the format you here specify, but we can do that with post-processing:

from itertools import groupby
from operator import itemgetter

[
    {'id': k, 'genre': { v['genre_id']: v['num'] for v in vs }}
    k, vs for groupby(qs, itemgetter('author_id'))
]

How to count foreign key attribute values in Django

1 Answers1