Django admin: how to sort by one of the custom list_display fields that has no database field

Question

# admin.py
class CustomerAdmin(admin.ModelAdmin):  
    list_display = ('foo', 'number_of_orders')


# models.py
class Order(models.Model):
    bar = models.CharField[...]
    customer = models.ForeignKey(Customer)

class Customer(models.Model):
    foo = models.CharField[...]

    def number_of_orders(self):
        return u'%s' % Order.objects.filter(customer=self).count()  

How could I sort Customers, depending on number_of_orders they have?

admin_order_field property can't be used here, as it requires a database field to sort on. Is it possible at all, as Django relies on the underlying DB to perform sorting? Creating an aggregate field to contain the number of orders seems like an overkill here.

The fun thing: if you change url by hand in the browser to sort on this column - it works as expected!

Anabasis

"The fun thing: if you change url by hand in the browser to sort on this column - it works as expected!" You mean like: /admin/myapp/customer/?ot=asc&o=2 Are you sure? – Wafd

yeah, both asc and dsc. Maybe it just works with decimals. – Anabasis

I don't think it would work with multiple pages. – Agostino

Answer 1

I loved Greg's solution to this problem, but I'd like to point that you can do the same thing directly in the admin:

from django.db import models

class CustomerAdmin(admin.ModelAdmin):
    list_display = ('number_of_orders',)

    def get_queryset(self, request):
    # def queryset(self, request): # For Django <1.6
        qs = super(CustomerAdmin, self).get_queryset(request)
        # qs = super(CustomerAdmin, self).queryset(request) # For Django <1.6
        qs = qs.annotate(models.Count('order'))
        return qs

    def number_of_orders(self, obj):
        return obj.order__count
    number_of_orders.admin_order_field = 'order__count'

This way you only annotate inside the admin interface. Not with every query that you do.

Philologian

Yes, this is a much better way. :) – Shearwater

There's a suggested edit on this answer. I voted to reject it because it removed too much text. I don't know Django, I have no idea whether the proposed code change is worth mentioning. – Tiresias

You can annotate with a name: qs = qs.annotate(number_of_orders=models.Count('order')), and then just write: number_of_orders.admin_order_field = 'number_of_orders', to avoid django's double underscore automatic field name. – Karlik

@Gilles the suggested edit is correct about a simpler number_of_orders definition. This works: def number_of_orders(self, obj): return obj.order__count – Belanger

In addition to Eric's comment (2 arguments self and obj) later on there should be ... Order.objects.filter(customer=obj)... not customer=self. Anyway thanks, just figured it out thanks to this magnificent question. – Calisa

Hi, I've made the edits you suggested in the comments. Great collaboration! :) – Haberman

How to do this with a GenericForeignKey? #34488996 – Erlin

Shoudn't that be get_queryset() instead of queryset() ? – Feld

should be get_queryset(self, request):... for Django 1.6+ – Eniwetok

Greg's answer has the added advantage (or disadvantage, from a performance POV) that it adds the annotation for all query_set calls (objects.filter call) – Whomsoever

Works fine. but when I have search option enabled and try to search based on some other field on this list display, it gets the wrong count and also takes a long time to load. I am assuming its order of operation in which it tries to perform search and calc the count on the custom field is causing it. Anyone else has run into it? – Hofer

Beautiful! So get_queryset is one of the many hooks I can override whenever I want to customize a ModelAdmin? Really need a tutorial on when to override what about there hooks. – Lauritz

This should work for fields that can be retrieved by aggregation but not sure if we have a way for a bit complicated custom fields. e.g. def theme_names(self, obj) -> str: return ','.join(obj.theme.all().values_list('name', flat=True)) – Rozanna

Answer 2

I haven't tested this out (I'd be interested to know if it works) but what about defining a custom manager for Customer which includes the number of orders aggregated, and then setting admin_order_field to that aggregate, ie

from django.db import models 


class CustomerManager(models.Manager):
    def get_query_set(self):
        return super(CustomerManager, self).get_query_set().annotate(models.Count('order'))

class Customer(models.Model):
    foo = models.CharField[...]

    objects = CustomerManager()

    def number_of_orders(self):
        return u'%s' % Order.objects.filter(customer=self).count()
    number_of_orders.admin_order_field = 'order__count'

EDIT: I've just tested this idea and it works perfectly - no django admin subclassing required!

Shearwater

This is better answer compared to the accepted one. The issue I ran into applying the accepted one is when you search something along with that updated queryset at the admin level, it takes too much time and also comes up with wrong count for the results it found. – Hofer

Answer 3

The only way I can think of is to denormalize the field. That is - create a real field that get's updated to stay in sync with the fields it is derived from. I usually do this by overriding save on eith the model with the denormalized fields or the model it derives from:

# models.py
class Order(models.Model):
    bar = models.CharField[...]
    customer = models.ForeignKey(Customer)
    def save(self):
        super(Order, self).save()
        self.customer.number_of_orders = Order.objects.filter(customer=self.customer).count()
        self.customer.save()

class Customer(models.Model):
    foo = models.CharField[...]
    number_of_orders = models.IntegerField[...]

Wafd

This certainly should work, but can't mark it as accepted due to extra DB field involved. Also note missing .count() at the end of query-set line. – Anabasis

fixed the count(). The only other solution (short of subclassing large chunks of contrib.admin) would be a Jquery/Ajaxy hack. – Wafd