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.
2101d75ccd71c5dfb984991a7ba53b9b?d=identicon&s=25 harm (Guest)
on 2007-06-29 16: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
413faf211358af7cae68c1f2137de0f8?d=identicon&s=25 Mel Riffe (Guest)
on 2007-06-29 17: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
2101d75ccd71c5dfb984991a7ba53b9b?d=identicon&s=25 harm (Guest)
on 2007-06-29 19: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
Aff7605ddfa1b4c6b580089dea653a55?d=identicon&s=25 John Browning (Guest)
on 2007-06-29 20: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...
2101d75ccd71c5dfb984991a7ba53b9b?d=identicon&s=25 harm (Guest)
on 2007-06-29 23: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.