Finding non-empty has_many associations with a named_scope

rubyonrails-talk
1

If I have these classes:

class Customer < ActiveRecord::Base has_many :jobs … end

class Job < ActiveRecord::Base belongs_to :customer … end

can I construct a named scope that returns the equivalent collection (but hopefully more efficient) to this:

customers_with_jobs = Customer.all.reject{|t| t.jobs.empty?}

I don’t care about the number of jobs a customer has, or the state any of those jobs are in; I just want a named scope that will give me the ones who have jobs associated with them, hopefully without the massive N+1 query problem that the reject{…} gets me.

It seems like this should be a relatively simple thing, but it has eluded me so far.

2

Try this: http://pastie.org/740896

Hope it helps.

3

Well, it kind of does the wrong join:

c = Customer.all.reject{|j| j.jobs.empty?} […] c.size => 2125

c = Customer.with_jobs Customer Load (165.5ms) SELECT SQL_NO_CACHE customers.* FROM customers INNER JOIN jobs ON jobs.customer_id = customers.id WHERE (jobs.customer_id IS NOT NULL)

…

c.size 15692

What it ends up giving me is one customer record for every job that had a customer, which means I actually get duplicate customer records. If a customer has 3 jobs, then he’s in that result set 3 times.

What I’m after is a result set of all the customers that had a job, but no duplicates.

But while writing this it occurred to me: add a :group to your suggestion. So I tried this, and it works:

named_scope :with_jobs, :joins => [:jobs], :conditions => “jobs.customer_id IS NOT NULL”, :group => “customers.id”

With the :group added, it worked, so you rock! Thanks so much for the help!

4

ithank iam very rech no help by