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

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




--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

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

-- 
Rails Wheels - Find Plugins, List & Sell Plugins - http://railswheels.com

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

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?


On Dec 17, 4:50 pm, Mark Reginald James
<m...-bzGI/hKkdgQnC9Muvcwxkw@public.gmane.org>
wrote:
> 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
>
> --
> Rails Wheels - Find Plugins, List & Sell Plugins
-http://railswheels.com
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---

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

--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---