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

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

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

I'll give it a try!

Harm

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...

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