Finding non-empty has_many associations with a named_scope

If I have these classes:

class Customer < ActiveRecord::Base
has_many :jobs


class Job < ActiveRecord::Base
belongs_to :customer


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

customers_with_jobs = Customer.all.reject{|t|}

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.

Try this:

Hope it helps.

Well, it kind of does the wrong join:

c = Customer.all.reject{|j|}

=> 2125

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


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 => “

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

ithank iam very rech no help by