how to do "special queries" ala The Rails Way on "associated" conditions?

rubyonrails-talk
1

I have the following in a model class:

class Game < ActiveRecord::Base   has_many :player_stats   has_many :players, :through => :player_stats

  def visitor_stats     stats =     player_stats.each do |stat|       stats << stat if stat.player.team.team_code == self.visiting_team_code     end     stats   end

  def home_stats     stats =     player_stats.each do |stat|       stats << stat if stat.player.team.team_code == self.home_team_code     end     stats   end end

I understand that this isn't the most efficient or elegant way to do things. I gleaned this most recently from the posting at (see his Case 3):

http://www.therailsway.com/tags/has_many

So, I would like to do something like the following, but I'm having a problem with the conditions:

  has_many :visitor_stats, :class_name=>"PlayerStat", :conditions=> <what???>

the condition here should be something matching "player.team.team_code == self.visiting_team_code" in the code I have now.

  has_many :home_stats, :class_name=>"PlayerStat", :conditions=> <what???>

and here it should be something matching "player.team.team_code == self.home_team_code" in the existing code.

The conditions specified need to be something that could go in a SQL statement, right? How would I make the conditions work thru the associations of a PlayerStat belonging to a Player belonging to a Team which has a team_code. And matching it to this Game's visiting_team_code or home_team_code

2

lunaclaire wrote:

I have the following in a model class:

class Game < ActiveRecord::Base   has_many :player_stats   has_many :players, :through => :player_stats

  def visitor_stats     stats =     player_stats.each do |stat|       stats << stat if stat.player.team.team_code == self.visiting_team_code     end     stats   end

  def home_stats     stats =     player_stats.each do |stat|       stats << stat if stat.player.team.team_code == self.home_team_code     end     stats   end end

I understand that this isn't the most efficient or elegant way to do things. I gleaned this most recently from the posting at (see his Case 3):

http://www.therailsway.com/tags/has_many

So, I would like to do something like the following, but I'm having a problem with the conditions:

  has_many :visitor_stats, :class_name=>"PlayerStat", :conditions=> <what???>

the condition here should be something matching "player.team.team_code == self.visiting_team_code" in the code I have now.

  has_many :home_stats, :class_name=>"PlayerStat", :conditions=> <what???>

and here it should be something matching "player.team.team_code == self.home_team_code" in the existing code.

The conditions specified need to be something that could go in a SQL statement, right? How would I make the conditions work thru the associations of a PlayerStat belonging to a Player belonging to a Team which has a team_code. And matching it to this Game's visiting_team_code or home_team_code

I don't think what you need can be formulated into normal has_manys. You'll either have to use the has_many :finder_sql option that includes joins to the players and teams tables, allowing you to add the appropriate SQL conditions, or you can keep visitor_stats and home_stats as methods, but have them call find:

def home_stats    player_stats.find :all, :select => 'player_stats.*',                      :joins => {:player => :team},                      :conditions => "teams.team_code = #{home_team_code}" end

3

Thanks for the reply, Mark.

One of the assertions in that RailsWay posting was that by using the has_many approach, I'd gain efficiency because of caching. I don't understand enough about caching yet to see why, but let me ask this about using your approach... would the code you show with the :joins clause be more or less efficient than the code I have above in my current home_stats method?

4

lunaclaire wrote:

Thanks for the reply, Mark.

One of the assertions in that RailsWay posting was that by using the has_many approach, I'd gain efficiency because of caching. I don't understand enough about caching yet to see why, but let me ask this about using your approach... would the code you show with the :joins clause be more or less efficient than the code I have above in my current home_stats method?

Your original code becomes more efficient if: - Both visitor and home stats are often used together in the same request - The collections are small

But because you were not pre-loading the the player and team associations using :include, what you had is very inefficient. Each time round each loop a DB fetch is being made for the player of a stat record and the team of a player record.

You could add caching to the method I posted:

def home_stats(reload = false)    @home_stats = nil if reload    @home_stats ||= player_stats.find ... end