Recipe 13.11. Using Object Relational Mapping with ActiveRecord
Problem
You want to store data in a database without having to use SQL to access it.
Solution
Use the ActiveRecord library, available as the activerecord gem. It automatically defines Ruby classes that access the contents of database tables.
As an example, let's create two tables in the MySQL database cookbook (see the chapter introduction for more on creating the database itself). The blog_posts table, defined below in SQL, models a simple weblog containing a number of posts. Each blog post can have a number of comments, so we also define a comments table.
use cookbook;
DROP TABLE IF EXISTS blog_posts;
CREATE TABLE blog_posts (
id INT(11) NOT NULL AUTO_INCREMENT,
title VARCHAR(200),
content TEXT,
PRIMARY KEY (id)
) ENGINE=InnoDB;
DROP TABLE IF EXISTS comments;
CREATE TABLE comments (
id INT(11) NOT NULL AUTO_INCREMENT,
blog_post_id INT(11),
author VARCHAR(200),
content TEXT,
PRIMARY KEY (id)
) ENGINE=InnoDB;
Here are two Ruby classes to represent those tables, and the relationship between them:
require 'cookbook_dbconnect'
activerecord_connect # See chapter introduction
class BlogPost < ActiveRecord::Base
has_many :comments
end
class Comment < ActiveRecord::Base
belongs_to :blog_post
end
Now you can create entries in the tables without writing any SQL:
post = BlogPost.create(:title => 'First post',
:content => "Here are some pictures of our iguana.")
comment = Comment.create(:blog_post => post, :author => 'Alice',
:content => "That's one cute iguana!")
post.comments.create(:author => 'Bob', :content => 'Thank you, Alice!')
You can also query the tables, relate blog posts to their comments, and relate comments back to their blog posts:
blog_post = BlogPost.find(:first)
puts %{#{blog_post.comments.size} comments for "#{blog_post.title}"}
# 2 comments for "First post"
blog_post.comments.each do |comment|
puts "Comment author: #{comment.author}"
puts "Comment: #{comment.content}"
end
# Comment author: Alice
# Comment: That's one cute iguana!
# Comment author: Bob
# Comment: Thank you, Alice!
first_comment = Comment.find(:first)
puts %{The first comment was made on "#{first_comment.blog_post.title}"}
# The first comment was made on "First post"
Discussion
ActiveRecord uses naming conventions, database introspection, and metaprogramming to hide much of the work involved in defining a Ruby class that corresponds to a database table. All you have to do is define the classes (BlogPost and Comment, in our example) and the relationships between them (BlogPost has_many :comments, Comment belongs_to :blog_post).
Our tables are designed to fit ActiveRecord's conventions about table and field names. The table names are lowercase, pluralized noun phrases, with underscores separating the words. The table names blog_posts and comments correspond to the Ruby classes BlogPost and Comment.
Also notice that each table has an autoincremented id field named id. This is a convention defined by ActiveRecord. Foreign key references are also named by convention: blog_post_id refers to the id field of the blog_posts table. It's possible to change ActiveRecord's assumptions about naming, but it's simpler to just design your tables to fit the default assumptions.
For "normal" columns, the ones that don't participate in relationships with other tables, you don't need to do anything special. ActiveRecord examines the database tables themselves to find out which columns are available. This is how we were able to use accessor methods for blog_posts.title without explicitly defining them: we defined them in the database, and ActiveRecord picked them up.
Relationships between tables are defined within Ruby code, using decorator methods. Again, naming conventions simplify the work. The call to the has_many decorator in the BlogPost definition creates a one-to-many relationship between blog posts and comments. You can then call BlogPost#comments to get an array full of comments for a particular post. The call to belongs_to in the Comment definition creates the same relationship in reverse.
There are two more decorator methods that describe relationships between tables. One of them is the has_one association, which is rarely used: if there's a one-to-one relationship between the rows in two tables, then you should probably just merge the tables.
The other decorator is has_and_belongs_to_many, which lets you join two different tables with an intermediate join table. This lets you create many-to-many relationships, common in (to take one example) permissioning systems.
For an example of has_and_belongs_to_many, let's make our blog a collaborative effort. We'll add an users table to contain the posts' authors' names, and fix it so that each blog post can have multiple authors. Of course, each author can also contribute to multiple posts, so we've got a many-to-many relationship between users and blog posts.
use cookbook;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INT(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(200),
PRIMARY KEY (id)
) ENGINE=InnoDB;
Because a blog post can have multiple authors, we can't just add an author_id field to the blog_posts table. That would only give us space for a single author per blog post. Instead, we create a join table that maps authors to blog posts.
use cookbook;
DROP TABLE IF EXISTS blog_posts_users;
CREATE TABLE blog_posts_users (
blog_post_id INT(11),
user_id INT(11)
) ENGINE=InnoDB;
Here's another naming convention. ActiveRecord expects you to name a join table with the names of the tables that it joins, concatenated together with underscores. It expects the table names to be in alphabetical order (in this case, the blog_posts table comes before the users table).
Now we can create a User class that mirrors the users table, and modify the BlogPost class to reflect its new relationship with users:
class User < ActiveRecord::Base
has_and_belongs_to_many :blog_posts
end
class BlogPost < ActiveRecord::Base
has_and_belongs_to_many :authors, :class_name => 'User'
has_many :comments, :dependent => true
end
The has_and_belongs_to_many decorator method defines methods that navigate the join table. We specify the :class_name argument because otherwise ActiveRecord has no idea which ActiveRecord class corresponds to an "authors" relationship. Without :class_name, it would look for a nonexistent Author class.
With the relationships in place, it's easy to find blog posts for an author, and authors for a blog post:
# Retroactively make Bob and Carol the collaborative authors of our
# first blog post.
User.create(:name => 'Bob', :blog_posts => [post])
User.create(:name => 'Carol', :blog_posts => [post])
author = User.find(:first)
puts "#{author.name} has made #{author.blog_posts.size} blog post(s)."
# Bob has made 1 blog post(s).
puts %{The blog post "#{post.title}" has #{post.authors.size} author(s).}
# The blog post "First post" has 2 author(s).
As with the has_many or belongs_to relationships, the has_and_belongs_to_many relationship gives you a create method that lets you create new items and their relationships to other items:
author.blog_posts.create(:title => 'Second post',
:content => 'We have some cats as well.')
And since the blog_posts method returns an array-like
object, you can iterate over it to find all the blog posts to which a given user contributed:
author.blog_posts.each do |post|
puts %{#{author.name}'s blog post "#{post.title}" } +
"has #{post.comments.size} comments."
end
# Bob's blog post "First post" has 2 comments.
# Bob's blog post "Second post" has 0 comments.
If you want to delete an item from the database, you can use the destroy method available to all ActiveRecord objects:
BlogPost.find(:first).destroy
However, deleting a
blog post does not automatically remove all the comments associated with that
blog post. You must tell ActiveRecord that comments cannot exist independently of a blog post, like so:
class BlogPost < ActiveRecord::Base
has_many :comments, :dependent => destroy
end
Why doesn't ActiveRecord do this automatically? Because it's not always a good idea. Think about authors: unlike comments, authors can exist independently of a blog post. Deleting a blog post shouldn't automatically delete all of its authors. ActiveRecord depends on you to make this kind of judgment, using your knowledge about your application.
See Also
| 
