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 <[email protected]>
wrote:
> On Feb 16, 9:11 pm, Peter Browne <[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 :-)
>
> Fred
>
> > On Feb 16, 1:52 pm, Jeff Lewis <[email protected]> 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 <[email protected]> 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 [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---
