Django：如何用已经过滤的ForeignKey字段的数量注释查询集？ [英] Django: how to annotate queryset with count of filtered ForeignKey field?

问题描述

Django新手问题：）

Django novice question :)

我有以下型号 - 每个评论都是针对某个产品，每个产品都有一个部门：

I have the following models - each review is for a product, and each product has a department:

class Department(models.Model):
    code = models.CharField(max_length=16)
class Product(models.Model):
    id = models.CharField(max_length=40, primary_key=True, db_index=True)
    dept = models.ForeignKey(Department, null=True, blank=True, db_index=True)
class Review(models.Model):
    review_id = models.CharField(max_length=32, primary_key=True, db_index=True) 
    product = models.ForeignKey(Product, db_index=True) 
    time = models.DateTimeField(db_index=True) 

我想对Django进行查询日期范围（2012-01-01至2012-01-08），并返回所有部门的清单，注明部门ID，以及该部门在该日期范围内审查的产品数量。

I'd like to make a Django query for a date range (2012-01-01 to 2012-01-08) and return a list of all departments, annotated with department ID, and the number of products from that department that were reviewed during that date range.

这是炸我的大脑有点：）

This is frying my brain a bit :)

我可以得到所有的时间范围的评论：

I can get all the reviews for a time range:

 reviews = Review.filter(time__range=["2012-01-01", "2012-01-08"])

然后我猜每个评论都有一个产品字段，每个产品都有一个部门代码。但是，如何按照产品和代码分配计数和部门ID？

Then I guess each review has a product field, and each of those products has a department code. But how can I group them by product and code, with counts and department IDs?

或者，最好请求部门，然后用产品数量注释它们，不知何故？

Alternatively, is it best to request the departments, and then annotate them with product counts, somehow?

推荐答案

避免额外的和 code>尽可能。 聚合文档几乎有这种用例：

Avoid extra and raw whenever possible. The aggregation docs have nearly this use case:

直接从文档中：

# Each publisher, each with a count of books as a "num_books" attribute.
>>> from django.db.models import Count
>>> pubs = Publisher.objects.annotate(num_books=Count('book'))
>>> pubs
[<Publisher BaloneyPress>, <Publisher SalamiPress>, ...]
>>> pubs[0].num_books
73

所以，为了你的具体例子修改这个：

So, to modify this for your particular example:

depts = Department.objects.
            filter(product__review__time__range=["2012-01-01", "2012-01-08"]).
            annotate(num_products=Count('product'))

函数调用单独的行只是为了可读性，你应该相应地移动它们。我没有测试过这个，但我认为它应该有效。

The function calls on separate lines is just for readability and you should move them about accordingly. I haven't tested this, but I think it should work.

这篇关于Django：如何用已经过滤的ForeignKey字段的数量注释查询集？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！