Rails - Feb 2009 - 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) }

--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Feb 16, 7:10 am, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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) }
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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

On Feb 16, 1:52 pm, Jeff Lewis
<jeff.bu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> 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
>
> On Feb 16, 7:10 am, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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) }
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

On Feb 16, 9:11 pm, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
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
> On Feb 16, 1:52 pm, Jeff Lewis
<jeff.bu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > 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
>
> > On Feb 16, 7:10 am, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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) }
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Feb 16, 9:11 pm, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> 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
>
> > On Feb 16, 1:52 pm, Jeff Lewis
<jeff.bu...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > 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
>
> > > On Feb 16, 7:10 am, Peter Browne
<peterpe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>
> > > > 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) }
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---

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
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---