django admin enable sorting for calculated fields

Question

I have the following two fields in my db table and model (Model Name: Order):

id, branch_id, product_id, cost, quantity, status, ordered_at

And I have the following code in my OrderModelAdmin:

list_display = (
    'order_number',
    'branch',
    'product',
    'cost',
    'quantity',
    'calculated_total',
    'status',
    'ordered_at',
)

def calculated_total(self, obj):
    return obj.cost * obj.quantity
calculated_total.short_description = _('Total')

Now, I want to enable sorting for this field. In reality, all I need to do is to add a column in my SELECT statement:

 SELECT (t.cost * t.quantity) as TOTAL
 ORDER BY TOTAL

Is there a way I can append an SQL statement for sorting in Django Admin?

Answer 1

It isn't possible to order by the result of the calculated_total method.

However, you can set the default ordering for your model admin by overriding the get_queryset method for your model admin, and ordering by an expression that calculates the same thing.

class OrderModelAdmin(admin.ModelAdmin):
    ...
    def get_queryset(self, request):
        qs = super(OrderModelAdmin, self).get_queryset(request)
        qs = qs.order_by(F('cost')*F('quantity'))
        return qs

A similar approach is to annotate the queryset with the total, and then order by that field. Assuming that cost is a DecimalField and quantity is an IntegerField, you need to use ExpressionWrapper to set the output field. See the docs on Using F() with annotations for more info.

I don't think it's possible to use total directly in list_display. However, you can alter your calculated_total method to access the annotated field. We set calculated_total.admin_order_field = 'total' so that the Django admin allows you to sort on that column by clicking on it.

from django.db.models import F, ExpressionWrapper, DecimalField

class OrderModelAdmin(admin.ModelAdmin):
    list_display = ['name', 'number', 'price', 'calculated_total']

    def calculated_total(self, obj):
        return obj.total
    calculated_total.admin_order_field = 'total'

    def get_queryset(self, request):
        qs = super(OrderModelAdmin, self).get_queryset(request)
        qs = qs.annotate(total=ExpressionWrapper(F('cost')*F('quantity'), output_field=DecimalField())).order_by('total')
        return qs