Adam Ballai
2006-Jan-09 21:18 UTC
[Rails] Include with two references of one model of the same table
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
Leon Leslie
2006-Jan-28 22:36 UTC
[Rails] Include with two references of one model of the same table
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>
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060128/f73c31e5/attachment.html
Leon Leslie
2006-Jan-28 23:15 UTC
[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> 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-- First they laugh at you, then they ignore you, then they fight you. Then you win. -- Mahatma Karamchand Gandhi -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060128/2fca3793/attachment-0001.html
Bob Silva
2006-Jan-29 01:12 UTC
[Rails] Include with two references of one model of the same table
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
--
First they laugh at you, then they ignore you, then they fight you.
Then you win.
-- Mahatma Karamchand Gandhi
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://wrath.rubyonrails.org/pipermail/rails/attachments/20060129/a15216e0/attachment-0001.html