CODEWITHSUNDEEP
djangodjango-queryset
Phil Gyford
Phil Gyford

Reputation: 14584

Annotating a Django queryset through two relations

I have models that represent People and Books, which are joined via a through-model that can indicate their role (e.g. 'Author' or 'Editor'):

from django.db import models

class Person(models.Model):
    name = models.CharField()


class Book(models.Model):
    title = models.ChartField()

    people = models.ManyToManyField('Person',
                            through='BookRole', related_name='books')


class BookRole(models.Model):
    person = models.ForeignKey('Person', related_name='book_roles')

    book = models.ForeignKey('Book', related_name='roles')

    role_name = models.CharField()

And I have a Reading model that represents an occasion a Book was read:

class Reading(models.Model):
    book = models.ForeignKey('Book')

    start_date = models.DateField()
    end_date = models.DateField()

I know how to get a list of Persons ordered by the quantity Books associated with them:

from django.db.models import Count
from .models import Person

Person.objects.annotate(num_books=Count('books')) \
                .order_by('-num_books')

But how can I get a list of Persons ordered by the number of times their Books have been read (i.e. the number of Readings related to their Books)?

Upvotes: 0

Views: 327

Answers (1)

Alasdair
Alasdair

Reputation: 308779

You can use the standard django double underscore notation:

Person.objects.annotate(num_readings=Count('books__reading')) \
            .order_by('-num_readings')

See the docs on joins and aggregates for more info.

Upvotes: 4

Related Questions