On Tue, Feb 10, 2009 at 6:47 PM, Alessandro Ronchi
<alessandro.ron...@soasi.com> wrote:
> I have to get the max value of a model grouped by a field.
>
> The RAW SQL query is this:
>
> SELECT max(energy_tot) FROM fotovoltaico_modules,fotovoltaico_module_scheme
> WHERE fotovoltaico_module_scheme.plant_id = 3 AND
> fotovoltaico_modules.scheme_id = fotovoltaico_module_scheme.id
> GROUP BY fotovoltaico_modules.scheme_id;
>
> I haven't found any way to make the aggregation work with the group by.
>
> This is my model:
> http://dpaste.com/118868/
If you're using Django v1.0.X, you're on your own. Aggregates don't
really exist in v1.0; you will need to use custom SQL.
However, if you're using Django trunk, what you want is:
>>> from django.db.models import Max
>>> qs =
>>> Module_scheme.objects.filter(plant=3).annotate(max_energy_tot=Max('module__energy_tot'))
>>> for mod in qs:
... print mod.id, mod.max_energy_tot
That is - retrieve a list of Module_scheme objects associated with
plant 3, and annotate each one of which is annotated with the maximum
of the energy_tot of the modules related to the Module_scheme. The
maximum is provided as an extra attribute on each of the Module_scheme
instance.
This will return full Module_scheme objects, rather than just the
Maxima like your sample SQL. If you want to restrict the columns
returned, you can do that by using the values() clause on the query.
> I've tried also:
> qs = Modules.objects.filter(scheme__plant=3).order_by("-time")
> qs.query.group_by = ["fotovoltaico_modules.scheme_id"]
You shouldn't ever need to touch qs.query as an end user. That is
internal logic, and is therefore not guaranteed to be backwards
compatible API. There be dragons. :-)
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
-~----------~----~----~----~------~----~------~--~---
