On Mon, 20 Feb 2012 18:33:40 +0100, Tom Evans <[email protected]>
wrote:
On Mon, Feb 20, 2012 at 5:17 PM, Martin Tiršel <[email protected]>
wrote:
(btw, are you sure that works for views? looks like it is counting
clicks as well)
Yes, that was counting all records.
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;
Ok, I will use raw query. Btw. it should be (pgsql):
SELECT (date(date)) AS "date", action, count(*) as num_events
FROM bannerstats
WHERE banner_id = N
GROUP BY (date(date)), action;
This gives the correct results. I only need to fill out programatically
missing counts for days where the action didn't happened (no views or
clicks).
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.
There will be more actions as time passes so it is easier to add new value
to action than new columns to the table.
Cheers
Tom
Regards,
Martin
--
You received this message because you are subscribed to the Google Groups "Django
users" 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-users?hl=en.
