Code

Opened 4 years ago

Closed 4 years ago

#13589 closed (invalid)

Annotate returns empty set without value arguments

Reported by: adamnelson Owned by: nobody
Component: Uncategorized Version: 1.2
Severity: Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: UI/UX:

Description

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>]

Attachments (0)

Change History (3)

comment:1 Changed 4 years ago by adamnelson

  • Needs documentation unset
  • Needs tests unset
  • Patch needs improvement unset
  • Version changed from 1.1 to 1.2

comment:2 Changed 4 years ago by adamnelson

This is on psycopg2 in case that is relevant

comment:3 Changed 4 years ago by russellm

  • Resolution set to invalid
  • Status changed from new to closed

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.

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.