ActiveRecord scope based on number of foreign keys

Question

Given the canonical example:

class Post < ActiveRecord::Base
  has_many :comments
end

class Comments < ActiveRecord::Base
  belongs_to :post
end

I would like to return a scope (i.e. ActiveRecord::Relation) of all the Posts that have X or fewer Comments; using either using Active Record Query Interface, Arel or something else if it solves my problem.

Pseudocode:

scope :quiet, lambda { |n| where(comments: { maximum: n }) }

I am aware that this can be done in Ruby with a simple Enumerable#select. It is preferable to be calculated on the database as in actuality there can only ever be one Model that satisfies the predicate.

Answer 1

what about this scope:

scope :quiet, ->(n) { where("(SELECT COUNT(*) FROM comments WHERE post_id = `posts`.id) < ?", n) }

It will be done all database side. It is not using join, so you can actually update those post records if needed

Answer 2

You can do something like this..

Post.joins(:comments).group("posts.id HAVING count(comments.id) > 0")

Its not quite fully arel, but it will certainly work