Annotate returns empty set without value arguments

[Adam Nelson]

With a class referring to itself via a foreign key, annotate does not correctly return results from a filter unless the values to group by are in the queryset. This is either counter to the documentation or an interface bug:

​http://docs.djangoproject.com/en/dev/topics/db/aggregation/#filtering-on-annotations

class PageEmailLink(models.Model):
  parent = models.ForeignKey('self', null=True, blank=True, related_name='child')
  # Other fields like url, level_deep, etc...


from django.db.models import Count
PageEmailLink.objects.annotate(num_emails=Count('parent')).filter(num_emails__gt=1)

[]

PageEmailLink.objects.values('parent').annotate(num_emails=Count('parent')).filter(num_emails__gt=1)

[{'num_emails': 2, 'parent': 67}, {'num_emails': 2, 'parent': 70}, {'num_emails': 2, 'parent': 75}, {'num_emails': 2, 'parent': 83}, {'num_emails': 2, 'parent': 144446}, {'num_emails': 2, 'parent': 144448}, {'num_emails': 2, 'parent': 144452}, {'num_emails': 2, 'parent': 144453}, {'num_emails': 2, 'parent': 144454}, {'num_emails': 2, 'parent': 144456}, {'num_emails': 2, 'parent': 144457}, {'num_emails': 2, 'parent': 144459}, {'num_emails': 2, 'parent': 144493}, {'num_emails': 2, 'parent': 144495}, {'num_emails': 2, 'parent': 144497}, {'num_emails': 2, 'parent': 144499}, {'num_emails': 2, 'parent': 144500}, {'num_emails': 2, 'parent': 144502}, {'num_emails': 2, 'parent': 144507}, {'num_emails': 2, 'parent': 144508}, '...(remaining elements truncated)...']

PageEmailLink.objects.filter(parent=67)

[<PageEmailLink: http://www.dnelist.com/u/56c372cfa20fccb51b2325ce55496d265 5 levels deep>, <PageEmailLink: http://www.dnelist.com/u/56c372cfa20fccb51b2325ce55496d265 5 levels deep>]

[Adam Nelson]

This is on psycopg2 in case that is relevant

[Russell Keith-Magee]

As far as I can make out, this is behaving as expected.

The first query (without the values() clause) is annotating the PageEmailLink objects, and returning the number of PageEmailLink objects that have more than one related PageEmailLink object. The group in this case is the PK of the PageEmailLink object

The second query is grouping by the value of the parent FK itself. This provides the count that you seem to be expecting - that is, the number of PageEmailLink objects that have a shared parent.

As an alternate approach, you should be able to get the same result by counting children, rather than counting parents: i.e., PageEmaiLink.objects.annotate(num_emails=Count('child'))...

The aggregation docs already talk about how the grouping rules are affected by values, and the description of annotate describes what is being annotated. It strikes me that this is a very complex example (since it is self referential), and as a result you've been caught out. I'm not sure how we could improve the documentation to clarify this particular class of edge case; I'm certainly open to suggestions.

I'm closing invalid for now; if you can make a specific suggestion (preferably including draft text) of how to improve this area of the documentation, feel free to reopen.