Building a trending algorithm based on post count and frequency

Say I have a Board model. Boards have many Posts. I simply want to find the boards that have the highest post count within the span of (x) days. Below is my extremely naive approach to this. With the code provided I get the error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR:  missing FROM-clause entry for table "posts")
LINE 1: SELECT  "boards".* FROM "boards" WHERE (board.posts.created_...
                                                ^
: SELECT  "boards".* FROM "boards" WHERE (board.posts.created_at >= '2019-06-05 12:14:30.661233') LIMIT $1

---

Please let me know if there’s a better way to do this in addition the error I’m receiving.

class Board < ApplicationRecord
  has_many :posts

  scope :trending, -> { includes(:posts).where('board.posts.created_at >= ?', Time.now-7.days).order(posts_count: :desc) }
end

class Post < ApplicationRecord
  belongs_to :board, counter_cache: true
end

Update: So I managed to come up with a working scope but not 100% sure if it’s the most optimal. Your thoughts would be appreciated:

scope :trending, -> { includes(:posts).where(posts: { created_at: Time.now - 7.days }).order(posts_count: :desc) }

Answer

Update:

Board.joins(:posts)
     .select("boards.*, count(posts.id) as latest_posts_count")
     .where('posts.created_at >= ?', 7.days.ago)
     .order('latest_posts_count desc')
     .group('boards.id')

Try this, you will need to join it and group them by board_id

Board.joins(:posts)
     .select("boards.*, count(posts.id) as posts_count")
     .where('posts.created_at >= ?', 7.days.ago)
     .order('posts_count desc')
     .group('boards.id')

Explanation:

• We joined (inner join) the tables so by default you get only boards which has at least one post associated with it
• we ordered them based on posts count
• we grouped them based on boards.id