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

rubyonrails-talk
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?(r) }

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

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)

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 Jones