Django：在Queryset上使用注释，计数和不同 [英] Django: Using Annotate, Count and Distinct on a Queryset

问题描述

这是我的数据库查询：

results = Attachments.objects.filter(currency='current').annotate(num_attachments=Count('article_id')).order_by("num_attachments").distinct('article_id')

查询分解如下（据我所知）：

The query broken down as follows (as I understand it):

• 第一个过滤器是当前的当前附件。 >
  
• 然后用一定的article_id来计算这些附件的数量。


• 然后用附件数量注明每个附件的数量，有article_id是共同的。


• 然后根据附件的数量进行排名。


• 然后，使用distinct来排列列表，以便有一个每个article_id值的附件对象。

• First filter is current Attachments that are "current".
• Then to count the number of those Attachments with a certain 'article_id'.
• Then to annotate each Attachment with the number of Attachment with the number of those that have article_id in common.
• Then to rank based on the number of attachments.
• Then, paring down the list with distinct, so that there's one Attachment object for each article_id value.

我在PostgreSQL上运行，所以根据Django docs ，I根据一个字段来运行distinct（）可以很好运行。

I am running this on PostgreSQL, so according to the Django docs, I'm fine to run distinct() based on a field.

执行查询时没有错误，但是当我尝试迭代甚至打印结果时，错误被Django调试抛出：

There is no error when I execute the query, but when I try to iterate or even print the results the following error is thrown by Django debug:

NotImplementedError at /function/
annotate() + distinct(fields) not implemented.

来自交互式提示符的更详细的追溯是：

The more detailed traceback from the interactive prompt is:

  File "<console>", line 1, in <module>
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/query.py", line 118, in _result_iter
    self._fill_cache()
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/query.py", line 875, in _fill_cache
    self._result_cache.append(self._iter.next())
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/query.py", line 291, in iterator
    for row in compiler.results_iter():
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 763, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 808, in execute_sql
    sql, params = self.as_sql()
  File "/Users/Pat/.virtualenvs/envsp/lib/python2.7/site-packages/django/db/models/sql/compiler.py", line 107, in as_sql
    "annotate() + distinct(fields) not implemented.")
NotImplementedError: annotate() + distinct(fields) not implemented.

任何人都知道这里发生了什么？

Anyone know what's going on here?

推荐答案

解决方法是使用值（'distinct_fieldname'），因为这将使最终的SQL语句执行 GROUP BY 在该字段（您可以添加多个字段名称），这本质上是一样的。

The work-around is to use values('distinct_fieldname') because this will make the final SQL statement perform GROUP BY on that field (you can add more than one fieldname), which essentially is the same.

例如，如果你想要知道一个给定的'filename'有多少篇文章，你可以这样做：

For instance, if you want to know how many articles exist for a given 'filename' you would do this:

results = Attachments.objects.filter(currency='current').values('filename').annotate(num_attachments=Count('article_id')).order_by("num_attachments")

这篇关于Django：在Queryset上使用注释，计数和不同的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！