How to order a queryset using related_name in django

Question

I've got a User model which is related to the DriverCar model using related_name='driver'. Each user could be related to multiple drivercars. DriverCar model has a field "reference_number" of type integer. I want to show a list of all users ordered by thereference_number of the last DriverCar object related to the user. Using a query like User.objects.filter(archive=False).order_by('-driver__reference_number') gives me the wrong answer. But if I use order_by('-pk') everything works perfectly. Is there any way to do this correctly?

class User(AbstractUser):
    archive = models.BooleanField(default=False)

class DriverCar(models.Model):
    user = models.ForeignKey(User , on_delete=models.CASCADE , related_name='driver')
    reference_number = models.BigIntegerField(default=0, null=True, blank=True)

score 3 · Accepted Answer · answered Jul 26 '22 at 10:51

3

This will combine from a couple of existing answers to other questions...

You will need a subquery to get the value of reference_number for the "last DriverCar object related to the user."

See https://stackoverflow.com/a/60217311/202168

I'm assuming "last" can mean "highest pk" (of DriverCar)

So the subquery will look like:

subquery = (
    Subquery(
        DriverCar.objects.filter(user_id=OuterRef("id")
    )
    .order_by("-pk")
    .values('reference_number')[:1]
)

We can annotate our User queryset with the value from the subquery like:

User.objects.filter(archive=False).annotate(last_driver_reference_number=subquery)

Then we should be able to sort on the annotated field, see https://stackoverflow.com/a/1396313/202168

So:

qs = (
    User
    .objects
    .filter(archive=False)
    .annotate(last_driver_reference_number=subquery)
    .order_by("last_driver_reference_number")
)

answered Jul 26 '22 at 10:51

Anentropic

32,188
12
99
147

Thank you for your help. It worked just perfectly. It's just I want the ordering to be descending. Ascending order works just fine, but when I try to make it descending by using `.order_by("-last_driver_reference_number")` it doesn't work. is there any solution for that? – Fateme Fouladkar Jul 28 '22 at 10:21
what do you mean by "it doesn't work"? – Anentropic Jul 28 '22 at 15:40
It gives me the wrong answer. the result doesn't have a specific order. @anentropic – Fateme Fouladkar Jul 30 '22 at 05:24
are you saying that if you look at the values of `last_driver_reference_number` field in the result set it's not ordered by that? – Anentropic Aug 01 '22 at 09:54

How to order a queryset using related_name in django

1 Answers1