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')

Answer 2

You definitely can't order by your custom method (because your database doesn't know about it).

Now, it seems hard to do what you want at the model level, so maybe move it to the view and order using python, like this:

sorted(Customer.objects.all(), key=get_last_order_date)