如何在Django中的相关模型中按注释计数排序

Question

我正在使用Django构建一个食品记录数据库，并遇到了查询相关的问题。

我已经设置了我的模型，其中包括了一个食品模型，通过消费模型上的M2M字段"consumer"与用户模型连接。食品模型描述食物菜肴，而消费模型描述用户对食品的消费（日期、数量等）。

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.objects.all().annotate(consumption_times = Count(consumer)).order_by('consumption_times')`

但是这将会计算与食品对象相关联的所有消费对象，而不仅仅是与用户相关联的消费对象。我需要更改我的模型还是在查询中漏掉了一些明显的东西？

这是一个相当时间紧迫的操作（除此之外，它还用于填充前端自动完成字段），而Food表有几千个条目，因此我宁愿在数据库端进行排序，而不是使用暴力方法并迭代结果执行以下操作：

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

然后使用Python的排序来对它们进行排序。我不认为随着用户数量的增加，这种方法会很好扩展，我希望从一开始就尽可能地设计出未来的数据库。

有什么想法吗？

- Jens Alm

---

可能是按外键字段计数排序？的重复。 - Ciro Santilli OurBigBook.com

Answer 1

也许像这样？

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

- SmileyChris

---

但这只会返回已经被消耗过的食物对象，不是吗？我想要返回所有的食物对象，但是按照最常被消耗的顺序排列。如果我按用户筛选，那么我就得不到还没有被消耗的食物。一个想法可能是进行两个查询，第一个查询像你建议的那样获取至少被消耗一次的所有食品项目，然后类似于Food.objects.exclude(consumer__user = user)并用它们填充列表。那样行吗？ - Jens Alm

是的，我会用两个查询来实现它。 - SmileyChris

Answer 2

我遇到了一个非常类似的问题。基本上，我知道你想要的 SQL 查询是：

SELECT food.*, COUNT(IF(consumption.user_id=123,TRUE,NULL)) AS consumption_times
       FROM food LEFT JOIN consumption ON (food.id=consumption.food_id)
       ORDER BY consumption_times;

我希望你能够混合使用聚合函数和F表达式，为F表达式注释而不需要聚合函数，拥有更丰富的F表达式操作/函数集，并且拥有基本上是自动F表达式注释的虚拟字段。这样你就可以做到：

Food.objects.annotate(consumption_times=Count(If(F('consumer')==user,True,None)))\
            .order_by('consumtion_times')

此外，更容易地添加自己的复杂聚合函数也是很好的，但与此同时，以下是一种通过hack方式添加聚合函数的方法。

from django.db.models import aggregates,sql
class CountIf(sql.aggregates.Count):
    sql_template = '%(function)s(IF(%(field)s=%(equals)s,TRUE,NULL))'
sql.aggregates.CountIf = CountIf

consumption_times = aggregates.Count('consumer',equals=user.id)
consumption_times.name = 'CountIf'
rows = Food.objects.annotate(consumption_times=consumption_times)\
                   .order_by('consumption_times')

- sparkyb

---

太棒了！谢谢你，你救了我的一天！我会尝试让它看起来更好一些，但你绝对应该将其提交到Django的跟踪系统中。 - Grégoire Cachet