# How do I create a find method that retrieves all of the records that do NOT belong to an associatio

**URL:** <https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475>\
**Category:** Rails\
**Created:** [February 16, 2009, 7:38pm UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475 "2009-02-16T19:38:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 16, 2009, 7:38pm UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/1 "2009-02-16T19:38:13Z")

</div>

I have 3 models with a has\_many through association:

class Recording  
has\_many :listenings  
has\_many :listeners, :through =\> :listenings, :source =\> :user  
…  
end

class User  
has\_many :listenings, :foreign\_key =\> “listener\_id”  
has\_many :listened\_recordings, :foreign\_key =\>  
“listener\_id”, :through =\> :listenings, :source =\> :recording  
…  
end

class Listening  
belongs\_to :recording  
belongs\_to :listener, :class\_name =\> “User”  
…  
end

So I can get the recordings the user has listened to, easily with:

user.listened\_recordings

How do create a find method that retrieves all of the recordings the  
user has not listened to?  
The way I have been doing it is:

Recording.all.reject { |r| listened\_recordings.include?® }

---

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 16, 2009, 8:53pm UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/2 "2009-02-16T20:53:26Z")

</div>

Hi Peter,

One way, via two queries would be something like:

# in app/models/user.rb:

def not\_yet\_listened\_recordings  
ls = Listening.find\_by\_sql(["select distinct recording\_id from  
listenings where user\_id=?, self.id])  
return Recording.find(:all) if not ls or ls.empty?

```
criteria_a = []
vals_a = []
ls.each do |l|
  criteria_a << "id<>?"
  vals_a << l.recording_id.to_i
end
qstr = "select * from recordings where #{criteria_a.join(' and

```

')}"  
sql\_a = [qstr] + vals\_a  
return Recording.find\_by\_sql(sql\_a)  
end

…

which you would then be able to call like:

…  
user.not\_yet\_listened\_recordings  
…

Jeff

---

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 16, 2009, 10:12pm UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/3 "2009-02-16T22:12:18Z")

</div>

Thanks Jeff,

I just tried this out and it’s much faster (5-8x) than my method (I’m  
guessing it’s because it dosen’t have to select \* from recordings).

---

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 16, 2009, 11:35pm UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/4 "2009-02-16T23:35:19Z")

</div>

On Feb 16, 9:11 pm, Peter B.e [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> Thanks Jeff,
> 
> I just tried this out and it’s much faster (5-8x) than my method (I’m  
> guessing it’s because it dosen’t have to select \* from recordings).

You could also do something like this

select recordings.\* from recordings  
left outer join listened\_recordings on recording\_id = recordings.id  
and user\_id = 12345  
where listened\_recordings.id IS NULL

rewriting that sql as something you can use is left as an exercise to  
the reader 🙂

Fred

---

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 17, 2009, 2:23am UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/5 "2009-02-17T02:23:56Z")

</div>

Thanks Fred,

Here’s how I implemented this method:

```
Recording.find(
  :all,
  :joins => "LEFT OUTER JOIN listenings ON recording_id =

```

recordings.id AND listener\_id = #{self.id}",  
:conditions =\> [“recording\_id IS NULL”]  
)

I benchmarked the 3 methods (1. mine, 2. Jeff’s, 3. Fred’s):

#1 4.540000 0.360000 4.900000 ( 6.359951)  
#2 0.510000 0.050000 0.560000 ( 0.854199)  
#3 0.470000 0.040000 0.510000 ( 0.726049)

On Feb 16, 4:34 pm, Frederick C. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![Peter\_B.e](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/7bcc69/32.png) [@Peter\_B.e](https://www.ruby-forum.com/u/Peter_B.e)\
**Post date:** [February 18, 2009, 12:33am UTC](https://www.ruby-forum.com/t/how-do-i-create-a-find-method-that-retrieves-all-of-the-records-that-do-not-belong-to-an-associatio/160475/6 "2009-02-18T00:33:59Z")

</div>

On Jeff’s method, one quick optimization would be to switch to using  
a NOT IN query:

# in app/models/user.rb:

def not\_yet\_listened\_recordings  
ls = Listening.find\_by\_sql(["select distinct recording\_id from  
listenings where user\_id=?, self.id])  
return Recording.find(:all) if not ls or ls.empty?

```
Recording.find(:all, :conditions => ['id NOT IN (?)', ls])

```

end

A lot less code for the same result…

Overall though, the Fred’s LEFT OUTER JOIN thing is much more elegant;  
it also lends itself to being used as a named scope - which you’ll  
want when  
it comes time to paginate the list.

Example:

# in recording.rb:

named\_scope :not\_listened, lambda { |u| { :joins =\> “LEFT OUTER JOIN  
listenings ON recording\_id = recordings.id AND listener\_id = #  
{u.id}”, :conditions =\> [“recording\_id IS NULL”] } }

Then you could have a controller action (using mislav-will\_paginate);

def show\_not\_listened

# get a user object somehow - either logged in user or from params

@recordings = Recording.not\_listened(user).paginate(params[:page] ||  
1)  
end

–Matt J.