Forum: Ruby on Rails suggest for "ambiguous column" when JOIN associated tables

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.
Stas Linke (Guest)
on 2006-01-22 17:14
Today I face with incorrect behavior in ActiveRecord.

It take place when I try to use :include parameter for .find method.

Where are two typical cases:


1. You have record with self-referential joins

CREATE TABLE keywords (id, group_id);

class Keyword < ActiveRecord::Base
  belongs_to :group,
             :class_name =>  "Keyword",
             :foreign_key => "group_id"
end


2. Your record has two associations with the same table

CREATE TABLE artworks (id, author_id, agent_id);

class Artwork < ActiveRecord::Base
  belongs_to :author,
             :class_name =>  "Person",
             :foreign_key => "author_id"

  belongs_to :agent,
             :class_name =>  "Person",
             :foreign_key => "agent_id"
end


In both cases when you try to use :include parameter for the .find
method,
ActiveRecord generates incorrect query which brings to SQL error
"ambiguous column name".

So, for the fist case (self-refferal join), ActiveRecord will generate
this query:

SELECT keywords.id AS t1_r0, keywords.id AS t0_r0, keywords.group_id AS
t1_r2, keywords.group_id AS t0_r2, FROM keywords LEFT OUTER JOIN
keywords ON keywords.id = keywords.group_id;


My point is to use AS statement to refer JOIN'ed tables as aliases, so
we can produce query like:

SELECT keywords_t1.id AS t1_r0, keywords.id AS t0_r0,
keywords_t1.group_id AS t1_r2, keywords.group_id AS t0_r2 FROM keywords
LEFT OUTER JOIN keywords AS keywords_t1 ON keywords_t1.id =
keywords.group_id;

And all will be okay.

Unfortunately, I can't propose this idea as a patch, due my lack of
experience.

Hope ActiveRecord developers will implement it.

--
  Stas Linke
Flinn Mueller (Guest)
on 2006-01-22 20:10
(Received via mailing list)
For case 1, can't you use acts_as_tree?

Case #2 would be a nice addition, it would require a really smart
patch.  You would need to use the type attribute to determine if
there is a type, then make an alias in every single query generated.
The problem is that when the query is generated you need to determine
the type and make an alias accordingly...then when associations.rb
extracts the record it is looking for a table/columns to load the
record into the associated class.  In a number of methods in between
you would need to modify table_name references and column name
references as well.  You'd have to use the type as the alias so that
you can refer to it in :conditions.  The whole thing is a big harry
feature request that I wouldn't ask for unless I could provide a
patch for ;-)

--flinn
Rick Olson (Guest)
on 2006-01-22 20:55
(Received via mailing list)
On 1/22/06, Stas Linke <staslinke@gmail.com> wrote:
>
> Today I face with incorrect behavior in ActiveRecord.
>
> It take place when I try to use :include parameter for .find method.

I added a patch.  Please try it and let me know:

http://dev.rubyonrails.org/ticket/3574

--
Rick Olson
http://techno-weenie.net
This topic is locked and can not be replied to.