Opened 16 years ago
Closed 16 years ago
#10182 closed (fixed)
"invalid reference to FROM-clause" for nested annotate query
Reported by: | omat | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Keywords: | nested query, annotate | |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
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.
Change History (2)
comment:1 by , 16 years ago
comment:2 by , 16 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
(In [9888]) Fixed #10182 -- Corrected realiasing and the process of evaluating values() for queries with aggregate clauses. This means that aggregate queries can now be used as subqueries (such as in an in clause). Thanks to omat for the report.
This involves a slight change to the interaction of annotate() and values() clauses that specify a list of columns. See the docs for details.
BTW: If I force the first query to be evaluated before it is used as a nested query, using the python step syntax or using list() for example, like:
Then the second runs fine.