JOINING TO MULTIPLE TABLES 343

Sharing Association Extensions

You’ll sometimes want to apply the same set of extensions to a number of associations. You can do this by putting your extension methods in a Ruby module and passing that module to the association declaration with the :extend parameter.

has_many :articles, :extend => RatingFinder

You can extend an association with multiple modules by passing :extend an array.

has_many :articles, :extend => [ RatingFinder, DateRangeFinder ]

18.4Joining to Multiple Tables

Relational databases allow us to set up joins between tables: a row in our orders table is associated with a number of rows in the line items table, for example. The relationship is statically defined. However, sometimes that isn’t convenient.

You could get around this with some clever coding, but fortunately you don’t have to do so. Rails provides two mechanisms for mapping a relational model into a more complex object-oriented one: single-table inheritance and polymorphic associations. Let’s look at each in turn.

Single-Table Inheritance

When we program with objects and classes, we sometimes use inheritance to express the relationship between abstractions. Our application might deal with people in various roles: customers, employees, managers, and so on. All roles will have some properties in common and other properties that are role specific. We might model this by saying that class Employee and class Customer are both subclasses of class Person and that Manager is in turn a subclass of Employee. The subclasses inherit the properties and responsibilities of their parent class.2

In the relational database world, we don’t have the concept of inheritance: relationships are expressed primarily in terms of associations. But single-table inheritance, described by Martin Fowler in Patterns of Enterprise Application Architecture [Fow03], lets us map all the classes in the inheritance hierarchy into a single database table. This table contains a column for each of the attributes of all the classes in the hierarchy. It additionally includes a column, by convention called type, that identifies which particular class of object is

2. Of course, inheritance is a much-abused construct in programming. Before going down this road, ask yourself whether you truly do have an is-a relationship. For example, an employee might also be a customer, which is hard to model given a static inheritance tree. Consider alternatives (such as tagging or role-based taxonomies) in these cases.

Report erratum

JOINING TO MULTIPLE TABLES 344

represented by any particular row. This is illustrated in Figure 18.2, on the following page.

Using single-table inheritance in Active Record is straightforward. Define the inheritance hierarchy you need in your model classes, and ensure that the table corresponding to the base class of the hierarchy contains a column for each of the attributes of all the classes in that hierarchy. The table must additionally include a type column, used to discriminate the class of the corresponding model objects.

When defining the table, remember that the attributes of subclasses will be present only in the table rows corresponding to those subclasses; an employee doesn’t have a balance attribute, for example. As a result, you must define the table to allow null values for any column that doesn’t appear in all subclasses. The following is the migration that creates the table illustrated in Figure 18.2, on the next page.

Download e1/ar/sti.rb

create_table :people, :force => true do |t| t.column :type, :string

#common attributes t.column :name, :string t.column :email, :string

#attributes for type=Customer

t.column :balance, :decimal, :precision => 10, :scale => 2

# attributes for type=Employee

t.column	:reports_to,	:integer
t.column	:dept,	:integer

#attributes for type=Manager

#- none -

end

We can define our hierarchy of model objects.

Download e1/ar/sti.rb

class Person < ActiveRecord::Base end

class Customer < Person end

class Employee < Person

belongs_to :boss, :class_name => "Employee", :foreign_key => :reports_to end

class Manager < Employee end

Report erratum

JOINING TO MULTIPLE TABLES 345

Person

class Person < ActiveRecord::Base

name

# ..

email

end

class Customer < Person

# ...

Customer

Employee

balance

reports_to

end

dept

class Employee < Person

# ...

end

Manager

class Manager < Employee

# ...

end

people

id

type

name

email

balance reports_to dept

1

Customer

John Doe

[e-mail]

78.29

2

Manager

Wilma Flint

[e-mail]

23

3

Customer

Bert Public

[e-mail]

12.45

4

Employee

Barney Rub

[e-mail]

2

23

5

Employee

Betty Rub

[e-mail]

2

23

6

Customer

Ira Buyer

[e-mail]

-66.76

7

Employee

Dino Dogg

[e-mail]

2

23

Figure 18.2: Single-Table Inheritance: A Hierarchy of Four Classes Mapped into One Table

Report erratum

JOINING TO MULTIPLE TABLES 346

Then we create a couple of rows and read them back.

Download e1/ar/sti.rb
Customer.create(:name => 'John Doe',	:email => "[e-mail]" ,
:balance => 78.29)

wilma = Manager.create(:name => 'Wilma Flint', :email => "[e-mail]" , :dept => 23)

Customer.create(:name => 'Bert Public', :email => "[e-mail]" , :balance => 12.45)

barney = Employee.new(:name => 'Barney Rub', :email => "[e-mail]", :dept => 23)

barney.boss = wilma barney.save!

manager = Person.find_by_name("Wilma Flint")

puts manager.class		#=> Manager
puts	manager.email	#=>	[e-mail]
puts	manager.dept	#=>	23

customer = Person.find_by_name("Bert Public")

puts customer.class		#=> Customer
puts	customer.email	#=>	[e-mail]
puts	customer.balance	#=>	12.45

Notice how we ask the base class, Person, to find a row, but the class of the object returned is Manager in one instance and Customer in the next; Active Record determined the type by examining the type column of the row and created the appropriate object.

Notice also a small trick we used in the Employee class. We used belongs_to to create an attribute named boss. This attribute uses the reports_to column, which points back into the people table. That’s what lets us say barney.boss = wilma.

There’s one fairly obvious constraint when using single-table inheritance. Two subclasses can’t have attributes with the same name but with different types, because the two attributes would map to the same column in the underlying schema.

There’s also a less obvious constraint. The attribute type is also the name of a built-in Ruby method, so accessing it directly to set or change the type of a row may result in strange Ruby messages. Instead, access it implicitly by creating objects of the appropriate class, or access it via the model object’s indexing interface, using something such as

person[:type] = 'Manager'

Report erratum

JOINING TO MULTIPLE TABLES 347

Joe Asks. . .

What If I Want Straight Inheritance?

Single-Table Inheritance is clever—it turns on automatically whenever you subclass an Active Record class. But what it you want real inheritance–you want to define some behavior to be shared among a set of Active Record classes by defining an abstract base class and a set of subclasses?

The answer is to define a class method called abstract_class? in your abstract base class. The method should return true. This has two effects. First, Active Record will never try to find a database table corresponding to this abstract class. Second, all subclasses of this class will be treated as independent Active Record classes—each will map to its own database table.

Of course, a better way of doing this is probably to use a Ruby module containing the shared functionality, and mix this module into Active Record classes that need that behavior.

David Says. . .

Won’t Subclasses Share All the Attributes in STI?

Yes, but it’s not as big of a problem as you think it would be. As long as the subclasses are more similar than not, you can safely ignore the reports_to attribute when dealing with a customer. You simply just don’t use that attribute.

We’re trading the purity of the customer model for speed (selecting just from the people table is much faster than fetching from a join of people and customers tables) and for ease of implementation.

This works in a lot of cases, but not all. It doesn’t work too well for abstract relationships with very little overlap between the subclasses. For example, a content management system could declare a Content base class and have subclasses such as Article, Image, Page, and so forth. But these subclasses are likely to be wildly different, which will lead to an overly large base table because it has to encompass all the attributes from all the subclasses. In this case, it would be better to use polymorphic associations, which we describe next.

Report erratum

JOINING TO MULTIPLE TABLES 348

Polymorphic Associations

One major downside of STI is that there’s a single underlying table that contains all the attributes for all the subclasses in our inheritance tree. We can overcome this using Rails’ second form of heterogeneous aggregation, polymorphic associations.

Polymorphic associations rely on the fact that a foreign key column is simply an integer. Although there’s a convention that a foreign key named user_id references the id column in the users table, there’s no law that enforces this.3

In computer science, polymorphism is a mechanism that lets you abstract the essence of something’s interface regardless of its underlying implementation. The addition method, for example, is polymorphic, because it works with integers, floats, and even strings.

In Rails, a polymorphic association is an association that links to objects of different types. The assumption is that these objects all share some common characteristics but that they’ll have different representations.

To make this concrete, let’s look at a simple asset management system. We index our assets in a simple catalog. Each catalog entry contains a name, the acquisition date, and a reference to the actual resource: an article, an image, a sound, and so on. Each of the different resource types corresponds to a different database table and to a different Active Record model, but they are all assets, and they are all cataloged.

Let’s start with the three tables that contain the three types of resource.

Download e1/ar/polymorphic.rb

create_table :articles, :force => true do |t| t.column :content, :text

end

create_table :sounds, :force => true do |t| t.column :content, :binary

end

create_table :images, :force => true do |t| t.column :content, :binary

end

Now, let’s think about the three models that wrap these tables. We’d like to be able to write something like

# THIS DOESN'T WORK

class Article < ActiveRecord::Base has_one :catalog_entry

end

3. If you specify that your database should enforce foreign key constraints, polymorphic associations won’t work.

Report erratum

JOINING TO MULTIPLE TABLES 349

class Sound < ActiveRecord::Base has_one :catalog_entry

end

class Image < ActiveRecord::Base has_one :catalog_entry

end

Unfortunately, this can’t work. When we say has_one :catalog_entry in a model, it means that the catalog_entries table has a foreign key reference back to our table. But here we have three tables each claiming to have_one catalog entry: we can’t possibly arrange to have the foreign key in the catalog entry point back to all three tables...

...unless we use polymorphic associations. The trick is to use two columns in our catalog entry for the foreign key. One column holds the id of the target row, and the second column tells Active Record which model that key is in. If we call the foreign key for our catalog entries resource, we’ll need to create two columns, resource_id and resource_type. Here’s the migration that creates the full catalog entry.

Download e1/ar/polymorphic.rb

create_table :catalog_entries, :force => true do |t| t.column :name, :string

t.column :acquired_at, :datetime t.column :resource_id, :integer t.column :resource_type, :string

end

Now we can create the Active Record model for a catalog entry. We have to tell it that we’re creating a polymorphic association through our resource_id and resource_type columns.

Download e1/ar/polymorphic.rb

class CatalogEntry < ActiveRecord::Base belongs_to :resource, :polymorphic => true

end

Now that we have the plumbing in place, we can define the final versions of the Active Record models for our three asset types.

Download e1/ar/polymorphic.rb

class Article < ActiveRecord::Base

has_one :catalog_entry, :as => :resource end

class Sound < ActiveRecord::Base

has_one :catalog_entry, :as => :resource end

Report erratum

JOINING TO MULTIPLE TABLES 350

class Image < ActiveRecord::Base

has_one :catalog_entry, :as => :resource end

The key here is the :as options to has_one. It specifies that the linkage between a catalog entry and the assets is polymorphic, using the resource attribute in the catalog entry. Let’s try it.

Download e1/ar/polymorphic.rb

a = Article.new(:content => "This is my new article")

c = CatalogEntry.new(:name => 'Article One', :acquired_at => Time.now) c.resource = a

c.save!

Let’s see what happened inside the database. There’s nothing special about the article.

mysql>			select	*	from articles;
+----		+------------------------			+
| id |			content		|
+----		+------------------------			+
| 1		|	This is my new article |
+----		+------------------------			+
1	row in set (0.00 sec)

The catalog entry has the foreign key reference to the article and also records the type of Active Record object it refers to (an Article).

mysql>			select * from		catalog_entries;	+	+	+
+----		+-------------		+---------------------
| id |			name	|	acquired_at	| resource_id	| resource_type	|
+----		+-------------		+---------------------		+-------------	+---------------	+
| 1		|	Article One	|	2006-07-18 16:48:29 | 1		| Article	|
+----		+-------------		+---------------------		+-------------	+---------------	+
1	row in set (0.00			sec)

We can access data from both sides of the relationship.

Download e1/ar/polymorphic.rb
article = Article.find(1)
p article.catalog_entry.name	#=> "Article One"
cat = CatalogEntry.find(1)
resource = cat.resource
p resource	#=> #<Article:0x640d80		@attributes={"id"=>"1",
	#	"content"=>"This	is my new article"}>

The clever part here is the line resource = cat.resource. We’re asking the catalog entry for its resource, and it returns an Article object. It correctly determined the Active Record class, read from the appropriate database table (articles), and returned the right class of object.

Let’s make it more interesting. Let’s clear out our database and then add assets of all three types.

Report erratum

JOINING TO MULTIPLE TABLES 351

Download e1/ar/polymorphic.rb

c = CatalogEntry.new(:name => 'Article One', :acquired_at => Time.now) c.resource = Article.new(:content => "This is my new article")

c.save!

c = CatalogEntry.new(:name => 'Image One', :acquired_at => Time.now) c.resource = Image.new(:content => "some binary data")

c.save!

c = CatalogEntry.new(:name => 'Sound One', :acquired_at => Time.now) c.resource = Sound.new(:content => "more binary data")

c.save!

Now our database looks more interesting.

mysql>		select *	from articles;
+----	+------------------------		+
| id |		content	|
+----	+------------------------		+
| 1	|	This is my new article |
+----	+------------------------		+
mysql>		select *	from images;
+----	+------------------		+
| id |		content	|
+----	+------------------		+
| 1	|	some binary data |
+----	+------------------		+
mysql>		select *	from sounds;
+----	+------------------		+
| id |		content	|
+----	+------------------		+
| 1	|	more binary data |
+----	+------------------		+
mysql>		select *	from catalog_entries;	+	+	+
+----	+-------------		+---------------------
| id |		name	| acquired_at	| resource_id	| resource_type |
+----	+-------------		+---------------------	+-------------	+---------------	+
| 1	|	Article One | 2006-07-18 17:02:05 | 1			| Article	|
| 2	|	Image One	| 2006-07-18 17:02:05 | 1		| Image	|
| 3	|	Sound One	| 2006-07-18 17:02:05 | 1		| Sound	|
+----	+-------------		+---------------------	+-------------	+---------------	+

Notice how all three foreign keys in the catalog have an id of 1—they are distinguished by their type column.

Now we can retrieve all three assets by iterating over the catalog.

Download e1/ar/polymorphic.rb

CatalogEntry.find(:all).each do |c| puts "#{c.name}: #{c.resource.class}"

end

This produces

Article One: Article

Image One: Image

Sound One: Sound

Report erratum

		JOINING TO MULTIPLE TABLES 352
	has_one :other	belongs_to :other
other(reload=false)
other=
create_other(...)
build_other(...)
replace
updated?
	has_many :others	habtm :others
others
others=
other_ids=
others.<<
others.build(...)
others.clear(...)
others.concat(...)
others.count
others.create(...)
others.delete(...)
others.delete_all
others.destroy_all
others.empty?
others.find(...)
others.length
others.push(...)
others.replace(...)
others.reset
others.size
others.sum(...)
others.to_ary
others.uniq
push_with_attributes(...)		[deprecated]

Figure 18.3: Methods Created by Relationship Declarations

Report erratum

SELF-REFERENTIAL JOINS 353

18.5Self-referential Joins

It’s possible for a row in a table to reference back to another row in that same table. For example, every employee in a company might have both a manager and a mentor, both of whom are also employees. You could model this in Rails using the following Employee class.

Download e1/ar/self_association.rb

class Employee < ActiveRecord::Base belongs_to :manager,

:class_name => "Employee", :foreign_key => "manager_id"

belongs_to :mentor,

:class_name => "Employee", :foreign_key => "mentor_id"

has_many :mentored_employees, :class_name => "Employee", :foreign_key => "mentor_id"

has_many :managed_employees, :class_name => "Employee", :foreign_key => "manager_id"

end

Let’s load up some data. Clem and Dawn each have a manager and a mentor.

Download e1/ar/self_association.rb

Employee.delete_all

adam = Employee.create(:id => 1, :name => "Adam") beth = Employee.create(:id => 2, :name => "Beth")

clem = Employee.new(:name => "Clem") clem.manager = adam

clem.mentor = beth clem.save!

dawn = Employee.new(:name => "Dawn") dawn.manager = adam

dawn.mentor = clem dawn.save!

Then we can traverse the relationships, answering questions such as “who is the mentor of X?” and “which employees does Y manage?”

Download e1/ar/self_association.rb

p adam.managed_employees.map {|e| e.name}		# => [ "Clem", "Dawn" ]
p	adam.mentored_employees	#	=>	[]
p	dawn.mentor.name	#	=>	"Clem"

You might also want to look at the various acts as relationships.

Report erratum