thr3ads.net - Rails - Find Models which''s id does NOT appear in a collection [Jun 2007]

If this information is useful, please help other people find it:
Share via:

harm

2007-Jun-29 14:35 UTC

Find Models which''s id does NOT appear in a collection

Dear all,

I have a sticky problem and feel my knowledge of SQL is coming short.
Basically I have two Models, Foo and FooTracker. Foo hass_many
FooTrackers and FooTracker belongs_to Foo. So FooTracker has a field
foo_id.

At some point I have a FooTracker ID(which is not unique in the
foo_trackers table) and want to serve the next Foo which is NOT
already associated with a FooTracker. I have no idea how to do this. I
can of course do some thing like(not sure if it''s syntaxtacilly
correct):

def find_next(footracker_id)
  trackers = FooTracker.find_all_by_footracker_id(footracker_id)
  Foo.find_all.each do |foo|
    if trackers.foo_id.include?(foo)
      return foo
    end
  end
end

But that seems a bit of a waste. Especially since I''ll have to do this
very often. Does any one have a better suggestion? The DB schemas are
also not yet fully determined so perhaps there is room for
optimazation there as well.

Harm


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

Mel Riffe

2007-Jun-29 15:04 UTC

head link

Re: Find Models which''s id does NOT appear in a collection

Hey Harm,

My SQL knowledge isn''t at the master level, but are you requesting
something
like the following:

select * from foo where not in (select distinct foo_id from foo_trackers).

There might be ways to accomplish this in code but, if you''re going to
do
that often, it might be best to offload to the database.

Mel


On 6/29/07, harm <harmaarts-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:>
>
> Dear all,
>
> I have a sticky problem and feel my knowledge of SQL is coming short.
> Basically I have two Models, Foo and FooTracker. Foo hass_many
> FooTrackers and FooTracker belongs_to Foo. So FooTracker has a field
> foo_id.
>
> At some point I have a FooTracker ID(which is not unique in the
> foo_trackers table) and want to serve the next Foo which is NOT
> already associated with a FooTracker. I have no idea how to do this. I
> can of course do some thing like(not sure if it''s syntaxtacilly
> correct):
>
> def find_next(footracker_id)
>   trackers = FooTracker.find_all_by_footracker_id(footracker_id)
>   Foo.find_all.each do |foo|
>     if trackers.foo_id.include?(foo)
>       return foo
>     end
>   end
> end
>
> But that seems a bit of a waste. Especially since I''ll have to do
this
> very often. Does any one have a better suggestion? The DB schemas are
> also not yet fully determined so perhaps there is room for
> optimazation there as well.
>
> Harm
>
>
> >
>
--~--~---------~--~----~------------~-------~--~----~
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-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

harm

2007-Jun-29 17:24 UTC

head link

Re: Find Models which''s id does NOT appear in a collection

Yes, I think that is exactly what I need. And you seem masterful
enough to me. :)

I''ll give it a try!

Harm

On Jun 29, 5:04 pm, "Mel Riffe"
<mri...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:> Hey Harm,
>
> My SQL knowledge isn''t at the master level, but are you requesting
something
> like the following:
>
> select * from foo where not in (select distinct foo_id from foo_trackers).
>
> There might be ways to accomplish this in code but, if you''re
going to do
> that often, it might be best to offload to the database.
>
> Mel
>
> On 6/29/07, harm <harmaa...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
>
>
> > Dear all,
>
> > I have a sticky problem and feel my knowledge of SQL is coming short.
> > Basically I have two Models, Foo and FooTracker. Foo hass_many
> > FooTrackers and FooTracker belongs_to Foo. So FooTracker has a field
> > foo_id.
>
> > At some point I have a FooTracker ID(which is not unique in the
> > foo_trackers table) and want to serve the next Foo which is NOT
> > already associated with a FooTracker. I have no idea how to do this. I
> > can of course do some thing like(not sure if it''s
syntaxtacilly
> > correct):
>
> > def find_next(footracker_id)
> >   trackers = FooTracker.find_all_by_footracker_id(footracker_id)
> >   Foo.find_all.each do |foo|
> >     if trackers.foo_id.include?(foo)
> >       return foo
> >     end
> >   end
> > end
>
> > But that seems a bit of a waste. Especially since I''ll have
to do this
> > very often. Does any one have a better suggestion? The DB schemas are
> > also not yet fully determined so perhaps there is room for
> > optimazation there as well.
>
> > Harm

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

John Browning

2007-Jun-29 18:14 UTC

head link

Re: Find Models which''s id does NOT appear in a collection

If I understand this, the sql should be something like select id from  
foo as f where f.id not in (select foo_id from foo_tracker).

So a solution might be to do the sql and just start taking them off  
the top.

Hope this helps, I must admit I''m not entirely clear on what and why.  
But anyway...


On 29 Jun 2007, at 15:35, harm wrote:
>
> Dear all,
>
> I have a sticky problem and feel my knowledge of SQL is coming short.
> Basically I have two Models, Foo and FooTracker. Foo hass_many
> FooTrackers and FooTracker belongs_to Foo. So FooTracker has a field
> foo_id.
>
> At some point I have a FooTracker ID(which is not unique in the
> foo_trackers table) and want to serve the next Foo which is NOT
> already associated with a FooTracker. I have no idea how to do this. I
> can of course do some thing like(not sure if it''s syntaxtacilly
> correct):
>
> def find_next(footracker_id)
>   trackers = FooTracker.find_all_by_footracker_id(footracker_id)
>   Foo.find_all.each do |foo|
>     if trackers.foo_id.include?(foo)
>       return foo
>     end
>   end
> end
>
> But that seems a bit of a waste. Especially since I''ll have to do
this
> very often. Does any one have a better suggestion? The DB schemas are
> also not yet fully determined so perhaps there is room for
> optimazation there as well.
>
> Harm
>
>
> >
>

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

harm

2007-Jun-29 21:08 UTC

head link

Re: Find Models which''s id does NOT appear in a collection

Charming!
This is it:
SELECT * FROM foo AS f WHERE f.id NOT IN
        (SELECT f_id FROM foo_trackers WHERE foo_id = #{tracker_id})
AND reported = ''f'' LIMIT 1

Thanks a bunch for the help.

Harm

On Jun 29, 8:14 pm, John Browning
<list...-BLCF+mZFupPQT0dZR+AlfA@public.gmane.org>
wrote:> If I understand this, the sql should be something like select id from  
> foo as f where f.id not in (select foo_id from foo_tracker).
>
> So a solution might be to do the sql and just start taking them off  
> the top.
>
> Hope this helps, I must admit I''m not entirely clear on what and
why.
> But anyway...
>
> On 29 Jun 2007, at 15:35, harm wrote:
>
>
>
> > Dear all,
>
> > I have a sticky problem and feel my knowledge of SQL is coming short.
> > Basically I have two Models, Foo and FooTracker. Foo hass_many
> > FooTrackers and FooTracker belongs_to Foo. So FooTracker has a field
> > foo_id.
>
> > At some point I have a FooTracker ID(which is not unique in the
> > foo_trackers table) and want to serve the next Foo which is NOT
> > already associated with a FooTracker. I have no idea how to do this. I
> > can of course do some thing like(not sure if it''s
syntaxtacilly
> > correct):
>
> > def find_next(footracker_id)
> >   trackers = FooTracker.find_all_by_footracker_id(footracker_id)
> >   Foo.find_all.each do |foo|
> >     if trackers.foo_id.include?(foo)
> >       return foo
> >     end
> >   end
> > end
>
> > But that seems a bit of a waste. Especially since I''ll have
to do this
> > very often. Does any one have a better suggestion? The DB schemas are
> > also not yet fully determined so perhaps there is room for
> > optimazation there as well.
>
> > Harm

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

Rails - Jun 2007 - Find Models which's id does NOT appear in a collection

Find Models which''s id does NOT appear in a collection

Re: Find Models which''s id does NOT appear in a collection

Re: Find Models which''s id does NOT appear in a collection

Re: Find Models which''s id does NOT appear in a collection

Re: Find Models which''s id does NOT appear in a collection