Annotation and generic relations do not work well together

[Matthias Kestenholz]

Take the following models.py:

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):
                return self.name

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]
Out[8]: 
{'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)
    523 
    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
    542 

/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)))
   1555 
   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!

[stryderjzw]

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.

[fas]

I opened a new ticket (before seeing this one) that generalizes this ticket. ​http://code.djangoproject.com/ticket/10870.

[Matthias Kestenholz]

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

[Aymeric Augustin]

Change UI/UX from NULL to False.

[Aymeric Augustin]

Change Easy pickings from NULL to False.

[fhahn]

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 (​https://github.com/fhahn/django/commit/71cfbb97dcfacbd282d0467f439e7a1e3fcd1917)

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.

[Anssi Kääriäinen <akaariai@…>]

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.