views:

385

answers:

6

Assuming rails app has the following models song, play, radio_station: song has_many plays play has_many radio_stations radio_station has attribute "call_sign"

I want to create one find for Song that would return for me the all songs where the last play (of the song) was played by a radio station of "WKRP"

I could do

found = [] Song.find(:all,:include=>[{:plays=>[:radio_station]}]).each do |song| found << song if song.plays.last.radio_station.call_sign == "WKRP" end

but this would mean that all songs must be pulled down from the DB first and looped through... which would get slow as the # of songs and plays builds.

How can I put this into one find condition?

It seems like this is something that should be doable - but I can't figure out how and I am not really an SQL guru ...

and then.. would love to squeeze it into a named scope so i could call it like:

Song.find_all_last_played_at("WKRP")

A: 

You should probably look at named scopes.

class Song
  named_scope :last_played_by, lambda {|call_sign| {:conditions => ["plays.radio_stations.call_sign = ?", call_sign]}}
end

You would use it as such

Song.last_played_by("WKRZ")

However, I realized that I don't know what mechanism you are using to find the last play. But you could split it up into two named scopes as such:

class Song
  named_scope :played_by, lambda {|call_sign| {:conditions => ["plays.radio_station.call_sign = ?", call_sign]}}
  named_scope :last_play {:conditions => ["MAX(plays.created_at)"]
end

To use it:

Song.last_play.played_by("WKRZ")

I believe that is correct, but I haven't tested it out and I'm not all that familiar with named scopes.

Jaryl
A: 

Thanks Jaryl for reply..

The 'last' would be most recent play so created_at would be correct.

Is your second example doing two separate pulls from the DB? I was hoping to maximize the speed of the look up and use one named_scope (if possible)

I tried starting with using last_play

I had to change it to (added comma and removed curly braces) to get it to execute in console ...

named_scope :last_play, :conditions => ["MAX(plays.created_at)"]

but I get this error:

ActiveRecord::StatementInvalid: Mysql::Error: Invalid use of group function: SELECT * FROM `songs` WHERE (MAX(plays.created_at))

Are you able to get this condition working?

Streamline
Combining the two named scopes into one doesn't maximize your speed, because if you chain them together (Song.last_play.played_by("WRXS"), Rails generates a single SQL statement.
insane.dreamer
+1  A: 

This probably can't be done by ActiveRecord finders. You have to roll some custom SQL with a subquery (MySQL 5 required):


SELECT s.* FROM songs s LEFT JOIN plays p
 WHERE p.radio_station_id = [id of WKRP] 
   AND  p.created_at = (SELECT MAX(created_at) FROM plays p2 
                         WHERE p2.song_id = s.id)

Load these songs by using find_by_sql:


Song.find_by_sql('SELECT ...')

You could refactor it using a named_scope if you want to.



class Song < ActiveRecord::Base
  has_many :plays

  named_scope :last_played_on, lambda { |radio_station| 
    { :conditions => ['plays.created_at = (SELECT MAX(p2.created_at) FROM plays p2 
        WHERE p2.song_id = songs.id) AND plays.radio_station_id = ?', radio_station.id],
      :joins => [:plays] }
  }
end

@wkrp = RadioStation.find_by_name('WKRP') 
Song.last_played_on(@wkrp).each { |song| ... }

Note that using subqueries can be slow. You may want to cache the latest play_id of a song as a field in the songs table to make the queries easier and faster. Start by adding a last_play_id field to your songs table.


class Play < ActiveRecord::Base
  belongs_to :song

  after_create do |play|
    play.song.last_play_id = play.id
    play.song.save!
  end
end 
wvanbergen
thanks - how would I do your last suggestion? the caching of the lastest play_id? does this mean it is not saved to the DB but available in each record?
Streamline
Add a last_play_id to the songs table. Then add some code like this:class Play after_create do |play| play.song.last_play_id = play.id play.song.save! endend
wvanbergen
A: 

For performance, you should probably do what wvangergen suggests. But for syntatic sugar, I think this might work:

class Song
  has_many :plays, :order => 'created_at' do
    def last_played
      self.maximum(:created-at)
    end
  end
end

class Play
  named_scope :by, lambda {|call_sign| {:conditions => ["radio_stations.call_sign = ?", call_sign]}}
end

To use:

Song.plays.last_played.by("WKRZ")

I'm not testing the code out, so apologies for the syntax errors =) Now that I'm reading it, I don't think it will work, but I hope you might be able to figure it out or something.

Jaryl
A: 

I think you can do that starting with your associations (Note that the default 'order' is 'created_at'):

class Song
  has_many :plays
  has_many :radio_staions, :through => :plays, :uniq => true
  has_one  :last_radio_station, :through => :plays, :source => :last_radio_station

  named_scope :last_played_at, lambda {|call_sign| {:include => :last_radio_station, :conditions => ["radio_stations.call_sign = ?", call_sign]}}
end

This expects your Play model to have:

class Play
  has_one :last_radio_station, :class_name => 'RadioStation', :order => 'created_at'
  has_many :radio_stations

Then you can simply call:

Song.last_played_at("WKRZ")

I haven't tried this myself, but hopefully it will work.

A: 

Created a named_scope that pulls all the plays for that particular radio station, with order 'created_at DESC' and then chain .last on to the end of the returned query. Along the lines of:

Song.played_by('WRXD').last
insane.dreamer