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

Rails
1

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

2

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

3

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

4

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…

5

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