Possibly impossible Django question: sort admin field on Count of ForeignKey with time filter?

Question

I have a list of Book objects in Django, and I want admin users to be able to sort them by the number of attached Transations in the past three years.

I can already do this for the number of Transaction objects over all time, using annotate and a model manager, as below:

class Book(models.Model):
    title = models.CharField(max_length=400)
    def count(self):
        return self.transactioncount/2
    count.admin_order_field = 'count'
class Transaction(models.Model):
    book = models.ForeignKey(Book)
    transaction_date = models.DateTimeField()
class BookManager(models.Manager):
    def get_query_set(self):
        return super(BookManager,self).get_query_set().annotate(transactioncount=(Count('transaction')))

But I don't know how to do this for transactions in the past three years - I guess I need to rewrite my annotate function, or use something more complex?

Is this even possible in Django?

Many thanks.

score 1 · Answer 1 · answered Mar 08 '11 at 18:27

You cannot filter inside the annotate so a complex expression like counting transactions within a time range would need to be done using extra: http://docs.djangoproject.com/en/1.2/ref/models/querysets/#extra

Book.objects.extra(
    select={
        'recent_transaction_count': "SELECT COUNT(*) FROM app_transaction WHERE app_transaction.book_id = app_book.id AND app_transaction.transaction_date > '2008-03-08'"
    }
)

You would need to replace app with the app name which these models are contained. You can parametrize the date using select_params.

Possibly impossible Django question: sort admin field on Count of ForeignKey with time filter?

1 Answers1