October 30, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Managing MySQL Table Relations with Rails' Active Record Association Types

  • October 9, 2007
  • By Jason Gilmore
  • Send Email »
  • More Articles »

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.





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel