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

Rails
1

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?® }

2

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

3

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

4

On Feb 16, 9:11 pm, Peter B.e [email protected] 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 :slight_smile:

Fred

5

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. [email protected]

6

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.