It may indeed be that the MAX clause is using "mat_foo" instead of the
assigned correlation U0. What happens if you paste the two SQLs into a
query window in PgAdmin 3 and execute them. If the 1.2 query fails
with the same error message, I'd report a bug.
If it doesn't fail, it's still a Django issue, but I have no idea what
it could be. I'm very new to Django; not new to databases.
Brian
On Feb 8, 9:32 am, Mathieu Pillard <diox...@gmail.com> wrote:
> Hi,
>
> I have been testing the 1.2 beta1 and think I found a bug, but since
> the query I'm using is a bit complicated I wanted to run it through
> the list first.
>
> The model I'm using:
>
> 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)
>
> 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. The following code works in 1.1:
>
> 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))
>
> However, 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:
> 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:
> 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")
>
> The only difference is in the MAX() clause. Anyone can enlighten me
> about what's happening ? Is that a (known?) django 1.2 bug or am I
> pushing the ORM a little too far? It looks like sqlite doesn't
> complain with the same code, but I didn't test if the results were
> right.
>
> Thanks
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
