CODEWITHSUNDEEP
ruby-on-railsactiverecord
Lee Quarella
Lee Quarella

Reputation: 4732

Rails has_many, find only those with children

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?

Upvotes: 8

Views: 3953

Answers (5)

Dwayne Forde
Dwayne Forde

Reputation: 1394

This will handle the duplication.

Product.joins(:registered_products).uniq

Upvotes: 5

Tom H
Tom H

Reputation: 207

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

Upvotes: 1

mnelson
mnelson

Reputation: 3012

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.

Upvotes: 12

gduq
gduq

Reputation: 1454

You could use counter_cache (http://railscasts.com/episodes/23-counter-cache-column). It should be faster than actually doing the join just to find out if there are any children.

Upvotes: 0

Harish Shetty
Harish Shetty

Reputation: 64363

class Product
  has_many :registered_products
end

List of Products (with at-least one registered product) - Eager loaded registered products

Product.all(:include => :registered_products, 
  :conditions => "registered_products.id IS NULL")

List of Products (with at-least one registered product) - Without eager loading

Product.all(:joins => :registered_products)

Upvotes: 0

Related Questions