Single Table Inheritance is one of the coolest features of Ruby on Rails. Unfortunately, it is one feature that has not made its way into CakePHP. With a little bit of work, however, you can implement it for yourself. It should be noted, however, that this is by no means a complete solution. What follows works for me in my applications but does not cover all possible scenarios. This tutorial is in the same table as my blog entries so you are seeing the methods below in action but your mileage may vary.

An Example Scenario

To best illustrate this method we will need an example scenario to work with. As I mentioned in my blog entry on Single Table Inheritance, it is particularly helpful when creating a site like Digg. For our example, let’s consider building a site called Excavate. Our requirements are rather simple: a site for people to input links with descriptions. Once a link has been entered it will be considered an "upcoming" entry. Once enough members (only people who have signed up can view and excavate upcoming items) excavate an item it gets green-lighted and becomes a "current" entry. We will store both types of entry in one database table.

The Database Bits

The basic information we need for both kinds of entry is the same: a title, a description, a url, an author name, an author email address, and a created date. A current posting will also require the date which it was green-lighted to order the postings chronologically. To these required fields we will be adding a type field which will hold a reference to which object the record references, upcoming or current. Snippet 1 shows the SQL code for creating our table in MySQL.

Snippet #1: SQL Code for our entries table.

CREATE TABLE `entries` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `title` VARCHAR(255) NOT NULL,
    `description` text NOT NULL,
    `url` var CHAR(255) NOT NULL,
    `author_name` VARCHAR(255) NOT NULL,
    `author_email` VARCHAR(255) NOT NULL,
    `created` datetime DEFAULT NULL,
    `greenlit` datetime DEFAULT NULL,
    `type` VARCHAR(255) NOT NULL DEFAULT 'BlogEntry',
    PRIMARY KEY  (`id`), )
    ENGINE=MyISAM DEFAULT CHARSET=latin1;

There are a few things to check with your database code to ensure that everything will function smoothly. First of all, ensure that your `created` field is called created. CakePHP will automagically handle setting this value for you when you are saving your model. Secondly, ensure that you do not set any model-specific fields to NOT NULL. If, for example, we had set the `greenlit` field to be NOT NULL we would run into troubles when saving an upcoming object, as that object has not been greenlit yet.

The Models

Now that we have the database table in place to hold our data it is time to write the Model classes for our two objects. We will need to add several variable to the models and override several methods from the parent class AppModel. Let’s start by looking at the Current model.

Snippet #2: Class definition and variables
for the Current Model.

class Current extends AppModel
{
    var $name = 'Current';
 
    //single table inheritance vars
    var $__table = 'entries';
    var $__fields = array('Current.id', 'Current.title','Current.description',
'Current.author_name','Current.author_email','Current.created',
'Current.greenlit');
    var $__type_field = 'type';

The first variable we need to define is $name. This is required in CakePHP if you are using PHP4 but not in PHP5. Nevertheless, we will be using it as it will make many things we are going to do much easier. Ensure that the value of this variable matches exactly the name of your class. The next variable $__table contains the name of the database table in which we are storing our objects. $__fields is an array containing a list of the fields that belong to this model. Note that each field is prefixed with the name of the class it belongs to – even though the name of the table is not Current this will work because the queries created by CakePHP will have FROM `entries` AS `Current` to make everything play nicely. Also of note is the fact that we are not specifying that the type field is a part of the model – this is because it is not. The type field determines which model a record is but we will be handling that automatically and so do not need to manipulate the field directly. Finally, we place the name of the field in which we are storing our type into the variable $__type_field.

With the above variables in place, we can turn our attention to the methods we need to override. Any methods which involve reading or writing records need to be modified to take our changes into account. For my particular needs, I have found that I needed to override __construct, findAll, findCount, findAllThreaded and save. __construct is modified to supply the tablename to the __construct method of AppModel and, as such, is the simplest modification we will be making. The modifications to the save method are a little more significant by equally easy to accomplish. All that needs doing is to add a reference to the type field and its value to the $data variable before passing it to the parent.

The data retrieval methods findAll, findCount and findAllThreaded require a little more work than the fields detailed above. There are two things that need to be done here: modify the conditions to include a reference to the value of the type field and check for the fields being queried. The latter requires a little more explanation. Since we are storing the information for both objects in one table there are certain fields which belong to one model but not to the other. We need to make sure that we are only grabbing the fields that belong to the model in question. In our example, we need to check that we are not querying the greenlit field if we are querying for an Upcoming model. If the fields are not being queried specifically and $fields is null, then we will substitute our $__fields array for $fields to only return that which belongs to this model. Snippet #3 contains the completed code for our Current model.

Snippet #3: Complete definition for the Current Model.

class Current extends AppModel
{
     var $name = 'Current';
 
     //single table inheritance vars
    var $__table = 'entries';
    var $__fields = array('Current.id', 'Current.title', 'Current.description', 'Current.author_name', 'Current.author_email', 'Current.created', 'Current.greenlit');
    var $__type_field = 'type';
 
    function __construct($id = false,$ignore_table = null, $ds = null)
    {
        $table = $this->__table;
        parent::__construct($id,$table,$ds);
    }
 
    function findAll($conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null)
    {
        if($conditions == null)
        {
            $conditions = $this->name. "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
 
        if($fields == null)
        {
            $fields = $this->__fields;
        }
        else
        {
            foreach($fields AS $field)
            {
                if(!array_search($field,$this->__fields))
                {
                    throw new Exception("Field " . $field . " does not exist in Model " . $this->name);
                }
            }
         }
 
         return parent::findAll($conditions, $fields, $order, $limit, $page, $recursive);
    }
 
    function findCount($conditions = null, $recursive = 0)
    {
        if($conditions == null)
        {
            $conditions = $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        return parent::findCount($conditions, $recursive);
     }
 
    function findAllThreaded($conditions = null, $fields = null, $sort = null)
    {
        if($conditions == null)
        {
            $conditions = $this->__table . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        if($fields == null)
        {
            $fields = $this->__fields;
        }
        else
        {
            foreach($fields AS $field)
            {
                if(!array_search($field,$this->__fields))
                {
                    throw new Exception("Field " . $field . " does not exist in Model " . $this->name);
                }
            }
        }
        return parent::findAllThreaded($conditions, $fields, $sort);
    }
 
    function save($data = null, $validate = true, $fieldList = array())
    {
        $data[$this->name][$this->__type_field] = $this->name;
        return parent::save($data, $validate, $fieldList);
    }
}

Snippet #4 contains the complete code for our Upcoming Model. Notice that it does not reference the greenlit field whereas the Current Model did.

Snippet #4: Complete definition for the Upcoming Model.

class Upcoming extends AppModel
{
    var $name = 'Upcoming';
 
    //single table inheritance vars
    var $__table = 'entries';
    var $__fields = array('Upcoming.id', 'Upcoming.title','Upcoming.description', 'Upcoming.author_name','Upcoming.author_email', 'Upcoming.created');
    var $__type_field = 'type';
 
    function __construct($id = false,$ignore_table = null,$ds = null)
    {
        $table = $this->__table;
        parent::__construct($id,$table,$ds);
    }
 
    function findAll($conditions = null, $fields = null, $order = null, $limit = null, $page = 1, $recursive = null)
    {
        if($conditions == null)
        {
            $conditions = $this->name. "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
 
        if($fields == null)
        {
            $fields = $this->__fields;
        }
        else
        {
            foreach($fields AS $field)
            {
                if(!array_search($field,$this->__fields))
                {
                    throw new Exception("Field " . $field . " does not exist in Model " . $this->name);
                }
            }
        }
 
        return parent::findAll($conditions, $fields, $order, $limit, $page, $recursive);
    }
 
    function findCount($conditions = null, $recursive = 0)
    {
        if($conditions == null)
        {
            $conditions = $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        return parent::findCount($conditions, $recursive);
    }
 
    function findAllThreaded($conditions = null, $fields = null, $sort = null)
    {
        if($conditions == null)
        {
            $conditions = $this->__table . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        else
        {
            $conditions = $conditions . " AND " . $this->name . "." . $this->__type_field . " = '" . $this->name . "'";
        }
        if($fields == null)
        {
            $fields = $this->__fields;
        }
        else
        {
            foreach($fields AS $field)
            {
                if(!array_search($field,$this->__fields))
                {
                    throw new Exception("Field " . $field . " does not exist in Model " . $this->name);
                }
            }
        }
        return parent::findAllThreaded($conditions, $fields, $sort);
    }
 
    function save($data = null, $validate = true, $fieldList = array())
    {
        $data[$this->name][$this->__type_field] = $this->name;
        return parent::save($data, $validate, $fieldList);
    }
}

As you can see, aside from the values of the variables at the top, these two models are nearly identical for our simple example. In a real system you would likely add other methods to your models to handle more than just the simplest of situations. An example here would be for the green-lighting process. A method in the Upcoming model which sets the value of the greenlit field and changes the type field to ‘Current’ would effectively change the model of the record and have it show up where needed. I’ll leave that code as an exercise.

Given the fact that the modified methods are the same in both models, this method lends itself to scaffolding quite easily. One of these days I am going to look into modifying / extending the CakePHP script bake.php to allow for scaffolding Single Table Inheritance models. If someone has a chance to do this before I do, please drop me a line!

Controllers and Views

With the database table and Model classes taken care of it is time to look at the Controllers and Views. What needs to be done specially to handle the Single Table Inheritance? Nothing! This is the realy beauty of a setup like this, you make your changes at the Database and Model levels but your controllers and views are handled like they are for any other models. As far as you are concerned when writing the controllers and views, there are two database tables – one for Upcoming and one for Current – and you can handle them as you would normally.

Caveats

As I mentioned at the beginning of this, not every scenario will be covered by this method. Depending on your needs you may need to override more or less methods from AppModel. I am currently using this on two projects in addition to on this site. If anyone else manages to get some use out of it or if you have any questions/comments/error reports, please feel free to leave a comment below.

About author:

A software developer currently working primarily with Python on Google App Engine.

All entries by Jeffery Read