June 15th, 2005

Storing additional data on join tables with Rails

5 comments on 480 words

While working on my “something big soon to come”:http://weyoume.us (more on this later) , I had the need to store more than just two object id’s on a join table, namley the time in which the join was created.

I added a created_on “magic field name”:http://wiki.rubyonrails.com/rails/show/MagicFieldNames only to find out they weren’t so magic on join tables. Rails will not manage these fields for you.

It was suggested in “#rubyonrails”:irc://irc.freenode.org/rubyonrails to create a full blown model to represent my join table. This seemed a bit overkill for my purpose, after all I only wanted to add a single datetime column on the join table.

It turns out that ActiveRecord has this functionality built in– and it doesn’t require creating a new Model for the join table.

If you have a hasandbelongsto_many relationship between two objects, you can push additional attributes into the join table with “pushwith_attributes”:http://api.rubyonrails.com/classes/ActiveRecord/Associations/ClassMethods.html#M000436.

From the api docs:

collection.push_with_attributes(object, join_attributes) - adds one to the collection by creating an association in the join table that also holds the attributes from join_attributes (should be a hash with the column names as keys). This can be used to have additional attributes on the join, which will be injected into the associated objects when they are retrieved through the collection. (collection.concat_with_attributes is an alias to this method).

Seems pretty obvious now, but wasn’t so obvious earlier.

Here is a basic example:


class Post < ActiveRecord::Base
    has_and_belongs_to_many :users
end
class User < ActiveRecord::Base
  has_and_belongs_to_many :posts
end

And in your controller


 @user = User.find(@session['user'].id.to_i)
      @post.users.push_with_attributes(@user, :created_on => Time.now)

Well, thats that.

Discussion

  1. Justin Palmer Justin Palmer said on June 20th

    Unfortunately this will require custom sql. There isn’t an elegant solution that I’m aware of.

    You have to use find_by_sql and explicitly select the specified column or all columns from the join table.

    The find method doesn’t support selecting additional fields on the join table but there is a ticket in place for this.

    I’ve seen methods where you can do something like this, but this doesn’t look to elegant to me:

    @ad.breaks[ 0 ].custom_field

    Remove the spaces around the [], Textpattern does funny things.

  2. Michael Smedberg Michael Smedberg said on October 12th

    I also want to do updates to existing HABTM rows. Here’s what I wrote to do it:

    module ActiveRecord end

    module Associations    
  class HasAndBelongsToManyAssociation
    def update_attributes(record, join_attributes = {})
      if record.is_a? ActiveRecord::Base
        record_id = record.id
      else
        record_id = record
      end
      cols, vals = [], []
      join_attributes.each do | key, val |
        cols << key.to_s
        vals << val
      end
      col_string = cols.join(' = ?, ')
      @owner.connection().update(sanitize_sql(["UPDATE #{@join_table} SET #{col_string} = ? WHERE #{@association_class_primary_key_name} = ? AND #{@association_foreign_key} = ?", vals, @owner.id, record_id].flatten), "Update Attributes")
    end   
  end
end

    I put this into my application_helper.rb, and now I can update records without writing SQL every time. NOTE: I just wrote this today, so it’s NOT heavily tested (or really tested at all)- use at your own risk!

  3. Josh Josh said on January 31st

    Great idea! But it gives me a “Attempted to update a stale object” error…

    m.disc_jockeys.find(:first).update_attributes(:status => ‘PIPAPOs’)

    Can you give me a hint? joshmy-mail.ch

    Btw. you should prevent your blog from spamming…

  4. Josh Josh said on January 31st

    Hrm the e-mail address above is wrong. Correct is:

    josh at my-mail dot ch

  5. Wihic Wihic said on May 24th

    I love it!

Sorry, comments are closed for this article.