#12822: DatabaseError: aggregates not allowed in WHERE clause
-----------------------------+----------------------------------------------
Reporter: mat | Owner:
Status: new | Milestone: 1.2
Component: ORM aggregation | Version: 1.2-beta
Keywords: | Stage: Unreviewed
Has_patch: 0 |
-----------------------------+----------------------------------------------
From [http://groups.google.com/group/django-
users/browse_thread/thread/2fbd258cd90bc29c# django-users] ; I have been
searching for an existing bug and couldn't find any in the ORM/ORM
Aggregation component.
The following code works with django 1.1, and returns the expected
results, but fails with 1.2 beta 1. It's a basic messaging system, in
which you can group messages by
conversations : when saving a new Foo object, you can give it an existing
Foo id to form a conversation. I want to display the "inbox"
for a user, which should be a list with the last message from each
conversation.
{{{
#!python
from django.db import models
from django.contrib.auth.models import User
class Foo(models.Model):
subject = models.CharField(max_length=120)
sender = models.ForeignKey(User, related_name='sent_foo')
recipient = models.ForeignKey(User, related_name='received_foo')
conversation = models.ForeignKey('self', null=True, blank=True)
from django.db.models import Max
def conversations(self, user):
tmp =
Foo.objects.values('conversation').annotate(Max('id')).values_list('id__max',
flat=True).order_by( 'conversation')
return Foo.objects.filter(id__in=tmp.filter(recipient=user))
}}}
In 1.2 beta 1, with postgresql_psycopg2, it fails with:
{{{
DatabaseError: aggregates not allowed in WHERE clause
LINE 1: ...d" FROM "mat_foo" WHERE "mat_foo"."id" IN (SELECT MAX("mat_f...
}}}
The generated SQL queries are a bit different.
Here is django 1.2:
{{{
#!sql
SELECT "mat_foo"."id", "mat_foo"."subject", "mat_foo"."sender_id",
"mat_foo"."recipient_id", "mat_foo"."conversation_id" FROM "mat_foo"
WHERE "mat_foo"."id" IN (SELECT MAX("mat_foo"."id") AS "id__max" FROM
"mat_foo" U0 WHERE U0."recipient_id" = 1 GROUP BY U0."conversation_id")
}}}
And here is django 1.1 (which works):
{{{
#!sql
SELECT "mat_foo"."id", "mat_foo"."subject", "mat_foo"."sender_id",
"mat_foo"."recipient_id", "mat_foo"."conversation_id" FROM "mat_foo"
WHERE "mat_foo"."id" IN (SELECT MAX(U0."id") AS "id__max" FROM "mat_foo"
U0 WHERE U0."recipient_id" = 1 GROUP BY U0."conversation_id")
}}}
Note the difference in the MAX() clause. For what it's worth, it does work
with sqlite. Didn't test other databases. I suspect there is a better way
to write my query, but in any case, I don't think it should fail like
that, especially since it was working with 1.1.
--
Ticket URL: <http://code.djangoproject.com/ticket/12822>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
