Include with two references of one model of the same table

Rails
1

Hey guys,

I just came across this oddity, not sure what to make of it yet, but

I think it might be incorrect behavior. When doing a @inst.find(:all,
:include => [“hometeam”, “awayteam”] …) hometeam and awayteam are two
references from a belongs_to that is of the same model and of the same
table. I will get a pgerror stating that “matches”, which is the table
that these fk’s loop back to is being ambiguously referenced. The SQL
spit out showed only one select on “matches” where I think should be two
selects on two aliases of “matches”.

Heres some code below to better understand what I’m talking about.

class Match < ActiveRecord::Base
belongs_to :hometeam, :class_name => “TeamsSeason”, :foreign_key =>
“hometeam_id”
belongs_to :awayteam, :class_name => “TeamsSeason”, :foreign_key =>
“awayteam_id”
end

class TeamsSeason < ActiveRecord::Base
has_many :hometeam, :class_name => “Match”, :foreign_key =>
“hometeam_id”, :dependent => :destroy
has_many :awayteam, :class_name => “Match”, :foreign_key =>
“awayteam_id”, :dependent => :destroy

def self.find_all_played id
find(:all, :include => [“hometeam”, “awayteam”])
end
end

Heres a sample of the error…

|PGError: ERROR: table name “matches” specified more than once
: SELECT matches.“awayteam_goals” AS t2_r1, matches.“season_id” AS
t1_r7, teams_seasons.“division_id” AS t0_r6, matches.“hometeam_goals” AS
t2_r2, matches.“league_id” AS t1_r8, matches.“awayteam_id” AS t2_r3,
matches.“venue_id” AS t1_r9, matches.“hometeam_id” AS t2_r4,
matches.“matchtime_start” AS t2_r5, matches.“id” AS t1_r0,
matches.“matchtime_stop” AS t2_r6, matches.“awayteam_goals” AS t1_r1,
teams_seasons.“id” AS t0_r0, matches.“season_id” AS t2_r7,
matches.“hometeam_goals” AS t1_r2, teams_seasons.“team_id” AS t0_r1,
matches.“league_id” AS t2_r8, matches.“awayteam_id” AS t1_r3,
teams_seasons.“season_id” AS t0_r2, matches.“venue_id” AS t2_r9,
matches.“division_id” AS t1_r10, matches.“hometeam_id” AS t1_r4,
teams_seasons.“coach_id” AS t0_r3, matches.“division_id” AS t2_r10,
matches.“id” AS t2_r0, matches.“played” AS t1_r11,
matches.“matchtime_start” AS t1_r5, teams_seasons.“manager_id” AS t0_r4,
matches.“played” AS t2_r11, matches.“matchtime_stop” AS t1_r6,
teams_seasons.“league_id” AS t0_r5 FROM teams_seasons LEFT OUTER JOIN
matches ON matches.hometeam_id = teams_seasons.id LEFT OUTER JOIN
matches ON matches.awayteam_id = teams_seasons.id WHERE
(teams_seasons.division_id = 1) |

I’d like to know if this is an incorrect implementation detail or just a
don’t_do_that problem =)

–

Adam B. [email protected]
Integrum Technologies, LLC
Phone: +1 602 792 1270
Mobile: +1 480 580 7091

2

I am having the same probem…

My code looks like this

class Flight < ActiveRecord::Base
belongs_to :airport_of_orgin,
:class_name => “Airport”, :foreign_key => “orignal_airport_id”
belongs_to :airport_of_destine,
:class_name => “Airport”, :foreign_key => “destination_airport_id”
end

Is there any method to solve is problem?

regards,
Leon.

3

Chad F. on irc suggest to use :joins to solve the problemm…

using joins…

look out for his book http://www.pragmaticprogrammer.com/titles/fr_rr/

regards,
Leon…

4

Yes, doing the joins manually is the best solution right now until AR
figures out how to detect identical tables and alias them automatically.

Bob S.

http://www.railtie.net/

From: [email protected]
[mailto:[email protected]] On Behalf Of Leon L.
Sent: Saturday, January 28, 2006 3:15 PM
To: [email protected]
Subject: Re: [Rails] Include with two references of one model of the
same
table

Chad F. on irc suggest to use :joins to solve the problemm…

using joins…

look out for his book http://www.pragmaticprogrammer.com/titles/fr_rr/

regards,
Leon…

On 1/28/06, Leon L. [email protected] wrote:

I am having the same probem…

My code looks like this

class Flight < ActiveRecord::Base
belongs_to :airport_of_orgin,
:class_name => “Airport”, :foreign_key => “orignal_airport_id”
belongs_to :airport_of_destine,
:class_name => “Airport”, :foreign_key => “destination_airport_id”
end

Is there any method to solve is problem?

regards,
Leon.

On 1/9/06, Adam B. < [email protected]
mailto:[email protected] > wrote:

Hey guys,

I just came across this oddity, not sure what to make of it yet, but

I think it might be incorrect behavior. When doing a @inst.find(:all,
:include => [“hometeam”, “awayteam”] …) hometeam and awayteam are two
references from a belongs_to that is of the same model and of the same
table. I will get a pgerror stating that “matches”, which is the table
that these fk’s loop back to is being ambiguously referenced. The SQL
spit out showed only one select on “matches” where I think should be two
selects on two aliases of “matches”.

Heres some code below to better understand what I’m talking about.

class Match < ActiveRecord::Base
belongs_to :hometeam, :class_name => “TeamsSeason”, :foreign_key =>
“hometeam_id”
belongs_to :awayteam, :class_name => “TeamsSeason”, :foreign_key =>
“awayteam_id”
end

class TeamsSeason < ActiveRecord::Base
has_many :hometeam, :class_name => “Match”, :foreign_key =>
“hometeam_id”, :dependent => :destroy
has_many :awayteam, :class_name => “Match”, :foreign_key =>
“awayteam_id”, :dependent => :destroy

def self.find_all_played id
find(:all, :include => [“hometeam”, “awayteam”])
end
end

Heres a sample of the error…

|PGError: ERROR: table name “matches” specified more than once
: SELECT matches.“awayteam_goals” AS t2_r1, matches.“season_id” AS
t1_r7,
teams_seasons.“division_id” AS t0_r6, matches.“hometeam_goals” AS t2_r2,
matches.“league_id” AS t1_r8, matches.“awayteam_id” AS t2_r3,
matches.“venue_id” AS t1_r9, matches.“hometeam_id” AS t2_r4,
matches.“matchtime_start” AS t2_r5, matches.“id” AS t1_r0,
matches.“matchtime_stop” AS t2_r6, matches.“awayteam_goals” AS t1_r1,
teams_seasons.“id” AS t0_r0, matches.“season_id” AS t2_r7,
matches.“hometeam_goals” AS t1_r2, teams_seasons.“team_id” AS t0_r1,
matches.“league_id” AS t2_r8, matches.“awayteam_id” AS t1_r3,
teams_seasons.“season_id” AS t0_r2, matches.“venue_id” AS t2_r9,
matches.“division_id” AS t1_r10, matches.“hometeam_id” AS t1_r4,
teams_seasons.“coach_id” AS t0_r3, matches.“division_id” AS t2_r10,
matches.“id” AS t2_r0, matches.“played” AS t1_r11,
matches.“matchtime_start”
AS t1_r5, teams_seasons.“manager_id” AS t0_r4, matches.“played” AS
t2_r11,
matches.“matchtime_stop” AS t1_r6, teams_seasons.“league_id” AS t0_r5
FROM
teams_seasons LEFT OUTER JOIN matches ON matches.hometeam_id =
teams_seasons.id LEFT OUTER JOIN matches ON matches.awayteam_id =
teams_seasons.id WHERE (teams_seasons.division_id = 1) |

I’d like to know if this is an incorrect implementation detail or just a
don’t_do_that problem =)

–

Adam B. <[email protected] >
Integrum Technologies, LLC
Phone: +1 602 792 1270
Mobile: +1 480 580 7091

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

–
First they laugh at you, then they ignore you, then they fight you.
Then you win.
– Mahatma Karamchand Gandhi