Topic: Joining more tables in query

This should be easy, but I don't know the correct way to do it in Rails.

My app tracks a recreational sports league. Models include Game, Team, Player and Report (where players can submit writeups on their games). I want to be able to show reports while referencing the game (of course), the player (author) and the player's team.

Relevant models:

class Game < ActiveRecord::Base
   has_many :reports
end

class Report < ActiveRecord::Base
   belongs_to :game
   belongs_to :player
end

# (one team per season, but many over several seasons)
class Player < ActiveRecord::Base
   has_many :teams, :through => :rosters
end

class Team < ActiveRecord::Base
   has_many :players, :through => :rosters
   has_many :games
   belongs_to :season
end


Controller method:
  def show_reports
    week = params[:week] || 1
    @games = Game.find_all_by_season_and_week(session[:season],week)
  end

Rails (very nicely) joins the player columns, so I can loop through games and show reports, along with player name:

<h2>Reports for <%= session[:season_name] %></h2>
<% for game in @games %>
    <% if game.reports.length > 0 %>
        <p>
          <b><%= game.date.strftime("%b %d, %Y") %>:</b> <%= "#{game.time}, Field #{game.field}" %>
        </p>
        <ul>
        <%
        game.reports.each do |r|
        %>
        <li>
            <b><%= "Report by: #{r.player.first_name} #{r.player.last_name}" %></b><br />
        <%= "#{r.text}" %>
        </li>
        <% end # each %>
        </ul>
    <% end # if reports %>
<% end %>

But I'd also like to add team info, like:
<%= "Report by: #{r.player.first_name} #{r.player.last_name}, #{r.player.team.nickname}" %>

Is there some 'built-in' way to include data from teams table? Do I need to add a :join to the find() method? How efficient is this? BTW, how do I view the actual SQL query Rails is building?

Re: Joining more tables in query

Try using :include => :teams as a parameter in your find_all method.

As for viewing the SQL queries, check your log files (development.log, production.log, etc.).

vinnie - rails forum admin

Re: Joining more tables in query

If I change the show_reports line to:
@games = Game.find_all_by_season_and_week(session[:season], week, :include => :teams)

I get:
Association named 'teams' was not found; perhaps you misspelled it?

So I need to redo my associations, but I'm not quite sure how to link teams to reports ...

Re: Joining more tables in query

Your Team has_many :games but your Game doesn't belongs_to :team.  I think if you tell your Game that it belongs to a team the :include should work.

Re: Joining more tables in query

Thanks, guys. I just remembered that I had planned to put my team info into a session variable (since it'll be used on most pages) and pull from that. That idea had gotten lost in the shuffle.