ActiveRecord find all parents that have associated children

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.

Note:

This solution eager loads suppliers.

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

Answer 4

Simpler version:

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

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