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

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

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

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

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

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