Topic: Many-to-Many Migration with Indexes questions

The answer to this question is probably really simple, but I'm fairly new to rails and can't find any detailed information about this subject--not even in the Agile Web Development with Rails Book (2nd Edition).

The questions deal with many-to-many relationships in rails. I've got some database background so the concept of many-to-many isn't foreign to me.

First off, should I create all three tables (categories, products, and categories_products) in a single migration? If so, should I create the migration as "ruby script/generate migration categories_products"? Assuming I'm correct so far, what do I need to do for the category and product models (since Rails didn't auto-create these because I used ruby/script generate migration for 3 tables rather than ruby script/generate model for each part of the many-to-many table)?

On a related noted, I would like to create two indexes for the join table in the same migration as the many-to-many tables:

add_index :categories_products, [:product_id, :category_id]
add_index :categories_products, :category_id

What is needed for the def self.down part of the migration and in what order? I was thinking:

  remove_index :categories_products, [:product_id, :category_id]
  remove_index :categories_products, :category_id
  drop_table :categories
  drop_table :products
  drop_table :categories_products

but I don't think the remove Index parts are right.

One more question: The create_table :categories_features, id => false do |t| doesn't seem to work in my migration file. When I run the migration and examine the tables using Cocoa MySQL, the join table has an id field even though I used id => false. Any ideas?

Almost forgot, I also am trying to create database constraints in this migration file even though Rails doesn't technically need them.

Any and all help will be greatly appreciated. If anyone knows of a good up-to-date tutorial (for Rails 1.2+), please share. Thanks in advance for your help!

Last edited by Mithrill (2007-02-25 11:21:09)

He will return...

Re: Many-to-Many Migration with Indexes questions


1. It's probably a matter of taste, but I feel more comfortable keeping my tables separated in different migration files, really for sanity's sake.

2. As for your join table, I could be wrong but don't think it will work without an ID column. If you are trying to get fancy with the join relationship then you should look into has_many :through as an alternative since this will allow you to define a model for the relationship.

3. Your index code looks fine to me..are you having specific problems here?

4. Foreign Key Constraints require some trickery in Rails, like anything else there are a few ways to skin this cat:

- Simple Simulation in your products model ->

validates_presence_of :category, :message => "category must be specified" 
validates_associated :category

- If you need something with more oomph and are comfortable coding raw SQL, then use the execute command in your migration file:

execute "alter table products
add constraint fk_products_categories 
foreign key (category_id) references categories(id)"

- If all else fails, there is even a Migration Plugin -> … migrations. I havent used it myself but it looks promising

5. A lot of this stuff is actually covered in AWDWR.. I personally enjoy my hardcopy along with PDF for quick searches. All the same I subscribe to several blogs for inspiration. I will post a list on this forum soon.

Re: Many-to-Many Migration with Indexes questions

Ace of Dubs,

First off, thanks for your response. I'm not sure if I asked my question clearly enough. I'm not sure about how to create a migration(s) for a many-to-many relationship. I would like the migration files to create SQL level constraints and indexes as well. I also don't know how to delete the indexes the the down section of the migration. Hopefully, my responses to each of the suggestions below will help define what I'm looking for. I do really appreciate your help.

1. I would be more comfortable creating all of my table in different migration files b/c that makes more sense to me. Since I'm so new to rails, I wasn't sure how to deal with the join table in a migration file. Would I run a migration for it just like the other tables? Also, should I create the join table last because the key of the one goes into the many?

2. I'm almost certain that the join table doesn't need a separate id column because the product_id and category_id columns work together to create a composite key. As my program is now planned, the join table will only be used for as a join and not hold any other data so I probably don't need the cool and fancy has_many :through though it is a good suggestion.

3. I believe the code to add the Index is perfectly fine. It's the code to remove the Index that is giving me problems when I revert back to an older migration version. This could be that I'm defining all of my tables in a single migration file and then their deleted in the wrong order.

4. I am comfortable with raw SQL and have already added the code you suggested to the migration file for extra security. I'll will most likely add the same code you suggested to the model file. Always good to have those extra checks and balances in place.

5. I've been reading AWDWR and it is a great book, I just couldn't find enough info related to creating the migrations for many-to-many relationships. There's plenty of info related to migrations and many-to-many relationships, there just not a full example combining the two together.

Thanks again.

He will return...

Re: Many-to-Many Migration with Indexes questions


1. Here is how I usually do it. I create my product and category models, which also creates the migration files for those Models. In each of the those cooresponding migration files, I create the database schema for it. If I created the category model first then the product model, thus 004_category... 005_product... migration names, I add the join table in the 005_product migration file after the block for creating the product table. e.g.

  create_table :categories do |t|
    t.column :name, :string
    # ...shorten for brevity

  create_table :categories_products, :id => false do |t|
    t.column :product_id,     :integer
    t.column :category_id,   :integer

First, notice that "id" is a symbol. The problem you had is that you declared id => false with "id" not being either a symbol or string.

The join should be declared last.

2. The join table should *not* have an id of its own. There is no point.

3. Your self.up indexes are declared correctly. In your self.down, your definitions should be in backwards order of the self.up. e.g.

  remove_index :categories_products, :category_id
  remove_index :categories_products, [:product_id, :category_id]
  drop_table   :categories

That should cover it. Make any sense? If you want more clarifications on Ruby/Rails, shoot me an email and I'll write a snip on it for Ruby Snips. We're trying to pump out a lot of mini-tutorials/examples.

Re: Many-to-Many Migration with Indexes questions

Thanks a ton Robert! This info is exactly what I was looking for. This should help me get farther along with my first attempt a Rails program--actually my first attempt at an object oriented program. Should be fun.

Ruby Snips is looking nice. I can't wait till it gets built up more.

BTW, I noticed the feed for Ruby Snips was acting a bit strange today. You're probably aware of it and it may be because the site is under development, but I  thought I'd share just in case. A couple of your post showed up twice in my feed reader (currently Google Reader) but not all of them. The two that repeated were URL Based Breadcrumbs and Splatter that array. Soot me an email if you need more info about what I'm seeing.

He will return...

Re: Many-to-Many Migration with Indexes questions

kinda like your double post? lol j/k

Thanks for letting me know. I've got a pretty good idea what is going on and appreciate you letting me know!

Re: Many-to-Many Migration with Indexes questions

Oops!!! lol. Maybe I'm just a double kind of guy today...

As far as my double post, I've been having some Internet connections issues today and had to hit refresh while posting the reply because I thought I lost my wireless connection, guess the first post got through. I've now deleted the extra post.

He will return...