Forum: Ruby on Rails Find Models which's id does NOT appear in a collection

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
harm (Guest)
on 2007-06-29 18:36
(Received via mailing list)
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
Mel R. (Guest)
on 2007-06-29 19:05
(Received via mailing list)
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
harm (Guest)
on 2007-06-29 21:25
(Received via mailing list)
Yes, I think that is exactly what I need. And you seem masterful
enough to me. :)

I'll give it a try!

Harm
John B. (Guest)
on 2007-06-29 22:15
(Received via mailing list)
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...
harm (Guest)
on 2007-06-30 01:09
(Received via mailing list)
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
This topic is locked and can not be replied to.