How to sort by annotated Count() in a related model in Django

Question

I'm building a food logging database in Django and I've got a query related problem.

I've set up my models to include (among other things) a Food model connected to the User model through an M2M-field "consumer" via the Consumption model. The Food model describes food dishes and the Consumption model describes a user's consumption of Food (date, amount, etc).

class Food(models.Model):
    food_name = models.CharField(max_length=30)
    consumer = models.ManyToManyField("User", through=Consumption)

class Consumption(models.Model):
    food = models.ForeignKey("Food")
    user = models.ForeignKey("User")

I want to create a query that returns all Food objects ordered by the number of times that Food object appears in the Consumption table for that user (the number of times the user has consumed the food).

I'm trying something in the line of:

Food.objects.all().annotate(consumption_times = Count(consumer)).order_by('consumption_times')`

But this will of course count all Consumption objects related to the Food object, not just the ones associated with the user. Do I need to change my models or am I just missing something obvious in the queries?

This is a pretty time-critical operation (among other things, it's used to fill an Autocomplete field in the Frontend) and the Food table has a couple of thousand entries, so I'd rather do the sorting in the database end, rather than doing the brute force method and iterate over the results doing:

Consumption.objects.filter(food=food, user=user).count()

and then using python sort to sort them. I don't think that method would scale very well as the user base increases and I want to design the database as future proof as I can from the start.

Any ideas?

Answer 1

Perhaps something like this?

Food.objects.filter(consumer__user=user)\
            .annotate(consumption_times=Count('consumer'))\
            .order_by('consumption_times')