Rails has_many, find only those with children

Question

My "products" table has_many :registered_products.

I want to use something like

products.find(:has_registered_products) 

where that will return only the products that also have an entry in the registered_products table. How could I achieve this?

Answer 1

As long as you have a foreign_key for the product in the registered_products table you can do:

has_many :registered_products
named_scope :with_registered_products, :joins => :registered_products

# if you're using rails 3
scope :with_registered_products, joins(:registered_products)

and that will only return products that have at least one associated registered product.

Answer 2

This will handle the duplication.

Product.joins(:registered_products).uniq

Answer 3

As Jakob points out, if there are multiple child records, you need to ensure that you are not returning multiple parent objects.

Using the "select distinct" will work, but the select statement could interfere when this scope is combined with other scopes.

Another option is to ensure that you join to a child table that only has unique records. You can do this by composing your join as follows

class Product < ActiveRecord::Base
 has_many registered_products

 scope :with_registered_products, joins('join (select distinct product_id from registered_products) rp123456 on rp123456.product_id = products.id')
end