Hi,
i'm trying to aggregate rows using year and month and it's quite
challanging in Django. Finally i managed to achive that with a code
like that:
select_fields = {'year_month':"EXTRACT(YEAR_MONTH FROM
`blog_post`.`publication_date`)"}
archive =
Post.active_objects.extra(select=select_fields).values('year_month').annotate(num_posts=Count('id')).order_by('-
year_month')
that does what it's supposed to do, but it generets SQL:
SELECT (EXTRACT(YEAR_MONTH FROM `blog_post`.`publication_date`)) AS
`year_month`, COUNT(`blog_post`.`id`) AS `num_posts` FROM `blog_post`
WHERE `blog_post`.`active` = True GROUP BY (EXTRACT(YEAR_MONTH FROM
`blog_post`.`publication_date`)) ORDER BY `year_month` DESC
instead of:
SELECT (EXTRACT(YEAR_MONTH FROM `blog_post`.`publication_date`)) AS
`year_month`, COUNT(`blog_post`.`id`) AS `num_posts` FROM `blog_post`
WHERE `blog_post`.`active` = True GROUP BY 'year_month' ORDER BY
`year_month` DESC
So 'EXTRACT' phrase is used twice, instead of just column name
'year_month' in GROUP BY statement, so, as far as i know all this
'EXTRACT ... ' expression is unnecessarily executed twice. (Am i
right?)
I've tried to modify query.group_by, but the 'EXTRACT' phrase
automagically appears at the end.
archive =
Post.active_objects.extra(select={'year_month':"EXTRACT(YEAR_MONTH
FROM
`blog_post`.`publication_date`)"}).values('year_month').annotate(num_posts=Count('id')).order_by('-
year_month')
archive.query.group_by = ['`year_month`']
print archive.query
SELECT (EXTRACT(YEAR_MONTH FROM `blog_post`.`publication_date`)) AS
`year_month`, COUNT(`blog_post`.`id`) AS `num_posts` FROM `blog_post`
WHERE `blog_post`.`active` = True GROUP BY (`year_month`),
(EXTRACT(YEAR_MONTH FROM `blog_post`.`publication_date`)) ORDER BY
`year_month` DESC
Can you please help me how to achive the requested output or tell me
why everything from extra(select={}) appears in GROUP BY?
Best regards
Mariusz
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@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.
