Rails - Dec 2009 - Finding non-empty has_many associations with a named_scope

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.

--

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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Sat, Dec 12, 2009 at 7:40 PM, Ryan Waldron
<rew-L2nCScK6t0HQT0dZR+AlfA@public.gmane.org>
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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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
<leonardomateo-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>wrote:
> On Sat, Dec 12, 2009 at 7:40 PM, Ryan Waldron
<rew-L2nCScK6t0HQT0dZR+AlfA@public.gmane.org> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

ithank iam very rech no help  by

On Sun, Dec 13, 2009 at 2:10 AM, Ryan Waldron
<rew-L2nCScK6t0HQT0dZR+AlfA@public.gmane.org> 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.
>
> --
> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.