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

The 29 Healthiest Foods on the Planet

The 29 Healthiest Foods on the Planet > The following is a "healthy food hot list" consisting of the 29 food that will give you the biggest nutritional bang for you caloric buck, as well as decrease your risk for deadly illnesses like cancer, diabetes and heart disease.
Read more

Lists and arrays in Dart

(This is Part 3 in a series about Dart. Check out Part 2, Function in Dart .) Warning : We expect the Dart libraries to undergo potentially sweeping changes before Dart goes to alpha. This document is relevant as of 2011-12-22. Intro Dart is a "batteries included" effort to help app developers build modern web apps. An important "battery" is the bundled core Dart libraries, providing common and rich functionality. Dart is building a solution for large, complex web apps, and providing well tested, integrated, and common libraries is key to helping a web app developer be more productive out of the box. The Collection libraries are a crucial set of APIs that Dart developers get for free. Much more than simple arrays and maps, the Collection library includes standard ways to filter, iterate, inspect, compose, and sort your data. This post specifically looks at List<E> , Dart's ordered, indexable collection of objects. Aside: The Dart project is lucky...
Read more

Converting Array to List in Scala

Now, this has to have a built-in somewhere in Scala , because it just seems too common. So, how to convert an Array to a List in Scala? Why do I need this? I needed to drop to Java for some functionality, which in this case returns an Array. I wanted to get that Array into a List to practice my functional programming skillz. **Update**: I figured out how to convert Arrays to Lists the Scala way. Turns out it's a piece of cake. val myList = List.fromArray(Array("one", "two", "three")) or val myList = Array("one","two","three").elements.toList The call to elements returns an Iterator , and from there you can convert to a List via toList . Nice. Because my first version wasn't actually tail recursive, what follows is a true tail recursive solution, if I were to implement this by hand. The above, built in mechanism is much better, though. object ArrayUtil { def toList[a](array: Array[a]): List[a] = { d...
Read more