Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
334 views
in Technique[技术] by (71.8m points)

python - Django: Using Annotate, Count and Distinct on a Queryset

Here's my database query:

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

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

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

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?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...