Django: order by field of many-to-many relation

Question

Given django models A and B:

class A(models.Model):
    things = models.ManyToManyField('B')

class B(models.Model):
    score = models.FloatField()
    text = models.CharField(max_length=100)

How do I get a list of A entities ordered by the text of the highest-scoring B in things?

Answer 1

If I understand you correctly, this should do it. list will contain a list of all the objects of model A sorted by the text of each object's highest scoring thing.

dict = {}
list = []
for a in A.objects.all():
    dict[a] = a.things.all().order_by("-score")[0].text
for k, v in sorted(dict.items(), key=lambda x: x[1]):
    list.append(k)

There might be a prettier way to write it, but I can't think of one off the top of my head...

Answer 2

This sort of thing is really hard. Aggregation can help you if you want to sort by the score:

from django.db.models import Max
A.objects.annotate(highest_b=Max(B__score)).order_by('highest_b')

But in effect you want to do two operations: aggregate by the highest score, then sort by the text of the item with that score. That's tricky no matter what language you do it in. I've written about this issue on my blog - for your purposes, I think doing it in SQL via the .raw() method is probably the easiest.