How to order by counter_cache using only data from the past 30 days

Question

I am trying to rank a list of programs by number of times they have been viewed.

Right now, I have a Program model and a Views model that belongs_to program.

In Views.rb I create a counter_cache:

belongs_to :program, counter_cache: :views_count

I can use .views_count to sort Programs. However views_count updates for all time, so a program viewed 200 times 2 yrs ago and not at all this year will rank higher than one viewed 30 times in the past month.

I want to be able to specify that in calculating views_count I only want to consider views that have occurred e.g. in the past 30 days.

I tried creating a Program model method recent_views and using sort_by in the controller, but it takes too long and often times out. Right now, I am using views_count for programs updated recently, but that doesn't solve this problem.

Is there an easy way to do this in the model?

Is there a way to create a separate counter_cache which only includes the past 30 days?

Answer 1

Use a custom counter cache, as per answer here: Counter Cache for a column with conditions?

In the case of only using the last 30 days of views, you would need to re-calculate the count column for every row at least once a day in order for the counts to be up-to-date.