http://www.developer.com/

Back to article

Managing MySQL Table Relations with Rails' Active Record Association Types


October 9, 2007

In last week's article, I showed you how Rails' powerful migrations feature eliminates the long-standing chasm separating the way databases and code are maintained by offering a way to programatically manage table structures. Combined with Rails' Active Record implementation, programmers are able to more effectively integrate databases into applications because the need to switch from "programmer" mode to "database administrator" mode has been practically eliminated.

In this installment, I'll delve deeper these features, showing you how Active Record can help you to manage table relations more effectively than ever. By using association types and a simple way to assign foreign keys to other tables, you can automate what is otherwise often a tedious and confusing process when creating table relationships. Along the way, I'll introduce a variety of other database concepts as applied to Rails, which should be of great help if you're new to Rails and are trying to figure out how to create a simple database-driven Rails application.

As always, the discussion will be based around a real-world theme, namely a web-based address book. For purposes of demonstration, the database will consist of just two tables: contacts and states. The contacts table will contain the names, email addresses, cities, and states of your respective contacts. Because you don't want to encounter a data corruption problem by potentially entering an incorrect two-letter state abbreviation, you'll populate the states table with a list of allowable abbreviations, and force the user to choose from that list by way of a drop-down list.

To begin, go ahead and create the new Rails project:

%>rails addressbook

Creating the Models

Next, you'll need to create the application models, which will also configure the migration skeletons. Only two models are needed: Contact and State.

%>ruby script/generate model Contact
   exists  app/models/
   exists  test/unit/
   exists  test/fixtures/
   create  app/models/contact.rb
   create  test/unit/contact_test.rb
   create  test/fixtures/contacts.yml
   create  db/migrate
   create  db/migrate/001_create_contacts.rb
%>ruby script/generate model State
   exists  app/models/
   exists  test/unit/
   exists  test/fixtures/
   create  app/models/state.rb
   create  test/unit/state_test.rb
   create  test/fixtures/states.yml
   exists  db/migrate
   create  db/migrate/002_create_states.rb

Creating the Migrations

Next, you'll need to use the migrations feature as introduced in the last article to create the two table schemas. I won't go into details regarding how to execute these migrations (because the concept was introduced in the aforementioned tutorial), and instead just show you the migration scripts for 001_create_contacts.rb and 002_create_states.rb:

001_create_contacts.rb

class CreateContacts > ActiveRecord::Migration
   def self.up
      create_table :contacts do |table|
         table.column :name,  :string, :limit => 100,
                      :null => false
         table.column :email, :string, :limit => 100,
                      :null => false
         table.column :city,  :string, :limit => 100,
                      :null => false
         table.column :state_id, :integer, :null => false
      end
   end

   def self.down
      drop_table :contacts
   end
end

Each of the contacts columns should be self-explanatory, except perhaps for state_id. You'll use <state_id> to map each contact to a corresponding state found in the states table. How this is done with Active Record will be explained later in the tutorial.

002_create_states.rb

class CreateStates < ActiveRecord::Migration
   def self.up
      create_table :states do |table|
         table.column :abbreviation, :string, :limit => 2,
                      :null => false
      end
   end

   def self.down
      drop_table :states
   end
end

Go ahead and execute these migrations, and you should see the following table schemas in your MySQL database:

%>DESCRIBE contacts;
+----------+--------------+------+-----+----------+----------------+
| Field    | Type         | Null | Key | Default  | Extra          |
+----------+--------------+------+-----+----------+----------------+
| id       | int(11)      | NO   | PRI | NULL     | auto_increment |
| name     | varchar(100) | NO   |     |          |                |
| email    | varchar(100) | NO   |     |          |                |
| city     | varchar(100) | NO   |     |          |                |
| state_id | int(11)      | NO   |     |          |                |
+----------+--------------+------+-----+----------+----------------+

%>DESCRIBE states;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| abbreviation | varchar(2) | NO   |     |         |                |
+--------------+------------+------+-----+---------+----------------+

Next, you'll need to populate the states table with the desired data. This is easily done by using the Rails console:

%>ruby script/console
Loading development environment.
>>State.new(:abbreviation => "AK").save
=> true
>>State.new(:abbreviation => "CA").save
=> true
>>State.new(:abbreviation => "DC").save
=> true
>>State.new(:abbreviation => "OH").save
=> true
>>State.new(:abbreviation => "NY").save
=> true
>>State.new(:abbreviation => "TX").save
=> true

Personally, I find Ruby's object-oriented syntax coupled with Active Record's ORM (object-relational mapping) feature to be not only far less error-prone than opening up a MySQL client and executing SQL queries, but also much faster to execute than using a graphical interface such as PHPMyAdmin.

If you review the contents of the states table, you'll see the data has indeed been inserted:

mysql> select * from states;
+----+--------------+
| id | abbreviation |
+----+--------------+
|  1 | AK           |
|  2 | CA           |
|  3 | DC           |
|  4 | OH           |
|  5 | NY           |
|  6 | TX           |
+----+--------------+
6 rows in set (0.00 sec)

Creating Table Relations

To create the many-to-one relationship between the contacts' state identifier and one of the entries found in the states table (many contacts can live in one state), you'll need to modify the contact.rb file created when the Contact model was generated. This is done with the belongs_to association. To enforce this association, open the contact.rb model and modify it to read like this:

class Contact < ActiveRecord::Base
   belongs_to :state
end

Believe it or not, that's it! To view the relationship feature in action, log in to the Rails console again.

Testing Out the Relation

%>ruby script/console
Loading development environment.

Next, create a new contact, passing along 4 as the state_id (which maps to Ohio in the states table. Of course, in an actual web application the user would likely be provided the list of state abbreviations by way of a drop-down list:

>> contact = Contact.new
>> contact.name = "Jason Gilmore"
=> "Jason Gilmore"
>> contact.email = "jason@example.com"
=> "jason@example.com"
>> contact.city = "Columbus"
=> "Columbus"
>> contact.state_id = "4"
=> "4"
>> contact.save
=> true

And now comes the magic. Because this is the first contact, it will logically have a primary key identifier of 1. Therefore, use 1 to find that contact, and look up his state abbreviation simply by appending state.abbreviation:

>> Contact.find(1).state.abbreviation
=> "OH"

Voilà!!! You're on your way towards taking advantage of table relations, without having to write a single SQL query!

Other Supported Association Types

The belongs_to association is just one of the many association types supported by Rails. Here's a summary of the other commonly used types at your disposal:

has_many

The has_many association type is used when you want to associate a particular table row to potentially many rows in another table. For example, a cooking web site might allow registered users to maintain an personal recipes repository, meaning each user could map to many recipes.

has_and_belongs_to_many

The has_and_belongs_to_many association type is used when you want to associate a row in table A with potentially many rows in table B; however those rows in table B could also potentially be associated with other rows in table A. For instance, a member of a book enthusiast group would likely own numerous books, but that doesn't prevent other members from also owning those very same books.

has_many :through

The has_many :through association type is used when a somewhat more complex many-to-many relationship is required. This concept is best explained with an example. Suppose a baseball team management application maintains a list of players that the team is willing to sell. However, the selling price may vary according to the third-party team attempting to make the acquisition. Therefore, to determine the player's selling price according to team, you would use a has_many :through association, which allows an intermediate table to connect the three key pieces of data: player, team, and price.

Conclusion

Active Record's association types can greatly reduce the coding complexity otherwise required when creating database-driven Rails applications. I hope this article helps you understand how you can begin taking advantage of this powerful feature!

About the Author

W. Jason Gilmore is Apress' Open Source Editor, and co-founder of IT Enlightenment. He's the author of several books, including the best-selling "Beginning PHP and MySQL 5: Novice to Professional, Second Edition" (Apress, 2006. 913pp.). Jason loves receiving email, so don't hesitate to write him at wjATwjgilmore.com.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date