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

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.
lunaclaire (Guest)
on 2008-12-13 06:21
(Received via mailing list)
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
Mark Reginald J. (Guest)
on 2008-12-18 02:51
(Received via mailing list)
lunaclaire wrote:
> self.visiting_team_code
>   end
>
> 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

--
Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com
lunaclaire (Guest)
on 2008-12-18 05:14
(Received via mailing list)
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?
Mark Reginald J. (Guest)
on 2008-12-18 06:02
(Received via mailing list)
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


--
Rails Wheels - Find Plugins, List & Sell Plugins -
http://railswheels.com
This topic is locked and can not be replied to.