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