Django: Using Annotate, Count and Distinct on a Queryset

Question

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?

Seems pretty straight forward. `NotImplementedError` is a Django exception. The code is raising it to let you know that you can't combine `annotate` and `distinct` (with fields) together at this time, which means that it doesn't work right now. They may work out whatever problems are involved with it in the future, but for now you're out of luck. You can always just resort to `raw` and do whatever you want. — Chris Pratt, Aug 06 '12 at 15:48
In the code example above you are missing a dot "." between the order_by and distinct field. — Mikael, Aug 06 '12 at 15:50
@Chris any ideas on an alternative query (besides raw SQL) to accomplish the same thing? — Pat, Aug 07 '12 at 00:29
@ Mikael Thanks, fixed. That was a typo in stack only, the corrected version produces the same result. — Pat, Aug 07 '12 at 00:30

score 10 · Answer 1 · answered Feb 17 '14 at 16:10

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

score 4 · Answer 2 · answered Sep 03 '18 at 15:46

I have find another way, how to overcome this - by using a subquery:

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

This get actually evaluated as one database query in Django.

Django: Using Annotate, Count and Distinct on a Queryset

2 Answers2

Linked