Getting Started with ORM

The decision to use ORM depends on your development needs and preferences. Kohana's ORM is great for reducing the total lines of code required for most common create, read, update and delete tasks (especially when working with forms). It can also greatly enhance your development speed and reduce potential bugs introduced by writing custom code. However, ORM may not always produce the most efficient SQL code for all situations.

Although there are ways to fine tune ORM queries, developers that require total control over generated SQL should leverage Kohana's fully-featured database and query builder libraries to write custom queries with the benefit of auto-escaping. Since ORM integrates with the database library, you also have the option of writing custom methods to handle specific queries within your ORM models. There is also a third-party module called Auto Modeler. As with most things in Kohana, the choice is yours!

Although not required, to obtain the most benefits from ORM, it is highly-recommended to use a Relational Database that supports true Foreign Keys. Kohana has drivers for the following databases with foreign key support: PostgreSQL, MySQL (using InnoDB tables) and MSSQL. By using one of these databases, Referential Integrity is enforced at the the database level. You can still use MyISAM tables in MySQL, but you will need to manually delete rows in related tables when foreign keys are removed.

For example, you can create a table that will automatically delete rows when a foreign key is deleted:

-- roles_users joining table (MySQL)
CREATE TABLE roles_users (
  user_id smallint(5) UNSIGNED NOT NULL,
  role_id tinyint(3) UNSIGNED NOT NULL,
  PRIMARY KEY  (user_id,role_id),
  KEY fk_role_id (role_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
ALTER TABLE `roles_users`
  ADD CONSTRAINT roles_users_idfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  ADD CONSTRAINT roles_users_idfk_2 FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE;

The use of constraints (as shown above) will automatically delete the relationship between the user or role objects when either is deleted.

To get the most value out of the ORM library, you should adhere to the conventions outlined below. However, it is possible to override most default conventions via ORM object properties.

• Table names are plural, e.g. users (override by setting $table_names_plural to FALSE). ORM utilizes the inflector helper to determine table names. Note: If your table name does not follow standard pluralization, copy system/config/inflector.php to your application/config directory and add your table to the irregular array (e.g. $config['irregular'] = array('mytable' ⇒ 'mytableplural');).

• Model names are singular (e.g. user) with _Model appended to the model name, e.g. User_Model (override by setting $table_name in your model)
• Each table should have an auto_incrementing column named 'id' as its primary key (override by setting $primary_key in your model)
• Foreign keys should be named using the 'modelname_id' (e.g. user_id)
• Pivot tables should use the parent table names in alphabetical order in the form table1_table2. For example: If you have a many-to-many relationship between users and roles tables, the join table should be named roles_users.

To use ORM, you must first create a Model that extends the ORM library. Each model represents the database table and each object created by the model represents one or more rows from that table. A model should be created for each table that is part of a relationship (excluding pivot tables).

The syntax for creating an ORM model is as follows:

class User_Model extends ORM {}

Understanding relationships is essential to effectively using ORM, as properly defining relationships between your models enables the ORM library to perform its magic. Before defining relationships, it is a good idea to document your current database model to get a clear understanding of the various relationships between your tables. If your database is well documented, it is much easier to properly define the relationships between your ORM models.

The ORM library supports the following relationships in your model:

• has_one for one-to-one relationships
• has_many for the parent side of a one-to-many relationship
• belongs_to for the child side of a one-to-many or one-to-one relationship
• has_and_belongs_to_many for many-to-many relationships

In the examples that follow, we refer to a Blog database – where a post may have an author and an editor (both of which have user accounts in our database). A blog post may be associated with many categories and may have many comments.

It's also important to note that the referenced models have to be pluralized correctly:

• has_one Singular, e.g. category
• has_many Plural, e.g. categories
• belongs_to Singular, e.g. category
• has_and_belongs_to_many Plural, e.g. categories

The has_one relationship allows you to define a one-to-one relationship between two models. For example, a blog post has one user. The foreign key user_id should be defined in your blog_posts table.

class Blog_Post_Model extends ORM {
 
    protected $has_one = array('user');
 
}

Now we can access the user from the blog post like this:

$post = ORM::factory('blog_post', 1);
 
echo $post->user->username;

The has_many relationship is used in conjunction with belongs_to to define a one-to-many relationship between two models. Define has_many on the parent side (plural) and belongs_to on the child side (singular). For example, in a Blog database, each post will have many comments and each comment is associated with one blog post. The foreign key blog_post_id should be defined in your comments table.

Note: Defining a belongs_to relationship in the child model is optional and only required if you need to look up information in the parent model that is associated with the current child model.

class Blog_Post_Model extends ORM {
 
    protected $has_one = array('user');
    protected $has_many = array('comments');
 
}

class Comment_Model extends ORM {
 
    // This is only required if need to find the post by a comment
    protected $belongs_to = array('blog_post');
 
}

This allows us to do the following:

$post = ORM::factory('blog_post', 1);
 
foreach ($post->comments as $comment)
{
    echo $comment->name, $comment->body;
}
 
// We can also load a post via a comment
$comment = ORM::factory('comment', 1);
 
$post = $comment->blog_post;

The has_and_belongs_to_many relationship allows you to define a many-to-many relationship between two tables. It is important to note that many-to-many relationships require a “pivot table” that minimally contains the primary keys of both tables. The pivot table should be named in alphabetical order, such as roles_users. Once defined, has_and_belongs_to_many relationships allow multiple objects to be associated with many other objects, such as relating a blog post to many categories. The has_and_belongs_to_many relationship must be defined in both models.

For example, in a Blog database, blog_posts can be associated with many categories and categories can be associated with many different blog posts. A table called blog_posts_categories with the columns blog_post_id and category_id is required to establish the relationship between blog posts and categories (defining which categories are associated with various blog posts and vice versa).

class Blog_Post_Model extends ORM {
 
    protected $has_and_belongs_to_many = array('categories');
    protected $has_one = array('user');
    protected $has_many = array('comments');
 
}

class Category_Model extends ORM {
 
    protected $has_and_belongs_to_many = array('blog_posts');
 
}

Data is accessed from this relationship using a foreach loop the same way as is done with a has_many relationship:

$post = ORM::factory('blog_post', 1);
 
foreach ($post->categories as $category)
{
    echo $category->name;
}

A more useful example may be finding all of the blog posts that belong to a particular category:

$category = ORM::factory('category', 1);
 
foreach ($category->blog_posts as $post)
{
    echo $post->title, $post->author->username, $post->body;
}

Continue to the next section: Working with ORM >>