On Jun 8, 3:54 pm, Alex Gaynor <alex.gay...@gmail.com> wrote:
> 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
(Sort of) using the technique Russell described in the Custom
Aggregate Objects thread [1], what follows is my stab at a DateTrunc
aggregate for postgres that tries to mimic the functionality provided
by the dates() queryset method.
========================================
from django.db.models.sql.aggregates import Aggregate
class DateTrunc(Aggregate):
"""
An aggregate that returns unique date values according to the
arguments
specified for 'kind' and 'field'
"""
sql_function = 'DATE_TRUNC'
sql_template = "%(function)s('%(kind)s', %(field)s)"
def __init__(self, col, kind, **extra):
super(DateTrunc, self).__init__(col, kind=kind, **extra)
self.col = col
self.lookup = col
self.kind = kind
def _default_alias(self):
return '%s__%s' % (self.col, self.kind)
default_alias = property(_default_alias)
def add_to_query(self, query, alias, col, source, is_summary):
query.aggregate_select[alias] = self
========================================
Which really wasn't too hard (thanks Django Devs!). Unfortunately, I
can't seem to find a way to get it to append a GROUP BY clause onto
the query. I stumbled upon a comment on ticket #10302 [2] (a similar
problem) that ultimately led me to this query:
qs.extra(select={'rec_date':"DATE_TRUNC('day', datestamp)"}).values
('rec_date').annotate(num_on_day=Count('id'))
Obviously extra() is not the preferred method, but until custom
aggregates support GROUP BY or DatesQuerySet intuitively supports
further annotation, it'll do. Fortunately, I'm the patient type when
other people are working for me for free ;-)
Thanks,
Don
[1]:
http://groups.google.com/group/django-users/browse_thread/thread/bd5a6b329b009cfa/
[2]: http://code.djangoproject.com/ticket/10302
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
