In Django, combining and ordering counts across two model fields

I have these two simplified models, representing people and letters that have been sent and received by those people:

class Person(models.Model):
    name = models.CharField(max_length=200, blank=False)

class Letter(models.Model):
    sender = models.ForeignKey(Person, blank=False, related_name="letters_sent")
    recipient = models.ForeignKey(Person, blank=False, related_name="letters_received")

I want to display a list of Persons ordered by how many times they've sent or received any letters.

I started doing this very laboriously, but figure there must be a simpler way.

e.g., this gets the Person IDs and counts of how many times they've sent a Letter:

senders = (
    Letter.objects.order_by()
    .values(person_id=F("sender"))
    .annotate(count=Count("pk"))
    .distinct()
)

I can do the same for recipients.
Then combine the two into a single list/dict, adding the counts together.
Sort that.
Then inflate that data with the actual Person objects, and pass that to the template.

But I feel there must be a less laborious way, starting, I think, with a single query to get the combined sent + received counts.

You should be able to count all the letters and annotate that onto the person queryset. That will be less laborious in terms of written Django code (though I couldn't say if it'd be less laborious for the database or not).

In any case, this ought to be in the right neighborhood for what I'm describing:

queryset = Person.objects.annotate(
    letter_count=(
        Count('letters_sent') + Count('letters_received'))
    )
).order_by('-letter_count')
Back to Top