获取数量在模板中的几个过滤的子查询 [英] Get count on several filtered subqueries in template

问题描述

我最近要求如何获取一个模板中的文件化子查询，并得到一个答案：获取模板中过滤的子查询的计数

现在我意识到我需要这个扩展如下（我认为是很多mroe比我的第一个问题，因此在这里创建一个新的）：

1. 显示与标签不匹配的文章，计数


2. 扩展以允许不同的标签计数。

使用以下型号：

 类别（models.Model）：
 ... 
 
 class SubCategory（models.Model）：
 category = models.ForeignKey（Category）
 .... 
 
 class标签（models.Model）：
 .... 
 
 class文章（models.Model）
 sub_category = models.ForeignKey（SubCategory）
 tag = models.ForeignKey（Tag）
  

我得到以下答案（views.py），它将创建一个num_articles变量，其中包含带有标签的文章数量='xyz'：

  def my_view（请求，其他参数）：
 ... 
子类别= category.sub_category_set.filter（tag__tagname ='xyz'）\ 
 .annotate（num_articles = Count（'article__id'））
  

模板：

  {％子类别中的子类别％} 
 {{subcategory.name}}  -  {{subcategory.num_articles}} 
 {％endfor％} 
  





但现在，我想要做这样的事情，使独立计数在几个不同的标签上，所以xyz的计数不依赖于abc的计数，反之亦然：

  def my_view（request，other，arguments）：
 ... 
 subcategories = cate gory.sub_category_set。 \ 
 filter（tag__tagname ='xyz'）。annotate（num_articles_xyz = Count（'article__id'））\ 
 filter（tag__tagname ='abc'）。annotate（num_articles_abc = Count（'article__id' ））\ 
 filter（tag__tagname ='def'）。annotate（num_articles_def = Count（'article__id'））\ 
 ... 
  pre> 
 
 
要在模板中使用：
  {％子类别％} 
 {{subcategory.name}}  -  {{subcategory.num_articles_xyz}}< br /> 
 {{subcategory.num_articles_abc}}  -  {{subcategory.num_articles_def}} 
 ... 
 {％endfor％} 
  
还要显示所有子类别，即使在任何计数中没有匹配。但是，那个数值为0。
解决方案
 Django没有简单有效的方法ORM。通过注释的实现，ORM中没有办法在同一查询中对不同的过滤集进行注释。即使您再次进行注释，过滤和注释，所有注释也将在上次注释时被过滤的集合上。我甚至不确定你想要的是什么，在原始SQL代码中是可行的，但我不是专家。 
 
 
 
您可以获取所有相关的标签和文章，并在Django中进行实际注释。但是，您必须小心：即使使用 prefetch_related ，如果您以任何实际克隆查询集的方式过滤相关集或更改它，您仍然会执行数据库查询每个查询器。 
 
 
 
以下是您可以做到的一种方式。它还将处理任何没有特定子类别中的文章的标签。
  subcategories = category.sub_category_set.prefetch_related（'tags' ，'articles'）
子类别中的子类别：
 subcategory.num_articles = dict（）
标签中的标签：
 num = len（[a for a subcategory.articles .all（）if a.tag_id == tag.id]）
 subcategory.num_articles [tag.tag_name] = num 
  
在您的模板中：
  {％子类别中的子类别％} 
 { {subcategory.name}}< br /> 
 {％for k，v in subcategory.num_articles.iteritems％} 
 {{k}}  -  {{v}} 
 {％endfor％} 
 {％endfor ％} 
  
这将产生3个数据库查询，而不管标签的数量（4个包括查询 category ），并将在python中执行其余的处理。从数据库获取大数据集可能是昂贵的。如果您需要使用非常大的数据集并查看性能问题，您可能需要查看如何对每个标记进行一些精心优化的查询，以便在python中仅处理几个具有数据处理的大型查询。 
 
I recently asked for how to get count on filetered subqueries in a template and got an answer here: Get count on filtered subqueries in template

Now I realised that I need this extended as following (which I think is a lot mroe than my first question, therefor creating a new one here):

Show articles that doesn't match the tag, but then with 0 as count.
Extend to allow counting on different tags.
With the following model:
class Category(models.Model):
    ...

class SubCategory(models.Model):
    category = models.ForeignKey(Category)
    ....

class Tag(models.Model):
    ....

class Article(models.Model)
    sub_category = models.ForeignKey(SubCategory)
    tag = models.ForeignKey(Tag)
I got the following answer (views.py), which will create a num_articles variable that holds the count of number of articles with tag='xyz':
def my_view(request, other, arguments):
    ...
    subcategories = category.sub_category_set.filter(tag__tagname='xyz') \ 
                            .annotate(num_articles=Count('article__id'))
And the template:
{% for subcategory in subcategories %}
    {{ subcategory.name }} -- {{ subcategory.num_articles }}
{% endfor %}




But now, I want to be able to do something like this, make independent counts on several different tags, so that the count of 'xyz' is not dependent on the count on 'abc', and vice versa:
def my_view(request, other, arguments):
    ...
    subcategories = category.sub_category_set. \
        filter(tag__tagname='xyz').annotate(num_articles_xyz=Count('article__id')) \
        filter(tag__tagname='abc').annotate(num_articles_abc=Count('article__id')) \
        filter(tag__tagname='def').annotate(num_articles_def=Count('article__id')) \
        ...
To use in the template as:
{% for subcategory in subcategories %}
    {{ subcategory.name }} -- {{ subcategory.num_articles_xyz }}<br />
    {{ subcategory.num_articles_abc }} -- {{ subcategory.num_articles_def }}
    ...
{% endfor %}
And also want all the subcategories to be shown, even if there is no match in any of the counts. But, then with a value of 0 for that count.
解决方案
There is no easy, efficient way to do this with the Django ORM. With the implementation of annotation, there is no way in the ORM to annotate on different filtered sets within the same query. Even if you annotate, filter, and annotate again, all annotations will be on the filtered set at the time of the last annotation. I am not even sure if what you want is feasible in raw SQL code, but I'm not expert on that. 

You can fetch all related tags and articles, and do the actual annotation in Django. You must be careful, however: even when using prefetch_related, if you filter on the related set or alter it in any way that actually clones the queryset, you will still execute a database query for each queryset. 

The following is a way you can do this. It will also handle any tags that have no articles in that specific subcategory.
subcategories = category.sub_category_set.prefetch_related('tags', 'articles')
for subcategory in subcategories:
    subcategory.num_articles = dict()
    for tag in tags:
        num = len([a for a in subcategory.articles.all() if a.tag_id == tag.id])
        subcategory.num_articles[tag.tag_name] = num
And in your template:
{% for subcategory in subcategories %}
    {{ subcategory.name }} <br />
    {% for k,v in subcategory.num_articles.iteritems %}
        {{ k }} -- {{ v }}
    {% endfor %}
{% endfor %}
This will produce 3 database queries regardless of the amount of tags (4 including the query for category) and will do the rest of the processing in python. Fetching a large dataset from the database can be expensive, though. If you need to work with very large datasets and see performance issues, you might want to take a look how doing some well-optimized queries for each tag compares to doing only a few large queries with data-processing in python. 

                        
这篇关于获取数量在模板中的几个过滤的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！