Yii 1.1: Single table inheritance

39 followers

Relational databases do not support inheritance so if we need to represent it, we have to somehow store meta info while keeping performance by minimizing JOINs. One way to solve this problem is using single table inheritance. All fields for the whole class tree are stored in a single table. Class name is stored in the type field of the same table.

Schema will be the following:

CREATE TABLE `car` (
    `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `type` varchar(100) NOT NULL,
    PRIMARY KEY (`id`)
);

Base AR model with overridden instantiate method:

class Car extends CActiveRecord {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function tableName() {
        return 'car';
    }
 
    /**
     * We're overriding this method to fill findAll() and similar method result
     * with proper models.
     *
     * @param array $attributes
     * @return Car
     */
    protected function instantiate($attributes){
        switch($attributes['type']){
            case 'sport':
                $class='SportCar';
            break;
            case 'family':
                $class='FamilyCar';
            break;
            default:
                $class=get_class($this);
        }
        $model=new $class(null);
        return $model;
    }
}

And, finally, our children:

class SportCar extends Car {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function defaultScope(){
        return array(
            'condition'=>"type='sport'",
        );
    }
}
 
class FamilyCar extends Car {
    static function model($className=__CLASS__) {
        return parent::model($className);
    }
 
    function defaultScope(){
        return array(
            'condition'=>"type='family'",
        );
    }
}

In the above we've overridden defaultScope so, for example, when you'll search using FamilyCar model, it will return only family cars.

Total 6 comments

#10722 report it
marcovtwout at 2012/11/19 04:08am
Create/update example

Useful wiki article, was looking for this :)

Do we have examples for create/update forms? What I mean: a single (CForm-)form that allows selecting type, and displaying fields for that model.

#9774 report it
IainG at 2012/09/09 08:30pm
Add alias to default Scope

I've been using this extensively, and it works well apart from one small problem - when you do a CDBCriteria-based query involving two different Single Table Inherited models, you get an "ambiguous column name" SQL error.

The way round this is to change the default scope to include the table alias by adding "t." e.g.

function defaultScope(){
        return array(
            'condition'=>"t.type='family'",
        );
    }
#8836 report it
Ssidelnikov at 2012/07/02 07:53am
Re: what's about save and update

I faced the same problem - the type field is saved empty. On solution for this is setting the type in the beforeValidate function of the child or parent classes.

For the child class it may be:

protected function beforeValidate() {
    if(empty($this->type))
        $this->type = 'sport';
    return parent::beforeValidate();
}

For the parent class I wrote something like this (in this case the child classes are not changed):

protected function beforeValidate() {
    if(empty($this->type)) {
        $childClass = strtolower(get_called_class());
        // The child classes have the 'Car' tail which is trimmed here
        $type = substr($childClass, 0, strlen($childClass) - strlen('Car'));
        $this->type = $type;
    }
    return parent::beforeValidate();
}
#4270 report it
Mike at 2011/06/21 11:38am
Attributes from related table

Thanks Alex for this very nice tutorial, really helpful.

I now have one situation, where the type is stored in a 1:1 related table. I first tried to always JOIN that record through with+together in defaultScope - but i can't access this value in instantiate().

So i suppose i always need an additional query in instantiate(). Something i tried to avoid. Any workaround for this?

#4165 report it
yiqing95 at 2011/06/11 10:24pm
what's about save and update

use this tech , when i insert a new record which is subclass of car ,let's say SportCar , if i should manually give the type field a value 'sport', another word ,except query operation the insert , update scenario do we need care about the 'type' attribute ?

#4108 report it
bglee at 2011/06/06 11:41pm
Table Inheritance: One Exception To The Rule

First, very nice article! For what it's worth, I just wanted to point out that Postgresql actually supports table inheritance and nicely. I came across the feature years ago, although I never used it in practice. I've spent most of my time in Oracle databases. I have no idea how it is implemented or if there is a performance hit. Oracle (since about version 9.x) has included so-called object-relational features which also allow you to do inheritance. I don't know if the method improved since then, but it was a very awkward implementation.

Leave a comment

Please to leave your comment.

Write new article