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

  1. 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 L.

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 :wink:

–flinn

On 1/22/06, Stas L. [email protected] 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 O.
http://techno-weenie.net