Rails - Order by counter cache (likes_count), but only with likes created today

Question

Currently, I am ordering records with counter cache that keeps track of the amount of likes they have, such as:

Post.order("COALESCE(likes_count, 0) DESC").limit(10)

Is there a way to incorporate a time limit on the likes of the cache that are used to sort the items with? Such as, include only likes created in the last 24 hours.

Likes are handled in this manner:

like.rb

belongs_to :liker, class_name: "User"
belongs_to :liked, class_name: "Post", :counter_cache => :likes_count
validates :liker_id, presence: true
validates :liked_id, presence: true

user.rb

has_many :likes, foreign_key: "liker_id", dependent: :destroy
has_many :liked_posts, through: :likes, source: :liked

post.rb

has_many :likes, foreign_key: "liked_id", dependent: :destroy
has_many :liker_users, through: :likes, source: :liker

Thanks a ton for any answers. Hugely appreciated.

Answer 1

As it seems there's no functionality in counter cache itself to accomplish this, I ended up doing a little detour, but which in itself is actually is quite simple. It simply narrows down the objects which to rank with counter cache to those that got new likes in 24 hours.

application_helper.rb

 def trending_posts
    likes = Like.where("created_at >= ?", Time.now - 24.hours).select(:liked_id)
    trending = Post.where(id: likes)
    trending.order("COALESCE(likes_count, 0) DESC").limit(3)
 end

view.html.eb

 <%= render trending_posts %>

If anyone has any improvement ideas on this, it's greatly appreciated, I'm sure, by a lot of people. I'm not going to mark this an accepted answer in case someone comes up with the ultimate way to do this.

Answer 2

I have done something similar recently. What I did was to add a column to the post model:

t.string :state, :default => nil

Then I created a rake task called “Trending” that basically orders posts by likes within a specific time period and sets their :state to “trending”:

trending_posts = Post.trending.first(100)
trending_posts.each do |post|
   post.update_attributes(:state => “trending”)
end

Here “trending” is a method that I’ve defined in my post.rb (for Postgres though)

def self.trending
  joins("LEFT OUTER JOIN Likes ON likes.post_id = post.id 
                   AND likes.created_at >= (now() - interval '24 hour')")
                  .group("post.id").order("COUNT(likes.id) DESC”)
end

When you run the rake task you must first flush out old trending products meaning setting their state to nil. Otherwise it will mess up the rake run. You can then run this rake task automatically each day and then in your controller just reference the posts that have a trending state.

@trending_posts = Post.where(:state => “trending”)

This way you can also make a Popular state (posts with most likes the last month) or any other state. You just give it a different name and create a new rake task.