Topic: A table the joins another table twice

Hi,

I am just getting started with rails and I've got a question. 

I have two tables.  One named games and one named teams.  The games table has a date and two id fields for the two teams that are playing each other.  I know that if I were linking to just one team the field would be named team_id, but in this case I don't know what to do for the second team.  Thanks for your help.


Matt

Re: A table the joins another table twice

You can have two foreign keys for one table. Ex. TeamA_id and TeamB_id.

http://dev.mysql.com/doc/refman/5.0/en/ … aints.html

Re: A table the joins another table twice

You could probably do a join and then rename the attributes by using the "as" keyword. (I'm just learning this DB stuff, so I'm not 100% sure).

Exercise your faith!

Re: A table the joins another table twice

I can do the joins in the database, but I was hoping to be able to do it in Rails automagically without using any SQL. 

This is the page I'm trying to rewrite in Rails:
http://wsefcsoftball.org/teamstats.php? … eason=2005

It's currently written in PHP.

Matt

Re: A table the joins another table twice

Have you added your has_many and belongs_to to both your models?

Re: A table the joins another table twice

I've tried the add the belongs_to and has_many lines to the models, but since I am joining to the same table twice, it doesn't seem to work.  It works fine for the fields table since I only join to it once.

I've named the two foreign keys to the teams table team1_id and team2_id.

#I've tried this...

#team.rb
class Team < ActiveRecord::Base
	has_many :games
end

#game.rb
class Game < ActiveRecord::Base
	belongs_to :field
	belongs_to :team
end

#In the view...

#list.rhtml
<html>
	<body>
		<h1>Games</h1>
		<p/>
		<% @games.each do |game| %>
                        <!-- the next line barfs -->
			<%= game.gamedate %> - <%= game.field.name %> - <%= game.team.name %><br/>
		<% end %>
	</body>
</html>

I'm assuming it doesn't know how to join to the team table because it's looking for a team_id field that doesn't exist.

Matt

Re: A table the joins another table twice

I think I've got it figured out.  I created models named Team1 and Team2 and used the set_table_names method.  Then I used the foreign_key method in the game model to manually set the key name.  It seems to work.  Thanks for your help.

Matt

Re: A table the joins another table twice

Glad you got it working Matt.

Exercise your faith!

Re: A table the joins another table twice

Based on my prior experience, you want to do as much on the SQL side of the house as possible, especially if your site will gain any sort of large amount of traffic.

When I setup wowcensus.com, a lot of the calculations were done within the PHP which slowed the site down considerably. Once as much was moved over to the SQL queries as possible, the site sped up enormously.

I can't think of a single instance in which you could process the data within the scripts as well as within SQL, that you would want to choose scripting - from a speed standpoint.

Re: A table the joins another table twice

Hi Matt,

A better way to do this is to define two foreign keys in the field table, like this:

+-----------+
 FIELD 
+-----------+
id         =>  primary key
team1_id   =>  foreign key
team2_id   =>  foreign key

Then, in your field model, set up the relationships like this:

Class Field < ActiveRecord::Base
  has_one :team1, :class_name => "Team", :foreign_key => "team1_id"
  has_one :team2, :class_name => "Team", :foreign_key => "team2_id"
end

Class Team < ActiveRecord::Base
  belongs_to :field
end

If you do it this way, Rails will handle all the SQL joins for you. In your views, you can now use <%= field.team1 %>, <%= field.team2 %>, <%= team1.field %>, and <%= team2.field %>

In response to Evic's concern: Using this method enables Rails to construct these queries using pure SQL. The result of the SQL query is returned to the application as an object, on which you can then do calculations.

Last edited by ryenski (2006-02-27 08:35:16)

Ryan Heneise  |  Art of Mission  |  Now with extra-strong Donor Tools mojo

Re: A table the joins another table twice

You can read more about has_one and belongs_to in the ActiveRecord documentation at api.rubyonrails.com.

Ryan Heneise  |  Art of Mission  |  Now with extra-strong Donor Tools mojo