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  #This is from an old app that managed a relationship between MetaKeywords and Pages
   2  
   3  def manage_meta_keyword_associations(new_meta_keywords)
   4      current_meta_keywords = MetaKeywordPage.find_all_by_page_id(self.id).collect{|mkp| mkp.meta_keyword_id.to_s}
   5      
   6      meta_keywords_to_add = new_meta_keywords - current_meta_keywords
   7      for meta_keyword in meta_keywords_to_add
   8        MetaKeywordPage.create(:meta_keyword_id => meta_keyword, :page_id => self.id) 
   9      end
  10      
  11      meta_keywords_to_remove = current_meta_keywords - new_meta_keywords
  12      for meta_keyword in meta_keywords_to_remove
  13        MetaKeywordPage.find_by_meta_keyword_id_and_page_id(meta_keyword, self.id).destroy
  14      end
  15    end

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  class ModifyLocationsUsersToBeHmt < ActiveRecord::Migration
   2    def self.up
   3      execute "change script here"
   4    end
   5  end

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  #A transaction in Active Record
   2  ActiveRecord::Base.transaction do
   3    execute "update users set enabled = 1 where name like 'batman'"
   4  end

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  class ModifyLocationsUsersToBeHmt < ActiveRecord::Migration
   2    def self.up
   3      ActiveRecord::Base.transaction do
   4        execute "
   5        CREATE TABLE dbo.Tmp_locations_users
   6          (
   7          id int NOT NULL IDENTITY (1, 1),
   8          location_id int NOT NULL,
   9          user_id int NOT NULL,
  10          created_at datetime NULL,
  11          updated_at datetime NULL
  12          ) ON [PRIMARY]
  13        "
  14        execute "SET IDENTITY_INSERT dbo.Tmp_locations_users OFF"
  15      
  16        execute "IF EXISTS(SELECT * FROM dbo.locations_users)
  17           EXEC('INSERT INTO dbo.Tmp_locations_users (location_id, user_id)
  18            SELECT location_id, user_id FROM dbo.locations_users WITH (HOLDLOCK TABLOCKX)')"
  19        execute "DROP TABLE dbo.locations_users"
  20        execute "EXECUTE sp_rename N'dbo.Tmp_locations_users', N'locations_users', 'OBJECT'"
  21        
  22        execute"ALTER TABLE dbo.locations_users ADD CONSTRAINT
  23          PK_locations_users PRIMARY KEY CLUSTERED 
  24          (
  25          id
  26          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"
  27      
  28        
  29        execute "CREATE NONCLUSTERED INDEX index_locations_users_on_location_id ON dbo.locations_users
  30          (
  31          location_id
  32          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]"
  33      
  34        execute = "CREATE NONCLUSTERED INDEX index_locations_users_on_user_id ON dbo.locations_users
  35          (
  36          user_id
  37          ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  38        "
  39      
  40        execute "update locations_users set created_at = '1-1-2010'"
  41        execute "update locations_users set updated_at = '1-1-2010'"
  42        
  43        add_column :locations_users, :position, :integer
  44      end
  45    end
  46  
  47    def self.down
  48      ActiveRecord::Base.transaction do
  49        remove_column :locations_users, :position
  50        remove_column :locations_users, :id
  51        remove_column :locations_users, :created_at
  52        remove_column :locations_users, :updated_at
  53      end
  54    end
  55  end