b.kocik

I spent all day today discovering something that’s both useful, and frustrating. To put it shortly, having a primary key on the join table for your habtm relationship will cause you problems. But having an :id field that isn’t a primary key does something useful. Want details?

I was trying to figure out how, even though my join table only defined a relationship between two records, ActiveRecord appeared to know which direction that relationship was created in. I have two classes, Product and Language, which have a habtm relationship with one another. If I take a Product p, and a Language l, and do first “p.languages << l” and then “l.destroy”, the row in the join table holding this association survives. But, if I do “p.destroy”, the association goes away with p. It makes sense - but how the heck did ActiveRecord know that the relationship had been declared as “p having l” instead of the other way around? For the answer, follow this bouncing ball:

We begin with a language whose ID in the database is 1, and a product whose ID is 2 (just so we can tell them apart). When I do “p.languages << l”, Rails does this in the database:

INSERT INTO languages_products (`product_id`, `id`, `language_id`) VALUES (2, 1, 1)

The color coding is there for a reason, and is a big hint as to what’s going on. Now, when I do “l.products << p”, watch what Rails does:

INSERT INTO languages_products (`product_id`, `id`, `language_id`) VALUES (2, 2, 1)

Do you see the trick? Watch what happens when I execute “p.languages.delete(l)”:

DELETE FROM languages_products WHERE product_id = 2 AND language_id IN (1)

. . . and when I run “l.products.delete(p)”:

DELETE FROM languages_products WHERE language_id = 1 AND product_id IN (2)

If you haven’t figured it out yet, what Rails is doing is cleverly using the id field in the languages_products table to tell it which side of the relationship holds the reference to the other side; in other words, which is acting as the “belongs_to”. Once it knows that, it knows whether or not it should delete the relationship. (Well, it doesn’t really “know” any of this - it’s using clever SQL to make it happen automatically).

Pretty slick if you ask me. But then it occurred to me to wonder - if ActiveRecord is (ab)using the id field to track the side of the relationship that holds the reference, what would happen if you tried to, for example, state that two products had a particular single language in their collection? The answer is, not surprisingly, this:

Mysql::Error: #23000Duplicate entry ‘2′ for key 1: INSERT INTO languages_products (`product_id`, `id`, `language_id`) VALUES (3, 2, 2)

Since the id field on the join table is a primary key, you get a collision. So you have two options - you can either remove that field altogether (in which case all that smartness about the relationships goes away, which can be desirable if you want the relationship to be destroyed regardless of which side goes away), or you can include it, but not allow it to be a primary key. Remember that migrations create a primary key by default, and you have to explicitly tell it not to when you create your join table. In the case of Products and Languages:

create_table :languages_products, :id => false do |t|

This entry was posted on Thursday, July 26th, 2007 at 2:22 pm and is filed under Ruby and Rails. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.