On Tue, Mar 31, 2009 at 4:15 PM, Eric Bandel <e.ban...@malezieux.com> wrote:
> Hi, i've just upgrade my django install from 1.1 alpha to 1.1 beta.
>
> i have a simple query.
>
> data = mymodel.objects.extra(select={'month':'month(date)',
>
> 'test':'SUM(IF(ok,0,ok)})
> data.query.group_by = [''month]
>
> with the 1.1 alpha version the generated sql was:
>
> select (month(date)) AS `month`,
> (SUM(IF(ok,0,ok)) AS `test`
> from ...
> GROUP BY month
>
> since the 1.1 beta the generated sql is:
>
> select (month(date)) AS `month`,
> (SUM(IF(ok,0,ok)) AS `test`
> from ...
> GROUP BY month,month(date),(SUM(IF(ok,0,ok))
>
> and i get a sql error: invalid use of group function.
>
> Why all the extra fields are added in the group by clause?
They are added because they are required. On databases like Postgres
and Oracle, this requirement is enforced - queries that don't mention
all non-aggregate columns in the GROUP BY clause raise an error. Other
databases (SQLite and MySQL) are somewhat more lax on this front. In
order to support the more rigorous backends, we have made the
assumption that all extra clauses will be non-aggregates. Obviously,
in your case, this is not a valid assumption. This is one of the
unfortunate problems with a clause like "extra" - we need to make
certain assumptions about the unknown SQL that is provided - sometimes
those assumptions won't work.
I would also add that QuerySet.query is internal API. Manually
modifying attributes on this object (such as the group_by attribute)
is not recommended practice. Changes to this API are not considered to
be backwards incompatibilities, and you should expect that this API
can change at any time.
However, you should note that as a result of the change introduced to
the handling of extra(), you no longer need to manually modify
group_by - the month column (or rather, the full month calculation) is
automatically added to the group by. The feature you are trying to
implement is the subject of #10302, so this is on our radar to fix at
some time in the future.
All that remains is the handling of your SUM(IF)) clause. Obviously,
using extra() isn't an option. I would suggest to you that your best
course of action will be to experiment with the Aggregates API to try
and create a custom aggregate that will output the SQL that you
expect. From the list archives, here is an informal starting point:
http://groups.google.com/group/django-users/browse_thread/thread/bd5a6b329b009cfa/#63f77c8e2e1a7f18
Yours,
Russ Magee %-)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-users@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
-~----------~----~----~----~------~----~------~--~---
