Topic: Need to work on my relationships

Or so my wife tells me ...

I'm migrating a PHP app that tracks a recreational sports league to Rails.

New teams are drafted each year, so they don't carry over from season to season.

Six main models are mostly self explanatory:

class Season < ActiveRecord::Base
  has_many :teams
  has_many :games
  has_many :rosters
end

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

class Player < ActiveRecord::Base
  has_many :teams, :through => :rosters
  has_many :rosters, :dependent => :destroy
end

class Roster < ActiveRecord::Base
  belongs_to :player
  belongs_to :team
  belongs_to :season
end

class Game < ActiveRecord::Base
  belongs_to :season
  has_many :reports
end

# players can submit recaps of games as reports
class Report < ActiveRecord::Base
  belongs_to :game
  belongs_to :player
  has_many :teams, :through => :players
end


Here's the problem:

The database 'cheats' a bit on games. Each game has exactly two teams. The schema for games table:

 create_table "games", :force => true do |t|
    t.column "season", :string, :limit => 8
    t.column "week", :integer, :limit => 2
    t.column "date", :date, :default => #<Date: 4907879/2,0,2299161>
    t.column "time", :string, :limit => 0, :default => "12"
    t.column "field", :string, :limit => 0
    t.column "game_type", :string, :limit => 0, :default => "semi", :null => false
    t.column "team_1_id", :integer, :limit => 6
    t.column "team_2_id", :integer, :limit => 6
    t.column "team_1_pts", :integer, :limit => 2
    t.column "team_2_pts", :integer, :limit => 2
    t.column "winner", :integer, :limit => 6
    t.column "loser", :integer, :limit => 6
  end

Instead of creating a join table (maybe Participants or Opponents?) it was easy in PHP to just define columns "team_1_id" and "team_2_id" and use an OR statement in the SQL:

SELECT * FROM games WHERE (team_1_id=1 OR team_2_id=1)

Otherwise, I'd have a table with many, many rows and only two columns. At the time, it seemed like overkill. But now ... the current setup doesn't allow me to define a relationship in Rails. I guess I need a:

has_many :teams, :through => :participants

Also, the games table defines winner and loser columns, so I guess I need something like:

has_one :winner
has_one :loser

... with a self-table joiny thing.

I still haven't tackled how to display standings (and points for/against). I had a single, honking SQL statement in PHP that calculated this on the fly, but I'm wondering if there's a Rails-friendlier way ...


Am I on the right track here? Thanks for any input.

Re: Need to work on my relationships

Yeah, I would have a participants table with team_id, game_id, and points columns. As for winner and loser, can't you calculate the winner and loser by which team has the most points? Or is there ever an exception to this?

That should cut the games table in half. You can create a winner/loser method in the Game model if you need to:

class Game < ActiveRecord::Base
  def winner
    teams.find(:first, :include => :participants, :order => 'participants.points DESC') # I think that's right anyway
  end
end

As for calculating the standings, I don't know the math involved so I can't help you much there (yeah, I don't play sports, heh). Rails has some helper calculation methods that might help. If you post the equation or that SQL statement you used before I can try to come up with something.

Last edited by ryanb (2006-08-30 01:24:46)

Railscasts - Free Ruby on Rails Screencasts

Re: Need to work on my relationships

Thanks a lot, Ryan. Yes, I can just calculate winners and losers by points. That was another (improper) lazy shortcut. Once I create that definition, I'm guessing standings (and points for/against) should be relatively easy, too.

One question: How efficient is Rails with these find() queries?

I'd have to use a winner() method for every game played in order to get won-lost records for teams. Am I then hitting the database with a query every single time the method is run, or is the find() method working on a collection it's already retrieved from the db?

Re: Need to work on my relationships

I think it will call an SQL query every time you call the winner method, but don't worry about that at the beginning. Get it working with a good design first, then worry about performance. Optimizing later is an important best practice because:

1. You know exactly what needs optimized - no guessing.
2. You are more familiar with the entire application to see the best way to optimize something.

I see a few ways to optimize the winner method, including caching the winner into a column in the database which is what you had originally - but that introduces duplication which you must keep in sync and may not be the best way to optimize it.

Railscasts - Free Ruby on Rails Screencasts

Re: Need to work on my relationships

Hm, now after creating an Opponent model (I like that instead of Participant), I can't seem to grab games by season and team.

Relevant models:

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

class Game < ActiveRecord::Base
  belongs_to :season
  has_many :reports
  has_many :teams, :through => :opponents
end

class Opponent < ActiveRecord::Base
  belongs_to :team
  belongs_to :game
end


 def get_games_for_season_and_team
     @games = Game.find(:all,
                        :conditions => ["season_id=? and opponents.team_id=?", session[:season][:id], params[:team_id]],
                        :include => :teams
                        )
  end

I get "Could not find the association :opponents in model Team"

I know I'm going to feel stupid, but what am I doing wrong?

Re: Need to work on my relationships

You need to set has_many :opponents in Team and Game.

Railscasts - Free Ruby on Rails Screencasts

Re: Need to work on my relationships

Ah, thanks, Ryan. I had tried that but was getting errors. I think I needed a reset, however, because now it's working. One more question: Do I need to reference the tables and columns specifically in my :conditions statement?

So, games.season_id and opponents.team_id?

     @games = Game.find(:all,
                        :conditions => ["games.season_id=? and opponents.team_id=?", session[:season][:id], params[:team_id]],
                        :include => :teams
                        )

It works, but is that the preferred way? It seems like everything else in Rails prefers to use the ORM mapping rather than calling specific tables and columns.

Re: Need to work on my relationships

Yeah, you need to specify the table name in the conditions if the column exists in both tables. I don't think that's the case for you though, so you should be able to get by with something like this:

@games = Game.find(:all, :conditions => ['season_id=? and team_id=?', session[:season][:id], params[:team_id]], :include => :opponents)

You can include opponents instead of teams because teams has a season_id column which would require you to specify the games table.

You can also try this:

@games = Game.find_all_by_season_id_and_team_id(session[:season][:id], params[:team_id], :include => :opponents)

Atleast I think that will work.

Railscasts - Free Ruby on Rails Screencasts

Re: Need to work on my relationships

Thanks for all your help, Ryan. I'll be sure to give back when I actually have something to give back!

Last edited by itchyboy (2006-08-31 13:06:12)

Re: Need to work on my relationships

Ugh. Only problem is, this is only picking up one of the teams for each game, not both. The way the conditions are written, I can see why: It only grabs the opponents row with the appropriate team_id, not the opponent of that team.

So in my view, when I want to do:

game.opponents.collect {|o| ["#{o.team.nickname} #{o.points}"]

I only get one team. Do I need to do a find() on opponents for each game or is it possible to batch all this into the initial query?

(I keep wanting to just do find_by_sql, but I want to first see if there's a Rails-appropriate way I'm missing.)

Thanks for any help.

Re: Need to work on my relationships

itchyboy wrote:

game.opponents.collect {|o| ["#{o.team.nickname} #{o.points}"]

I only get one team. Do I need to do a find() on opponents for each game or is it possible to batch all this into the initial query?

Hmm, you should get two teams when you do this, that is if a game has two opponents - so game.opponents.count == 2, correct? You shouldn't need to do find or find_by_sql or anything.

Railscasts - Free Ruby on Rails Screencasts

Re: Need to work on my relationships

Thanks, again, Ryan, I solved it.

I was using the opponents table in my :conditions:

   Game.find( :all,
              :conditions => ["games.season_id=? and opponents.team_id=?", session[:season][:id], params[:team]],
              :include => [:opponents])

And I changed it to reference the teams table:

   Game.find( :all,
              :conditions => ["games.season_id=? and teams.id=?", session[:season][:id], params[:team]],
              :include => [:teams])