Finding non-empty has_many associations with a named_scope

[Ryan Waldron]

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.

[Leonardo Mateo]

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

Hope it helps.

--
Leonardo Mateo.
There's no place like ~

[Ryan Waldron]

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!

--

You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To post to this group, send email to rubyonra...@googlegroups.com.
To unsubscribe from this group, send email to rubyonrails-ta...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

[maryam kamali]

ithank iam very rech no help  by

--