Object Relational Mapping (ORM) Library

Object Relational Mapping (ORM) provides a way to create objects that represent rows from a database. It also provides a way to define and retrieve rows that are related by foreign keys. By creating relationships between models that follow convention over configuration, much of the repetition of writing queries to create, read, update and delete information from the database can be reduced or entirely removed. All of the relationships can be handled automatically by the ORM library and you can access related objects just like standard object properties.

Before using the ORM library, you need to create your ORM models and define relationships.

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)
• 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 {}

Once your ORM models are defined, you can load ORM models through standard object instantiation or via the static factory method. Both work exactly the same, but the factory method is chainable, so it is preferred. You can optionally pass a primary key to the constructor or factory to create a ORM object associated with one specific record. Note: In order to access data in related tables, you need to define relationships in your model.

// create a new ORM object
$user = new User_Model();
 
// Create a new ORM object for the primary key of 1
$user = new User_Model(1);
 
// Using the factory method
$user = ORM::factory('user');
 
// Using the factory method with optional primary key
$user = ORM::factory('user', 1);

Note: The find method can also be used to select a different row on a loaded object:

// Load the user with "id = 2"
$user->find(2);

Once your ORM models are loaded, data is accessible using standard object property notation ($object→property). In a one-to-many relationship, where many rows from the child model are associated with the parent model, data within the child model is accessible via a foreach loop (the child model is actually an ORM_Iterator object, not an array). See relationships below.

Here is an example that shows how to access ORM data from the user model (part of the Kohana Auth module).

// accessing data for the user with the primary key of 1
$user = ORM::factory('user', 1);
echo $user->name;

Most query builder methods can be used within ORM and are chainable, giving you a great deal of flexibility and control over the data returned by your ORM models. Note: Calls to query, get, insert, update and delete are not allowed within ORM since internal methods exist in ORM for these operations.

// query builder methods with chaining
$object = ORM::factory('model')->where('field', 'some_value')->orwhere('field', 'another_value')->find();
 
// retrieve a sorted list of books of the first 20 books associated with author (where id = 1)
$author = ORM::factory('author', 1);
$sorted_books = $author->orderby('date','desc')->limit(20)->books;

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 (one-to-one, one-to-many and many-to-many). 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 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.

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 relationship above is simple and effective, but does not accurately communicate the relationship of the user, as the “author”, to the blog post. However, relationships can be aliased to allow multiple users to have relationships to the post. Aliasing allows multiple objects of the same type to be related to another object. Aliasing can also be used to simply change the name of the related object to communicate the purpose of the relationship more clearly.

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

Now we can access both the author and the editor of the blog post like this:

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

The blog_posts database table would have 2 columns now, blog_posts.author_id and blog_posts.editor_id, and both would have values that exist in users.id.

Note Aliases can also be used in reverse within the belongs_to relationship as well.

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('author' => 'user', 'editor' => '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('author' => 'user', 'editor' => '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;
}

Adding and removing data from pivot tables is done using the add() and remove() methods. However, you can also use array(1,2,3) syntax to make multiple changes (adds, deletes) to related data in a pivot data for the current parent model. This allows you to pass the results of a multi-selection HTML form element (e.g. checkbox) as an array directly to your ORM model and ORM will automatically add and remove the appropriate values from your pivot table.

For example, if you have a form that allows users to update categories for a blog post via checkboxes, you can use the following syntax for both adds and updates:

$post = ORM::factory('blog_post', 1);
 
$post->categories = array(1,2); // array should contain id's
$post->save(); // save() must always be called last
 
 
echo '<h4>Categories of '.$post->title.'</h4>', '<ul>';
foreach ($post->categories as $category)
{
    echo '<li>', $category->id, ' ', $author->name, '</li>';
}
echo '</ul>';

If you want similar functionality to has_and_belongs_to_many, but want to have additional data/columns stored in the pivot table for the relationships, you can use a has_many through relationship. This looks similar to aliasing:

class Post_Model extends ORM {
 
    protected $has_one = array('author' => 'user', 'editor' => 'user');
    protected $has_many = array('comments', 'categories'=>'posts_categories');
}
 
class Posts_Category_Model extends ORM {
 
}

Above the Post_Model is linked to different categories through the posts_categories pivot table. You must also create a model for the pivot table Posts_Category_Model. You can now access both the categories and the pivot table by using the following:

$post = ORM::factory('post', $post_id);
 
foreach ($post->categories as $category)
{ ... }
 
foreach ($post->posts_categories as $pivot)
{ ... }

Note: You should have an ID column for the pivot table (which, unlike has_and_belongs_to_many, doesn't require one)

All of the default public and protected methods of ORM are listed here.

Static method used to load ORM objects:

$object = ORM::factory($model_name, $row_id = NULL);

Find executes the database query, gets one row and sets the current object to the result.

// find the article with primary key = 1
$object = ORM::factory('article')->find(1);
echo $object->title;

// find an article by title
$object = ORM::factory('article')->where('title', $title)->find();

Find_all executes a database query and returns the multiple records using the ORM_Iterator

$articles = ORM::factory('article')->find_all();
foreach($articles as $article)
{
    echo $article->title;
}

Also you can get a range of the multiple records using additional parameters (like in SQL LIMIT)

$limit = 10;
$offset = 30;
//it will return 10 records started from row #30
$articles = ORM::factory('article')->find_all($limit,$offset);

Save the current object into the database. If the object has no 'id' set it will insert a new record, else it will update. Note: You need to call save() after add() and remove() to save changes to related tables.

$article = ORM::factory('article', 1);
 
$article->title = 'New title';
$article->save();

Newly created objects will always be reloaded after they are saved, to properly account for default values of columns.

As of 2.4, this method offers an 'id' parameter that allows you to update a record without first loading it (avoiding an additional query)

$article = ORM::factory('article');
 
$article->title = 'New title';
$article->save(1);

Clears the state of an object, making it empty for reuse.

$article = ORM::factory('article', 1);
 
// Article is now empty
$article->clear();
 
var_dump($article->loaded); // returns FALSE

Reloads the ORM object from the database. If $this→reload_on_wakeup is enabled, unserializing an object will cause it to be reloaded.

$article = ORM::factory('article', 1);
 
$article->title = 'A different title';
 
// Article title will be reset to the saved state
$article->reload();
 
var_dump($article->title); // returns the original title

Delete deletes current object or object with the given id.

$article = ORM::factory('article', 1);
$article->delete();
// OR
ORM::factory('article')->delete(1); // Only uses one query instead of two

Delete deletes multiple objects. Will delete all objects of this type with no arguments, or an array can be used to specify the IDs to delete.

// Deletes all records
ORM::factory('article')->delete_all();
 
// Deletes records with ID 1, 2, 4, and 5
ORM::factory('article')->delete_all(array(1,2,4,5));

Returns the current object in array format.

$article = ORM::factory('article', 1)->as_array();
 
echo $article['title'];

Generates a key/value pair array of all the objects. The function accepts two column names as parameters: the first column is the value and the second column is the name or description. This is especially useful when used in conjunction with the form helper to automatically build and populate selection menus.

The following example generates links to all articles followed by an HTML select form element pre-populated with all articles.

$articles = ORM::factory('article')->select_list('id', 'title');
 
foreach ($articles as $id => $title)
{
    // Display a list of links
    echo html::anchor('articles/'.$id, $title);
}
 
// Display a dropdown list
echo form::dropdown('articles', $articles);

Tests if an object has a many-to-many relationship with another object. The following code will test if the user has the login role. This method always returns a boolean.

$user = ORM::factory('user', 1);
 
$user->has(ORM::factory('role', 'login'));

Adds a relationship to an object that has a many-to-many relationship. The following code will add the admin role to a user. Note that you need to call the save() method to add the relationship and related records. ORM does not automatically save your changes.

$user = ORM::factory('user', 1);
 
$user->add(ORM::factory('role', 'admin'));
 
$user->save();

Alternative syntax is also available to add multiple relationships in a many-to-many pivot table using array(id, id) syntax.

Remove a relationship from an object that has a many-to-many relationship. The following code will remove the login role from the user. Note that you need to call the save() method to remove the relationship and related records. ORM does not automatically save your changes.

$user = ORM::factory('user', 1);
 
$user->remove(ORM::factory('role', 'login'));
 
$user->save();

Alternative syntax is also available to add/update multiple relationships in a many-to-many pivot table using array(id, id) syntax. Id's excluded from the array will be removed.

Binds a one-to-one relationship using a JOIN. This is useful in situations where you do not want to use lazy-loading, thus improving performance. You can also bind nested one-to-one relationships using a colon.

// This uses 1 SQL query to fetch the user, associated city, and associated country.
$users = ORM::factory('user')->with('city')->with('city:country')->find_all();
 
foreach($users as $user) {
  echo $user->city->country->name;
}

You can also set the $load_with property of the ORM model to bind automatically.

ORM has several public object properties which can be used for various purposes. By default, all of these properties are managed by ORM and will change dynamically based on object. They should never be manually set in a model.

Boolean for seeing whether the current object has been loaded from the database. This can be used to test if an object has been successfully loaded.

$article = ORM::factory('article', 1);
 
if ($article->loaded==true)
{
    echo 'loaded article ', $article->id;
}
else
{
    echo 'no article by that id exists';
}

Array used by ORM to keep track of changes made to columns in an ORM model prior to saving. You can check the status of a specific column by using isset($this→changed['name']).

The changed property is very useful within the context of an overloaded save() method in your ORM Model. Overloading the save() method allows you to perform extra processing, filtering or data integrity checks prior to saving any new/updated data for your ORM Model.

// overload the save method in your ORM Model
public function save()
{
    if (isset($this->changed['name']))
    {
        // set the slug when the name changes -- 'my-post-name'
	$this->slug = url::title($this->name);
    }
}

Boolean for checking whether the current object is saved.

$article = ORM::factory('article', 1);
 
if($article->saved==false)
{
    echo 'not saved';
}
 
$article->save();
 
if($article->saved==true)
{
    echo 'saved';
}

The simple name of the object. If my class is named Blog_Post_Model, then blog_post is the object_name.

The column name of the primary key.

A convenience value corresponding to a column in the table. By default it is set to name. It can be used as a more human-friendly identifier for table rows. For instance, if you had a users table, you might set it to username.

The name of the database table that holds the records.

The database table column information that is being used.

An array of sorting parameters that should be applied to queries. By default, results are sorted by id ASC. You can add multiple columns and directions to this property.

class User_Model extends ORM {
 
    protected $sorting = array('last_login' => 'desc', 'username' => 'asc');
 
}

Kohana currently supports three databases that allow for true foreign keys: PostgreSQL, MySQL (using InnoDB tables), and MSSQL. By using one of these databases, relationship integrity is enforced at the the database level.

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_ibfk_1 FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
  ADD CONSTRAINT roles_users_ibfk_2 FOREIGN KEY (role_id) REFERENCES roles (id) ON DELETE CASCADE;

Use constraints like this will automatically delete the relationship between the user or role objects when either is deleted.

If we have two tables in a one_to_many relationship: schools and students (a school has many students) and want to list information about each student in the school you need to use a foreach loop. Note: As you loop through the objects using foreach, only one object exists at any given point. Note: Each time the loop continues, the old object is replaced by a new object. This is done to keep memory usage low when working with many objects at once.

// in your School_Model
protected $has_many = array('students');

// in your Student_Model
protected $belongs_to = array('school');

// in your controller
 
// create a new ORM object for school id = 1
$school = new School_Model(1);
 
// iterate through all of the students associated with the school and output their name
foreach ($school->students as $student)
{
    echo $student->name.'<br/>';
}

ORM and Pagination can be combined quickly by using the count_last_query method. The following example would be used in a controller.

$per_page = $this->input->get('limit', 1);
$page_num = $this->input->get('page', 1);
$offset   = ($page_num - 1) * $per_page;
 
$user = ORM::factory('user', 'john.smith');
$posts = $user->limit($per_page, $offset)->blog_posts;
$pages = $pages = Pagination::factory(array
(
    'style' => 'digg',
    'items_per_page' => $per_page,
    'query_string' => 'page',
    'total_items' => $user->count_last_query()
));
 
foreach ($posts as $post)
{
    echo $post->title, ' by ', $post->author->username, '<br/>';
}
echo $pages;

When creating a database record when there are associated columns in a one-to-many relationship, you need to obtain the last insert id of the parent object to properly add an associated child row. The save() method in ORM sets the primary key of the object (usually id) to the last_insert_id.

// models/page.php
class Page_Model extends ORM {
	protected $has_many = array('keywords');
}
 
// models/keyword.php
class Keyword_Model extends ORM {
	protected $belongs_to = array('pages');
}
 
// in your controller
 
// create a new page object
$page = ORM::factory('page');
$page->title = "Test Page";
$page->content = "This is a test page";
$page->save();
 
$keyword = ORM::factory('keyword');
$keyword->name = "testing";
$keyword->page_id = $page->id;  // $page->id has the last insert id from the above save
$keyword->save();

By default, ORM will load using the id column as the identifier for the unique row within the database. However it is possible to allow ORM to load the object from other unique keys. ORM uses a method called unique_key to load data and this method can be overloaded within your ORM model to allow other columns to be used.

The example demonstrates the use of unique_key within an ORM model. The id is checked for data type. If the datatype is not a digit and is a string, the column shortname will be used to load the model.

public function unique_key($id = NULL)
{
	if ( ! empty($id) AND is_string($id) AND ! ctype_digit($id) )
	{
		return 'shortname';
	}
 
	return parent::unique_key($id);
}

If you intend to you use custom unique keys within your application, it is a good idea to ensure you correctly index all columns being used as a unique identifier. This will ensure that as your application scales, performance is not adversely effected.

Assuming the homepage record has an ID of 1, including the unique_key method within your ORM model allows the following constructor methods in your code.

// Using the ORM::factory method
$my_page = ORM::factory( 'Page', 'homepage' );
 
// Using the standard constructor
$my_other_page = new Page_Model( 'homepage' );
 
$my_old_method = new Page_Model( 1 );

$my_page, $my_other_page and $my_old_method will all contain the same record.

ORM tree is an ORM extension that allows creating an object relationship with itself. Typical use of this library can be a category hierarchy, in other words parent ↔ childrens link.

class Category_Model extends ORM_Tree
{
	protected $children = "categories";
}

echo Kohana::debug(ORM::factory("Category", 42)->children->as_array());
//returns the children for the category with id 42

echo Kohana::debug(ORM::factory("Category", 4)->parent->name);
//returns the parent name of the category with ID 4