On Mon, Jun 8, 2009 at 3:42 PM, Don Spaulding <donspauldin...@gmail.com>wrote:
>
> Hi there,
>
> While playing around with the aggregation support in trunk, I came up
> with the need for the following SQL query:
>
> SELECT
> DATE_TRUNC('day', datestamp) AS rec_day,
> COUNT(id) AS count
> FROM stats_record
> GROUP BY rec_day
>
> How would I construct this same query via the ORM? My intuition tells
> me "just combine the .dates() and .annotate() queryset methods", as
> in:
>
> qs.dates('datestamp', 'day').annotate(num_on_day=Count('id'))
>
> But the interactive shell gives me no joy with that query, simply
> returning the dates. I'm not sure what I would expect it to return
> (I'm certainly not expecting a setattr on a datetime instance), maybe
> the equivalent of a .values() call with just the field specified in
> the .dates() call and the annotation alias (e.g. {'datestamp':
> datetime(2009,3,23,0,0), 'num_on_day': 12}). That would make sense to
> me, but I'm not sure if that requires reading the user's mind behind
> the scenes.
>
> Am I just being dense and missing an easier way to do this with the
> Aggregation support already in trunk?
> >
>
Unfortunately this isn't really possible as it is. There are a number of
places where aggregates (and F objects) don't really interact with dates
well. The way I would handle it is to create a custom aggregate for doing
the date lookup, and then use values, so the final syntax would be.
Model.objects.annotate(rec_day=Date('datestamp',
'day')).values('rec_day').annotate(Count('id')).
Which should work (I think ;) ). Search the mailing list for some info on
custom aggregate objects (they're not super difficult).
Alex
--
"I disapprove of what you say, but I will defend to the death your right to
say it." --Voltaire
"The people's good is the highest law."--Cicero
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
