如何在 Django 的相关模型中按带注释的 Count() 排序 [英] How to sort by annotated Count() in a related model in Django

问题描述

我正在 Django 中构建食物日志数据库，但遇到了与查询相关的问题.

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

我已经设置了我的模型，以包括(除其他外)通过消费模型通过 M2M 字段消费者"连接到用户模型的 Food 模型.Food 模型描述食物菜肴，Consumption 模型描述用户对食物的消费(日期、数量等).

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")

我想创建一个查询，该查询返回按 Food 对象在该用户的 Consumption 表中出现的次数(用户食用该食物的次数)排序的所有 Food 对象.

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).

我正在尝试以下内容:

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

但这当然会计算与 Food 对象相关的所有 Consumption 对象，而不仅仅是与用户关联的那些.我需要更改我的模型还是我只是在查询中遗漏了一些明显的东西?

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?

这是一个非常耗时的操作(除其他外，它用于填充前端的自动完成字段)并且 Food 表有几千个条目，所以我宁愿在数据库端进行排序，而不是执行蛮力方法并迭代结果:

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()

然后使用 python sort 对它们进行排序.我不认为这种方法会随着用户群的增加而很好地扩展，而且我想从一开始就尽可能将数据库设计为面向未来.

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.

有什么想法吗?

推荐答案

也许是这样的?

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

这篇关于如何在 Django 的相关模型中按带注释的 Count() 排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！