Django: Any way to sort on model method in admin interface?

Question

I have models as follows:

class Book(models.Model):
    title = models.CharField(max_length=400) 
    def transactions_all_time(self):
        latest_transactions = Transaction.objects.filter(book=self, transaction_type=0)
        return len(latest_transactions)
class Transaction(models.Model):
    book = models.ForeignKey(Book)
    user = models.ForeignKey(User)
    transaction_type = models.IntegerField(choices=TRANSACTION_TYPES)

I'd really like to find a way to list a table of books by title and transactions_all_time in the admin interface, and make this table sortable on the transactions_all_time field (or simply the number of related transactions, if that's easier).

I know that there isn't a straightforward way to do this - could anyone suggest a possible approach? Could I write some kind of custom admin view?

Thanks for your help.

score 4 · Accepted Answer · edited May 23 '17 at 09:58

4

First of all you shouldn't call len() on the queryset to get the number of items as it will not translate to sql's COUNT but fetch all items of the database instead. Better use the queryset's count() method:

latest_transactions = Transaction.objects.filter(book=self, transaction_type=0).count()

Sorting by a ForeignKey's count should be possible with something like that:

from django.db.models import Count
queryset = Book.objects.filter(transaction_type=0) \
                .annotate(num_transactions=Count('transaction')) \
                .order_by('-num_transactions')

For instructions on how to add a sortable column to django's admin see this post!

edited May 23 '17 at 09:58

Community

1
1

answered Feb 03 '11 at 21:08

Bernhard Vallant

49,468
20
120
148

Thanks! Unless I'm missing something though, the link doesn't say how to add a sortable column to Django's admin - which is the absolutely critical thing I need to do? Sorry, maybe I'm just being an idiot but I don't see it. – AP257 Feb 04 '11 at 21:22
Sorry I've posted the wrong link. See the first answer from the updated link above! – Bernhard Vallant Feb 04 '11 at 23:34

Elf Sternberg · Answer 2 · 2011-02-04T04:55:13.063

The queryset used by an admin is exposed through the method queryset(). Neither my answer or lazerscience's deals with your transaction type. So I went back to my example and said, "Those are only the awards those films tried to win. What if I only wanted those awards the films actually won?" That addresses your issue of counting only by those transactions of a certain type. Provided your SQL server supports the count() method internally (it had better!) the answer is:

class FilmAdmin(admin.ModelAdmin):
    def queryset(self, request):
        qs = super(FilmAdmin, self).queryset(request)
        qs = qs.extra(
            select = { 'award_count': 'select count(*) from films_filmaward where films_filmaward.film_id=films_film.id and films_filmaward.won=1' }
        )
        qs = qs.order_by('award_count')
        return qs

Additionally, if you want to display the award_count in your list, be aware that since it's not a native field of the model it won't be available when the admin builds its list builder factory. You can work around this by adding the following to your ModelAdmin class:

    def award_count(self, obj):
        return obj.award_count

    list_display = ('id', 'title', 'year', 'contact', 'award_count')

The list builder factory will see that there's a method for generating the award count, and that method will have access to the rows generated by your queryset. Viola', there you go.

Django: Any way to sort on model method in admin interface?

2 Answers2