Rails, Legacy Schemas, ActiveRecord, and has_and_belongs_to_many


There turns out to be an issue with legacy schemas and ActiveRecord, the ORM layer of Rails when using has_and_belongs_to_many.



Note: This issue might be Oracle specific.


If the join table itself has an id column, and it has the same name as the id column from the association table, then it will override the id from the association table.


This means that instances from the association table will have the wrong id.



The solution looks to be the new :finder_sql parameter of the has_and_belongs_to_many relationship. With :finder_sql, you can override the generated SQL.



How to use :finder_sql? Try:



has_and_belongs_to_many :ksaas, :join_table => "tbl_ksaa_objective",
:foreign_key => "objective_cid", :association_foreign_key => "ksaa_cid",
:finder_sql => "SELECT TBL_KSAA.cid, TBL_KSAA.ksaa_type, TBL_KSAA.ksaa_item, " +
"TBL_KSAA.KSAA_TYPEID, TBL_KSAA.SAMPLE_BEHAVIOR " +
"FROM TBL_KSAA LEFT JOIN TBL_KSAA_OBJECTIVE ON " +
"TBL_KSAA.cid = TBL_KSAA_OBJECTIVE.ksaa_cid WHERE " +
'(TBL_KSAA_OBJECTIVE.objective_cid = #{id} )'



Keep those single quotes in order to lazily interpret the id in the query.



Note that Hibernate does not have this problem, as it aliases all column names, even when not using joins. Therefore it doesn't have the column naming conflicts.

Popular posts from this blog

Lists and arrays in Dart

Converting Array to List in Scala

Null-aware operators in Dart