I need to add some very simple statistics to my project. One of them is article popularity. Counting overall popularity can’t be easier and all I need to is to sort my articles on views_count column.
But what about last month popularity? I.e. retrieve articles, that was most popular up to thirty days from since now?
I don’t even know, what would be the best approach for column or table design, for only counting visits. Retrieving this data is the next step.
The only thing I come out is to introduce separate table, that would store article_id and timestamp of each hit (view). Then, filter this table with proper timestamp (now() - (30 * 24 * 60 * 60) for month), sum count of each article_id’s and the sort it by that sum.
But I don’t know, if there aren’t any flaws at design level (maybe the entire approach is wrong). And if not – then I don’t have idea, how to solve it on ActiveRecord or SQL.
How are you planning to count the visits? Any visit? or just unique visits per time period? You should first define what a visit is and how they should be counted from business point of view and then we should try some brainstorming to come up with something that works.
I think, we can assume solution as simple as possible. I.e. all visits are counted, without including time component. If user visits an article and then visits it again after a few seconds, then this is counted as two. I may implement later a cookie-based solution, to not to recount articles already visited during the same day, but this is not the point in here.
Key assumption is: whenever anyone uses route [font="Courier New"]article/show.php?id=xyz[/font], to display particular article, this should be counted as one.
I guess you could create a separate db table for the views such as:
article_id FK
view_date (truncated date?)
count
Basically first check if article_id and view_date exist. If they do increment count +1 if not create the record and set count=1. This does not allow any aggregation by user. To take this further you could add user_id column and add the $user->id if that is of interest. I leave the implementation of the primary key to yourself. This should be good to start with. Let me know what you think!
