Rolling Counts

Rails
1

Hi

I just wondered if anyone has come up with an elegant solution to
rolling counts for a RoR app?

Our requirement is to show most viewed items for the past week …
with this rolling each day.

Currently we are showing most viewed ever so that simply has one row
in a table per item with an incremented count column.

It would be nice if somehow counter_cache extended to rolling data.

Any thoughts or suggestions?

Cheers
Shane

2

I just wondered if anyone has come up with an elegant solution to
rolling counts for a RoR app?

Our requirement is to show most viewed items for the past week …
with this rolling each day.

We have a polymorphic View model that let’s us keep track of any
model/object’s views by day. We end up with a lot of rows, but our
folks
wanted it to go back a long way. Once you have this you can run some
SQL to do the sums and group_by’s to get what you want.

mysql> desc views;
±--------------±------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±--------------±------------±-----±----±--------±---------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| views | int(11) | YES | | 0 | |
| viewed_on | date | YES | MUL | NULL | |
| viewable_id | int(11) | YES | MUL | NULL | |
| viewable_type | varchar(45) | YES | | NULL | |
±--------------±------------±-----±----±--------±---------------+

If you really only need it for the last week you could do something
similar but instead of “viewed_on” being a date you could change that to
“weekday_num” and make it an int.

Then at 12:01 of every day simply zero out any rows for that weekday.

That would keep the table fairly compact and still give you the rolling
count…

Depending on what you’re doing you may want to cache the “top 10 list”
for
awhile.

-philip