On Mon, Feb 20, 2012 at 5:17 PM, Martin Tiršel <dja...@blackpage.eu> wrote:
> Hi,
>
> I have:
>
> class BannerStats(models.Model):
> ACTION_TYPE_VIEW = 0
> ACTION_TYPE_CLICK = 1
>
> ACTION_TYPES = (
> (ACTION_TYPE_VIEW, 'view'),
> (ACTION_TYPE_CLICK, 'click'),
> )
>
> banner = models.ForeignKey(
> Banner
> )
> date = models.DateTimeField(
> auto_now_add=True
> )
> action = models.PositiveSmallIntegerField(
> choices=ACTION_TYPES,
> default=ACTION_TYPE_VIEW
> )
>
> I need to get list of dates with sum of views and sum of clicks grouped by
> date. I use:
>
> BannerStats.objects.filter(
> banner=banner
> ).extra(
> {'date': 'date(date)'}
> ).values(
> 'date',
> ).annotate(
> views=models.Count('id')
> )
>
> But this works only for vies, is there a way how can I get views and click
> in one query? Or have I to do two queries and join it in Python? Or should I
> remove action column and split it to views and clicks columns?
>
> Thanks,
> Martin
>
(btw, are you sure that works for views? looks like it is counting
clicks as well)
This type of query is tricky to do in django. In SQL, you would simply do:
SELECT action, count(*) as num_events
FROM bannerstats where banner_id = N group by action;
In django, with this kind of table structure, you have to do two
queries (or use SQL):
BS.objects.filter(banner=b, action=0).aggregate(num_views=Count('id'))
BS.objects.filter(banner=b, action=1).aggregate(num_clicks=Count('id'))
If you altered the table structure to have two columns, one for clicks
and one for views, and use the value '1' for each (so an event
registering a click would log (1,0), for a view (0,1), then you could
do it in one query:
BS.objects.filter(banner=b).aggregate(num_views=Sum('views'),
num_clicks=Sum('clicks'))
>From a DB perspective, I prefer the original structure though.
Cheers
Tom
--
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.
