Rails - Jan 2006 - suggest for "ambiguous column" when JOIN associated tables

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

-- 
Posted via http://www.ruby-forum.com/.

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

On Jan 22, 2006, at 11:14 AM, Stas Linke wrote:
> 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

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