Rails Active Record - counter_cache to return records only from a given period of time

Question

I have a User who has_many articles. I use a counter cache to store the number of articles ever published by a user.

class User < ActiveRecord::Base
  has_many :articles
end

class Article < ActiveRecord::Base
  belongs_to :user, :counter_cache => true
end

I'm using the below syntax to find the users with most articles ever published:

User.order( "articles_count desc" ).limit(50).all  

What I would like to do is to check for 50 top users who published most articles in the past month.

I know I can get the number of articles published last month by each user like so but it just doesn't feel very efficient:

User.find_each do |user|
  user.articles.where('created_at >= ?', 1.week.ago.utc).count
end

I tried with some SQL queries without much luck and wondered if maybe there's a way to use the data stored in my counter_cache for that purpose?

Answer 1

This seems to be slightly more neat:

User.
joins(:articles).
where('articles.created_at >= ?', 1.week.ago.utc).
group('articles.user_id').
order("count(*) desc").
limit(50)

Answer 2

How about this:

Article.
  includes(:user).
  where('created_at >= ?', 1.week.ago.utc).
  group(:user).
  order("count(*) desc").
  limit(50).
  count

It gives you a hash in which the key is the user, the value is the number of articles, for the top 50 users by number of articles, and it is sorted in descending order of article count.

Place an index on created_at, and maybe user_id as well.