Creating Data Relations

ActiveRecord allows to organize relations between data models in a very simple way. Mostly you will need to use the following types of relations: one-to-many and many-to-many. These cases are implemented in ActiveRecord as has many, belongs to, and has and belongs to many relations.

"Has Many" data relation

The has many relation is suitable when you need to link multiple records from a details table to a single record in a master table. For example, LemonStand products can have options. Each product can have many options. Products and options are different tables. To organize a connection between tables, we use an extra field (a foreign key) in the options table - product_id. This field contains an identifier of a product the option belongs to. On a database level the relation looks like follows:

It is possible to have many records in the options table, with a same value of the product_id field. Thus, we can link many options to a single product. Now we can organize a link between model PHP classes. Of course, first we need to have both model classes - for products and for options. The process of creating a simple model class is described in the Working with the database article. If we have the Product and Option model classes, it is simple to add a has many relation to the products data model:

<?
  class Product extends Db_ActiveRecord
  {
    public $table_name = 'products';
    
    public $has_many = array(
      'options'=>array('class_name'=>'Option', 'foreign_key'=>'product_id')
    );
  }
?>

The $has_many field is dedicated for declaring "has many" relations. It is an array-type field. Each element of this array defines a separate has-many relation. The array indexes ("options" in this case) define relation names, which you can use for accessing a list of related objects (see below). The array used for defining a relation should contain the following indexes:

• class_name - a name of a PHP model class which represents detail records. The class should be defined in a separate file, as a usual data model class.
• foreign_key - a name of a foreign key field in the details table.

Also you can use some optional indexes:

• order - a field name in the details table to use for ordering details records.
• delete - indicates that detail records should be automatically deleted from the database when you delete a master record.
• conditions - allows to filter the detail records list using a SQL query.

If we apply all possible parameters to the options relation declaration, we will have the following relation declaration:

public $has_many = array(
  'options'=>array('class_name'=>'Option', 'foreign_key'=>'product_id', 'order'=>'name', 'delete'=>true, 'conditions'=>'enabled=1')
);

Now, when we have the options data relation declared in the product class we can fetch a list of product options using the following code:

<?
  // Create a product class instance and find a record with identifier 1
  $product = new Product();
  $product = $product->find(1);
  
  // Fetch product options
  $options = $product->options;
  
  // Output options
  foreach ($options as $option)
    echo $option->name;
?>

The options class field added by the ActiveRecord engine is an object of the Db_DataCollection class. This class allows to iterate through a list of items, and add or delete collection elements. You can find more details about the class in the class reference documentation section.

"Belongs To" data relation

The belongs to data relation represents the same data relation scenario as the has many relation, but from the details data model point of view. In case of the products and product options case explained earlier in this section, the belongs to relation could be added to the product options model. It would mean "The option belongs to a product". The relation declaration code could look like this:

<?
  class Option extends Db_ActiveRecord
  {
    public $table_name = 'product_options';
    
    public $belongs_to = array(
      'product'=>array('class_name'=>'Product', 'foreign_key'=>'product_id')
    );
  }
?>

This code adds the product field to the Option class. The foreign_key parameter should contain a name of a foreign key used for binding data in the details table with records in the master table. Now if you have an Option class instance, you can easily a product the option belongs to:

<?
  // Create an option class instance and find a record with identifier 1
  $option = new Option();
  $option = $option->find(1);
  
  // Output the option product name
  $product = $option->product;
  echo $product->name; 
?>

The product field of the Option class is an instance of the Product class. In case if there is no product record the option refers to, the product field would be null. It means that it is a good idea to check object existence before accessing their fields or methods.

"Has and Belongs To Many" data relation

The has and belongs to many data relation represents the many-to-many data layout scenario. The example of a many-to-many relation is blog posts which could belong to multiple categories. The difference between the has many and has and belongs to relations is that the has and belongs to relation allows same categories to be assigned to multiple blog posts. This relation requires an extra join table for storing relations between master and detail records.

The post_categories table has foreign keys which refer to both master and details table records. To implement this data layout in data models, you need two model classes - for the blog posts and for the categories. You do not need a model class for the join table. This is a definition of the blog posts model class:

<?
  class Post extends Db_ActiveRecord
  {
    public $table_name = 'posts';
    
    public $has_and_belongs_to_many = array(
      'categories'=>array(
          'class_name'=>'Category',
          'join_table'=>'posts_categories',
          'primary_key'=>'post_id',
          'foreign_key'=>'category_id'
       )
    );
  }
?>

The $has_and_belongs_to_many field declaration creates the categories field in the Post class, which contains a list of all categories assigned to a post. The field is in instance of the Db_DataCollection class. The $has_and_belongs_to_many field should be an array. The ActiveRecord engine creates class fields with names matching the array index names. Each relation should be declared as an array with the following required indexes:

• class_name - a name of a PHP model class representing a details record
• join_table - a name of a database table used for linking master and detail records
• primary_key - a name of a field in the join table, which refers to the master table record identifiers
• foreign_key - a name of a field in the join table, which refers to the details table record identifiers

Also you can use optional parameters - order, delete and conditions which are described in the Has Many data relation earlier in this section.

Extending the blog module with post categories

In this section we will continue extending a simple blog module which we started in the Developing a simple module article. Now we are going to add blog categories model class and bind it to the blog posts model, which we already created earlier. We will use many-to-many data layout, and this will allow us ti bind multiple categories to each blog post.

Adding the categories and join table

As it was described in the Creating and updating database tables article, in order to create new database tables, we need to add a record to the version information file, and create a corresponding SQL file. After adding a line to the version.dat file it contents should look like follows:

#1 Module initialization
#2 Created the blog posts database table
#3 Added the categories feature

We also prepared a content for the 3.sql file update file, which should be created in the updates module directory. To prepare the queries, we created the tables using the CocoaMySQL tool. A content of the 3.sql file:

CREATE TABLE `abcblog_categories` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `abcblog_posts_categories` (
  `category_id` int(11) NOT NULL default '0',
  `post_id` int(11) NOT NULL default '0',
  PRIMARY KEY  (`category_id`,`post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

In order to apply updates we need to log out and log in to the Administration Area.

Defining the category model class and adding the post-categories data relation

According the naming conventions described in the Developing LemonStand modules article, a suitable name for the category model class is AbcBlog_Category. A PHP file for this class should have the abcblog_category.php name, and should be placed to the module models directory. This is a definition of the AbcBlog_Category class:

<?
  class AbcBlog_Category extends Db_ActiveRecord
  {
    public $table_name = 'abcblog_categories';
  }
?>

Now we can add the categories field to the AbcBlog_Post class:

<?
  class AbcBlog_Post extends Db_ActiveRecord
  {
    public $table_name = 'abcblog_posts';

    public $has_and_belongs_to_many = array(
      'categories'=>array(
          'class_name'=>'AbcBlog_Category',
          'join_table'=>'abcblog_posts_categories',
          'primary_key'=>'post_id', 
          'foreign_key'=>'category_id'
       )
    );
  }
?>

Now our blog post class has the categories field. To test whether our code works, we can create a category and add it to an existing blog post record.

// Create a new category object
$category = new AbcBlog_Category();
$category->name = 'My first category';
$category->save();

// Find a blog post with identifier 1
$post = new AbcBlog_Post();
$post = $post->find(1);

// Add the category to the post category list
$post->categories->add($category);

// Save the post record in order to apply changes
$post->save();

Please note that if you are adding a new related object to a master object relation, the new object should be saved before it is added (see the example above).

If you look to the database after executing this code, you will see that the abcblog_categories contains one record

Also, the abcblog_posts_categories table will contain a single record as well:

As you can see, adding manipulating with data using model classes is a simple task. In the further articles we will demonstrate how you can add pages to the Administration Area for managing categories and blog posts. You can download the updated blog module here.

Previous: Creating and Updating Database Tables
Return to Working with the Database