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

**URL:** <https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169>\
**Category:** Rails\
**Created:** [June 29, 2007, 4:36pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169 "2007-06-29T16:36:39Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![harm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b4bc9f/32.png) [@harm](https://www.ruby-forum.com/u/harm)\
**Post date:** [June 29, 2007, 4:36pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169/1 "2007-06-29T16:36:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![harm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b4bc9f/32.png) [@harm](https://www.ruby-forum.com/u/harm)\
**Post date:** [June 29, 2007, 5:05pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169/2 "2007-06-29T17:05:42Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![harm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b4bc9f/32.png) [@harm](https://www.ruby-forum.com/u/harm)\
**Post date:** [June 29, 2007, 7:25pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169/3 "2007-06-29T19:25:56Z")

</div>

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

I’ll give it a try!

Harm

---

<div class="post-metadata">

**Author:** ![harm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b4bc9f/32.png) [@harm](https://www.ruby-forum.com/u/harm)\
**Post date:** [June 29, 2007, 8:15pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169/4 "2007-06-29T20:15:59Z")

</div>

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…

---

<div class="post-metadata">

**Author:** ![harm](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/h/b4bc9f/32.png) [@harm](https://www.ruby-forum.com/u/harm)\
**Post date:** [June 29, 2007, 11:09pm UTC](https://www.ruby-forum.com/t/find-models-whichs-id-does-not-appear-in-a-collection/108169/5 "2007-06-29T23:09:25Z")

</div>

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