One of the things that bugged me about Rails when they made the move from only having has_and_belongs_to_many (HABTM) to also including the has_many_through (HM:T) join model option (which is now the preferred way to go), was that you needed to write your own code in the model to get the associations to work properly. With the old HABTM implementation this was handled for you.
Writing the association code yourself required some array math and it was hard to remember what order to do things in and how to exactly format it. I had to copy and paste the code in from a snippet library every time. Not only that but it was ugly and a pain in the butt to maintain, and really felt like it should be handled in the core Rails framework. It looked like this (at least my version):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
|
Here’s another post detailing this requirement, but luckily as of 18 months ago (or longer) Rails no longer makes you manage that association code yourself. While this isn’t news in and of itself, it’s important if you’re working with a legacy app and you try to convert a HABTM to a HM:T and can’t figure out why it’s not working. That’s exactly what happened to me a few weeks ago after digging back into an old codebase. I needed the HM:T implementation though because I needed to add the position column to the join table to allow users to order their locations by the most recently added one. The app I’m working in has users, employers and locations. Users and locations are associated through the locations_users (join table) and locations belong to employers, thus users are associated to employers through the locations_users table.
The problem is that there was a lot of data in my existing HABTM table (locations_users) that I needed to retain and I wasn’t sure how to go about doing this. I’m also using Microsoft SQL Server 2005 with my Ruby on Rails app so that throws another kink in the mix as most google searches will only yield info for MySQL or PostgreSQL. I thought about just modifying the table directly in SQL Management Studio but that was less than desirable because it is hard to automate and hard to document. However, most .NET developers I know use “change scripts” instead of migrations like we do in the Rails world so I thought about how I could get a hybrid solution working.
I modified the table in our staging environment with SQL Management Studio, I added an “id” column, and both “updated_at” and “created_at” timestamp columns and then generated a change script. Note: it’s important that when in design mode that you click the “generate change script” button before saving the table, otherwise if you save the table before generating the change script you can’t click the change script button anymore… it gets grayed out.
Initially I tried just executing the whole change script directly in an execute block inside of self.up, which would look something like this:
1 2 3 4 5 |
|
Unfortunately that didn’t work, but I did manage to figure out a way to still execute the change script incrementally. I just needed a new “execute” call for each SQL statement (which you can see in the final code example at the end of this post). The last thing to do was to try to execute this task with some sort of fail-safe so that I wouldn’t screw up my data if something went wrong. Luckily ActiveRecord supports transactions as long as your database does, and SQLServer 2005 definitely supports them. In fact they’re in the change script as “BEGIN TRANSACTION”, we just can’t execute that code directly in our migration which is why you don’t see them in the final code example.
For more discussion on transactions in Rails look at this stackoverflow thread.
1 2 3 4 |
|
Because we want to retain all the data that exists in the join table and we’re adding an ID col with some timestamps and re-positioning the fields, SQL Server will be creating a tmp table, dumping all our records into that, then renaming the tmp table to locations_users and modifying the ID col to be a true auto-incrementing primary column. Here is the final, transactional Active Record Migration for SQL Server 2005.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 |
|