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!
On Sat, Dec 12, 2009 at 6:41 PM, Leonardo Mateo <[email protected]>wrote:
> On Sat, Dec 12, 2009 at 7:40 PM, Ryan Waldron <[email protected]> wrote:
> > 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.
>
> Try this:
> http://pastie.org/740896
>
> Hope it helps.
>
> --
> Leonardo Mateo.
> There's no place like ~
>
> --
>
> 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 [email protected].
> To unsubscribe from this group, send email to
> [email protected]<rubyonrails-talk%[email protected]>
> .
> For more options, visit this group at
> http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>
>
--
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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
