CODEWITHSUNDEEP
ruby-on-railsactiverecordassociations
brad
brad

Reputation: 32335

ActiveRecord find all parents that have associated children

I don't know why I can't figure this out, I think it should be fairly simple. I have two models (see below). I'm trying to come up with a named scope for SupplierCategory that would find all SupplierCategory(s) (including :suppliers) who's associated Supplier(s) are not empty.

I tried a straight up join, named_scope :with_suppliers, :joins => :suppliers which gives me only categories with suppliers, but it gives me each category listed separately, so if a category has 2 suppliers, i get the category twice in the returned array:

Currently I'm using:

named_scope :with_suppliers, :include => :suppliers

and then in my view I'm using:

<%= render :partial => 'category', :collection => @categories.find_all{|c| !c.suppliers.empty? } %>

Not exactly eloquent but illustrates what I'm trying to achieve.

Class Definitions

class SupplierCategory < AR
  has_many :suppliers, :order => "name"
end

class Supplier < AR
  belongs_to :supplier
end

Upvotes: 10

Views: 7140

Answers (4)

Harish Shetty
Harish Shetty

Reputation: 64363

Here is one more approach:

named_scope :with_suppliers, :include    => :suppliers, 
                             :conditions => "suppliers.id IS NOT NULL"

This works because Rails uses OUTER JOIN for include clause. When no matching rows are found the query returns NULL values for supplier columns. Hence NOT NULL check returns the matching rows.

Rails 4

scope :with_suppliers, { includes(:steps).where("steps.id IS NOT NULL") }

Or using a static method:

def self.with_suppliers
  includes(:steps).where("steps.id IS NOT NULL")
end

Note:

This solution eager loads suppliers.

categories = SupplierCategory.with_suppliers
categories.first.suppliers #loaded from memory

Upvotes: 12

klew
klew

Reputation: 14967

Simpler version:

named_scope :with_suppliers, :joins => :suppliers, :group => :id

If you want to use it frequently, consider using counter_cache.

Upvotes: 3

Ju Nogueira
Ju Nogueira

Reputation: 8461

I believe it would be something like

#model SupplierCategory
named_scope :with_suppliers, 
   :joins => :suppliers,
   :select => "distinct(supplier_categories), supplier_categories.*",
   :conditions => "suppliers.supplier_categories_id = supplier_categories.id"

Let me know if it works for you.

Edit: Using fl00r's idea:

named_scope :with_suppliers, 
   :joins => :suppliers,
   :select => "distinct(supplier_categories), supplier_categories.*",
   :having => "count(supliers.id) > 0"

I believe this is the faster way.

Upvotes: 1

fl00r
fl00r

Reputation: 83680

class SupplierCategory < AR
  has_many :supliers

  def self.with_supliers
    self.all.reject{ |c| c.supliers.empty? }
  end
end

SupplierCategory.with_supliers
#=> Array of SuplierCategories with supliers

Another way more flexible using named_scope

class SupplierCategory < AR
  has_many :supliers
  named_scope :with_supliers, :joins => :supliers, :select => 'distinct(suplier_categories.id), suplier_categories.*', :having => "count(supliers.id) > 0"
end

SupplierCategory.with_supliers(:all, :limit => 4)
#=> first 4 SupplierCategories with suppliers

Upvotes: 2

Related Questions