id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
10182	"""invalid reference to FROM-clause"" for nested annotate query"	omat	nobody	"As discussed here:
http://groups.google.com/group/django-users/browse_thread/thread/6010c4375610f5cc/

I have 2 models:

{{{
class Tag(models.Model):
    name = models.CharField(max_length=50, unique=True, db_index=True)
    slug = models.SlugField(unique=True)
    forward = models.ForeignKey('self', blank=True, null=True)
    rank = models.IntegerField(default=0)
    relevance = models.IntegerField(default=0)
    added = models.DateTimeField(auto_now_add=True)

class TaggedItem(models.Model):
    tag = models.ForeignKey(Tag, related_name='items')
    added = models.DateTimeField(auto_now_add=True)
    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField(_('object id'), db_index=True)
    object = generic.GenericForeignKey('content_type', 'object_id') 
}}}

Get a list of tag ids and use it as a nested query:

{{{
# obtain a list of tag ids
>>> tag_ids = TaggedItem.objects.all().order_by('-added__max').values_list('id', flat=True).annotate(Max('added'))[:10]
>>> Tag.objects.filter(id__in=tag_ids)
}}}

and the result is

{{{
ProgrammingError: invalid reference to FROM-clause entry for table ""tagging_taggeditem""
LINE 1: ... WHERE ""tagging_tag"".""id"" IN (SELECT U0.""id"", MAX(""tagging_t...
                                                             ^
HINT:  Perhaps you meant to reference the table alias ""u0"".
}}}

The full traceback is:

{{{
Traceback (most recent call last):
  File ""<console>"", line 1, in <module>
  File ""/Library/Python/2.5/site-packages/django/db/models/query.py"", line 148, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File ""/Library/Python/2.5/site-packages/django/db/models/query.py"", line 163, in __len__
    self._result_cache.extend(list(self._iter))
  File ""/Library/Python/2.5/site-packages/django/db/models/query.py"", line 281, in iterator
    for row in self.query.results_iter():
  File ""/Library/Python/2.5/site-packages/django/db/models/sql/query.py"", line 241, in results_iter
    for rows in self.execute_sql(MULTI):
  File ""/Library/Python/2.5/site-packages/django/db/models/sql/query.py"", line 1974, in execute_sql
    cursor.execute(sql, params)
  File ""/Library/Python/2.5/site-packages/django/db/backends/util.py"", line 19, in execute
    return self.cursor.execute(sql, params)
ProgrammingError: invalid reference to FROM-clause entry for table ""tagging_taggeditem""
LINE 1: ... WHERE ""tagging_tag"".""id"" IN (SELECT U0.""id"", MAX(""tagging_t...
                                                             ^
HINT:  Perhaps you meant to reference the table alias ""u0"".
}}}

Environment: Django r9803 / PostgreSQL 8.2 / Mac OS X.
"		closed	Database layer (models, ORM)	dev		fixed	nested query, annotate		Unreviewed	0	0	0	0	0	0