Opened 8 years ago

Closed 3 years ago

#10461 closed Bug (fixed)

Annotation and generic relations do not work well together

Reported by: Matthias Kestenholz Owned by: fhahn
Component: Database layer (models, ORM) Version: master
Severity: Normal Keywords: generic relations, annotate
Cc: stryderjzw@…, mbencun@…, tim.babych@…, Robin, seldon, flo@… Triage Stage: Accepted
Has patch: yes Needs documentation: yes
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Take the following

from django.db import models
from django.contrib.contenttypes import generic
from django.contrib.contenttypes.models import ContentType

from django.db.models import Sum

class Note(models.Model):
        text = models.TextField()

        content_type = models.ForeignKey(ContentType)
        object_id = models.IntegerField()
        content_object = generic.GenericForeignKey()

        def __unicode__(self):
                return self.text

class Something(models.Model):
        name = models.CharField(max_length=100)

        notes = generic.GenericRelation(Note)

        def __unicode__(self):

And the following django/python shell session:

In [1]: from genann.models import *

In [2]: s = Something.objects.create(name='Diesunddas')

In [3]: Note.objects.create(content_object=s, text='note')
Out[3]: <Note: note>

In [4]: s.notes.all()
Out[4]: [<Note: note>]

In [5]: Something.objects.all().annotate(Sum('notes'))
Out[5]: [<Something: Diesunddas>]

In [6]: Something.objects.all().annotate(Sum('notes'))[0].notes__sum
Out[6]: 1

So far, everything seems to work. It does not however, since the annotation clause does not take the content type into account:

In [7]: from django.db import connection

In [8]: connection.queries[-1]
{'sql': u'SELECT "genann_something"."id", "genann_something"."name", SUM("genann_note"."id") AS "notes__sum"
FROM "genann_something" LEFT OUTER JOIN "genann_note" ON ("genann_something"."id" = "genann_note"."object_id")
GROUP BY "genann_something"."id", "genann_something"."name" LIMIT 1',
 'time': '0.000'}

And here it falls completely apart:

In [9]: Something.objects.all().annotate(Sum('notes')).filter(notes__sum__gt=0)
FieldError                                Traceback (most recent call last)

/home/mk/Projects/bugsquash/genann/<ipython console> in <module>()

/home/mk/Projects/bugsquash/django/django/db/models/query.pyc in filter(self, *args, **kwargs)
    520         set.
    521         """
--> 522         return self._filter_or_exclude(False, *args, **kwargs)
    524     def exclude(self, *args, **kwargs):

/home/mk/Projects/bugsquash/django/django/db/models/query.pyc in _filter_or_exclude(self, negate, *args, **kwargs)
    538             clone.query.add_q(~Q(*args, **kwargs))
    539         else:
--> 540             clone.query.add_q(Q(*args, **kwargs))
    541         return clone

/home/mk/Projects/bugsquash/django/django/db/models/sql/query.pyc in add_q(self, q_object, used_aliases)
   1489                 else:
   1490                     self.add_filter(child, connector, q_object.negated,
-> 1491                             can_reuse=used_aliases)
   1492                 if connector == OR:
   1493                     # Aliases that were newly added or not used at all need to

/home/mk/Projects/bugsquash/django/django/db/models/sql/query.pyc in add_filter(self, filter_expr,
connector, negate, trim, can_reuse, process_extras)
   1387             field, target, opts, join_list, last, extra_filters = self.setup_joins(
   1388                     parts, opts, alias, True, allow_many, can_reuse=can_reuse,
-> 1389                     negate=negate, process_extras=process_extras)
   1390         except MultiJoin, e:
   1391             self.split_exclude(filter_expr, LOOKUP_SEP.join(parts[:e.level]),

/home/mk/Projects/bugsquash/django/django/db/models/sql/query.pyc in setup_joins(self, names,
opts, alias, dupe_multis, allow_many, allow_explicit_fk, can_reuse, negate, process_extras)
   1552                     names = opts.get_all_field_names() + self.aggregate_select.keys()
   1553                     raise FieldError("Cannot resolve keyword %r into field. "
-> 1554                             "Choices are: %s" % (name, ", ".join(names)))
   1556             if not allow_many and (m2m or not direct):

FieldError: Cannot resolve keyword 'sum' into field. Choices are: content_type, id, object_id, something, text, notes__sum

It does not let me search for objects containing notes claiming that it does not know notes_ _sum, even though it appears in the list at the end of the traceback.

Thanks for all the awesome work!

Change History (14)

comment:1 Changed 8 years ago by Jacob

Triage Stage: UnreviewedAccepted

comment:2 Changed 8 years ago by stryderjzw

Cc: stryderjzw@… added

I can confirm this as of r10173. Aggregate/annotation don't work with generic relations because the query doesn't take Content Types into account.

Not sure what other info is helpful.

comment:3 Changed 8 years ago by fas

I opened a new ticket (before seeing this one) that generalizes this ticket.

comment:4 Changed 8 years ago by fas

Cc: mbencun@… added

comment:5 Changed 8 years ago by Matthias Kestenholz

This has just been documented as not working in [10781]

comment:6 Changed 7 years ago by tymofiy

Cc: tim.babych@… added

comment:7 Changed 6 years ago by Robin

Cc: Robin added

comment:8 Changed 6 years ago by Chris Beaven

Severity: Normal
Type: Bug

comment:9 Changed 5 years ago by Aymeric Augustin

UI/UX: unset

Change UI/UX from NULL to False.

comment:10 Changed 5 years ago by Aymeric Augustin

Easy pickings: unset

Change Easy pickings from NULL to False.

comment:11 Changed 5 years ago by seldon

Cc: seldon added

comment:12 Changed 4 years ago by Anssi Kääriäinen

Component: ORM aggregationDatabase layer (models, ORM)

comment:13 Changed 4 years ago by fhahn

Cc: flo@… added
Has patch: set
Keywords: annotate added
Needs documentation: set
Owner: set to fhahn
Status: newassigned

I'm working on a GSOC proposal for "Improve annotation and aggregation" and this issue is mentioned on the wiki page. I looked into it and it seems fixed. I've created a branch and added a test (

This patch needs a documentation update as well (that's the reason I didn't open a PR yet), but I want to finish my proposal first.

comment:14 Changed 3 years ago by Anssi Kääriäinen <akaariai@…>

Resolution: fixed
Status: assignedclosed

In 76da053641e52db540801e18b362497c01e9bb1d:

Fixed #10461 -- bug in generic relation + annotate() case

This issue was fixed when the contenttype restriction was moved from
where clause to the join clause. So, this is tests only addition.

Note: See TracTickets for help on using tickets.
Back to Top