Forum: Ruby on Rails How do I create a find method that retrieves all of the records that do NOT belong to an associatio

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
47bb042a711bec6c2fc4c4e9b9bfe00c?d=identicon&s=25 Peter Browne (Guest)
on 2009-02-16 19:38
(Received via mailing list)
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) }
0d56133042e4814c1b2255ea655f2361?d=identicon&s=25 Jeff Burlysystems (jburly)
on 2009-02-16 20:53
(Received via mailing list)
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
47bb042a711bec6c2fc4c4e9b9bfe00c?d=identicon&s=25 Peter Browne (Guest)
on 2009-02-16 22:12
(Received via mailing list)
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).
81b61875e41eaa58887543635d556fca?d=identicon&s=25 Frederick Cheung (Guest)
on 2009-02-16 23:35
(Received via mailing list)
On Feb 16, 9:11 pm, Peter Browne <peterpe...@gmail.com> 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
47bb042a711bec6c2fc4c4e9b9bfe00c?d=identicon&s=25 Peter Browne (Guest)
on 2009-02-17 02:23
(Received via mailing list)
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 Cheung <frederick.che...@gmail.com>
6883e5ef03484d4fcef507d7b4f1d243?d=identicon&s=25 Matt Jones (Guest)
on 2009-02-18 00:33
(Received via mailing list)
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
This topic is locked and can not be replied to.