On Jun 27, 4:54 pm, Russell Keith-Magee <[email protected]>
wrote:
> > queryset.aggregate(
> > expensive_house=Count(house__price,
> > only=(Q(house__price__gt=41000), Q(house__price__lt=43000))),
> > ...
> > )
>
> Ok, so that's you're syntax proposal. Now show me the SQL that this
> translates into. In particular, keep in mind that you're doing joins
> in your Q clauses -- how does that get rolled out into SQL?
This should translate to the following SQL:
SELECT sum(case when house.price > 41000 and house.price < 43000 then
1 else 0 end) as expensive_house,
sum(case when house.price > 43000 then 1 else 0 end) as
really_expensive_house, ...
FROM house
JOIN something on something.id = house.something_id
-- The given example queryset is clearly missing that something :)
I think it might be good to restrict the only clauses to the fields of
the same model the aggregated field is in. This way there is already a
usable join generated by the aggregate. The only clause affects the
"case when" structure only.
The only clauses should never restrict the queryset. It gets really
complicated to do that restriction when you have multiple aggregates
using only. You can use filter to restrict the queryset instead. And
you probably don't want the filter there in any case, In the above
example, you would not get any results for the rows aggregating to 0.
If the only clauses never restrict the queryset, then translating
conditional aggregates to SQL isn't really _that_ complicated. When
you normally generate "avg(table.column) as column_avg", now you just
generate "avg(case when table.some_column matches only condition then
table.column else null end)" as column_avg.
- Anssi
--
You received this message because you are subscribed to the Google Groups
"Django developers" 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-developers?hl=en.
