Django order_by a related _set

Question

I have a model for our customers and a model for their purchases/orders. In our backend/web admin, we want to be able to sort the customer list by their most recent order.

Here is basically what our models look like

class Customer(models.Model):
    username = models.CharField(max_length=32)
    first_name = models.CharField(max_length=322)
    def __unicode__(self):
        return "%s" % self.username
    def get_last_order_date(self):
        self.order_set.latest('purchase_date')

class Order(models.Model):
    customer = models.ForeignKey(Customer)
    purchase_date = models.DateTimeField()

I can display the most recent order of each customer using my get_last_order_date function, however I can't sort by that function (or at least I don't know how). I've tried everything I can think of:

.order_by('order__purchase_date')
.order_by('order__set').latest('purchase_date')

and a lot more with no luck.

Any hints or clues would be very much appreciated.

Thanks.

Answer 1

You can't use order by with a function, as it's done at the database level. You could manually sort the queryset using .sort(key=get_last_order_date), but this won't be very efficient.

A better way would be to use Django's aggregation feature, to get the maximum purchase date for a customer and sort on that:

from django.db.models import Max
Customer.objects.annotate(latest_order=Max('order__purchase_date')).order_by('latest_order')