CODEWITHSUNDEEP
pythondjangodjango-models
axevalley
axevalley

Reputation: 335

How to count foreign key attribute values in Django

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.

Upvotes: 1

Views: 235

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476584

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'))
]

Upvotes: 1

Related Questions