Forum: Ruby on Rails Include with two references of one model of the same table

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ffecaee0d49f054bf7653c5f89228c7f?d=identicon&s=25 Adam Ballai (Guest)
on 2006-01-09 22:18
(Received via mailing list)
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 Ballai <adam.ballai@integrumtech.com>
Integrum Technologies, LLC
Phone: +1 602 792 1270
Mobile: +1 480 580 7091
2073c7e0e7d2311727b680a887a375be?d=identicon&s=25 Leon Leslie (Guest)
on 2006-01-28 23:36
(Received via mailing list)
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.
2073c7e0e7d2311727b680a887a375be?d=identicon&s=25 Leon Leslie (Guest)
on 2006-01-29 00:16
(Received via mailing list)
Chad Fowler 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..
Af93ba6b6b59f22a8f37e8de5702ef98?d=identicon&s=25 Bob Silva (Guest)
on 2006-01-29 02:13
(Received via mailing list)
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 Silva

http://www.railtie.net/

  _____

From: rails-bounces@lists.rubyonrails.org
[mailto:rails-bounces@lists.rubyonrails.org] On Behalf Of Leon Leslie
Sent: Saturday, January 28, 2006 3:15 PM
To: rails@lists.rubyonrails.org
Subject: Re: [Rails] Include with two references of one model of the
same
table



Chad Fowler 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 Leslie <leonleslie@gmail.com> 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 Ballai < adam.ballai@integrumtech.com
<mailto:adam.ballai@integrumtech.com> > 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 Ballai <adam.ballai@integrumtech.com >
Integrum Technologies, LLC
Phone: +1 602 792 1270
Mobile: +1 480 580 7091


_______________________________________________
Rails mailing list
Rails@lists.rubyonrails.org
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
This topic is locked and can not be replied to.