Rails Join Association with Count Condition using Postgres

Recently, I have the pleasure of working on a feature that needed to filter search results based on the record count of an associated model. Here's how it was tackled.

The Problem

We needed to filter the products that had less than three orders. Here are the models for clarity:

# product.rb
class Product < ActiveRecord::Base
  has_many :orders
end

# order.rb
class Order < ActiveRecord::Base
  belongs_to :product
end

1. Create a scope on Product

The scope uses left outer join so it will include products that don't have any orders, and also uses

# product.rb
class Product < ActiveRecord::Base
  has_many :orders
  
  scope :with_max_orders, -> (max_orders = 3) {
      joins('LEFT OUTER JOIN orders ON orders.product_id = products.id')
        .select('products.*')
        .group('products.id')
        .having("COALESCE(COUNT(orders.id), 0) < #{max_orders}")
  }
end

Then to use the scope:

@products = Product.with_max_orders(3)

One tip when using group and having is to use the length method instead of count which will query the DB a second time.

@products.length

There are likely other ways to accomplish this, by using Arel for example, but this worked for our needs.