AR Relations & Scopes - Get all Items that don't have any relations

Ruby on Rails
1

Hey there, I’m having trouble getting a scope setup properly. Below is an example of how the relationships are setup.

  class Product

  has_many :board_products
  has_many :boards, through: :board_products
  has_many :sales, through: :boards

  class Board

  has_many :sales

  class Sale

  belongs_to :board

What I want is to get all Products where Product.sales is an empty collection. I can easily do this via something like Product.select { |product| product.sales.count.zero? } but that would be incredibly inefficient compared to SQL, and also it breaks us out of ActiveRecord so that scope wouldn’t be chainable.

Can anyone lend a hand?

2

In testing, the correct count (obtained via Product.select) is 155.

Using what I thought would work Product.includes(:sales).where(sales: {board_id: nil}).count gives me 172 records.

3

Screw it! I’m refactoring that relationship. When the app was originally made, a Board was something users could add products to to share on social media – the original dev also used that Board model to attach products to sales. Instead, I created a direct relationship to Sales & Products, through SaleProduct. Seems more fitting for the purpose.

4

Hi something like this may help:

Product
  .left_outer_joins(:sales)
  .distinct
  .group('sales.id')
  .having('count(sales.id) = 0')

Which creates the following SQL:

SELECT DISTINCT "products".* 
FROM "products" 
LEFT OUTER JOIN "board_products" ON "board_products"."product_id" = "products"."id" 
LEFT OUTER JOIN "boards" ON "boards"."id" = "board_products"."board_id" 
LEFT OUTER JOIN "sales" ON "sales"."board_id" = "boards"."id" 
GROUP BY sales.id 
HAVING (count(sales.id) = 0)

Hope it helps!