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) }
Advertisement
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