# Rolling Counts

**URL:** <https://www.ruby-forum.com/t/rolling-counts/120356>\
**Category:** Rails\
**Created:** [November 16, 2007, 5:03am UTC](https://www.ruby-forum.com/t/rolling-counts/120356 "2007-11-16T05:03:53Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![shane](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dbc845/32.png) [@shane](https://www.ruby-forum.com/u/shane)\
**Post date:** [November 16, 2007, 5:03am UTC](https://www.ruby-forum.com/t/rolling-counts/120356/1 "2007-11-16T05:03:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![shane](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/dbc845/32.png) [@shane](https://www.ruby-forum.com/u/shane)\
**Post date:** [November 16, 2007, 6:41pm UTC](https://www.ruby-forum.com/t/rolling-counts/120356/2 "2007-11-16T18:41:48Z")

</div>

> 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