Column Aggregation

In the following example we have one database table called entity. Users and groups are both entities and they share the same database table.

The entity table has a column called type which tells whether an entity is a group or a user. Then we decide that users are type 1 and groups type 2.

The only thing we have to do is to create 3 records (the same as before) and add the call to the Doctrine_Table::setSubclasses() method from the parent class.

// models/Entity.php

class Entity extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 30);
        $this->hasColumn('username', 'string', 20);
        $this->hasColumn('password', 'string', 16);
        $this->hasColumn('created_at', 'timestamp');
        $this->hasColumn('update_at', 'timestamp');

        $this->setSubclasses(array(
                'User'  => array('type' => 1),
                'Group' => array('type' => 2)
            )
        );
    }
}

// models/User.php
class User extends Entity
{ }

// models/Group.php
class Group extends Entity
{ }

Here is the same example in YAML format. You can read more about YAML in the YAML Schema Files chapter:

---
Entity:
  columns:
    username: string(20)
    password: string(16)
    created_at: timestamp
    updated_at: timestamp

User:
  inheritance:
    extends: Entity
    type: column_aggregation
    keyField: type
    keyValue: 1

Group:
  inheritance:
    extends: Entity
    type: column_aggregation
    keyField: type
    keyValue: 2

Lets check the SQL that is generated by the above models:

// test.php

// ...
$sql = Doctrine_Core::generateSqlFromArray(array('Entity', 'User', 'Group'));
echo $sql[0];

The above code would output the following SQL query:

CREATE TABLE entity (id BIGINT AUTO_INCREMENT, 
username VARCHAR(20), 
password VARCHAR(16), 
created_at DATETIME, 
updated_at DATETIME, 
type VARCHAR(255), 
PRIMARY KEY(id)) ENGINE = INNODB

Notice how the type column was automatically added. This is how column aggregation inheritance knows which model each record in the database belongs to.

This feature also enable us to query the Entity table and get a User or Group object back if the returned object matches the constraints set in the parent class.

See the code example below for an example of this. First lets save a new User object:

// test.php

// ...
$user = new User();
$user->name = 'Bjarte S. Karlsen';
$user->username = 'meus';
$user->password = 'rat';
$user->save();

Now lets save a new Group object:

// test.php

// ...
$group = new Group();
$group->name = 'Users';
$group->username = 'users';
$group->password = 'password';
$group->save();

Now if we query the Entity model for the id of the User we created, the Doctrine_Query will return an instance of User.

// test.php

// ...
$q = Doctrine_Query::create()
    ->from('Entity e')
    ->where('e.id = ?');

$user = $q->fetchOne(array($user->id));

echo get_class($user); // User

If we do the same thing as above but for the Group record, it will return an instance of Group.

// test.php

// ...
$q = Doctrine_Query::create()
    ->from('Entity e')
    ->where('e.id = ?');

$group = $q->fetchOne(array($group->id));

echo get_class($group); // Group

The above is possible because of the type column. Doctrine knows which class each record was created by, so when data is being hydrated it can be hydrated in to the appropriate sub-class.

We can also query the individual User or Group models:

$q = Doctrine_Query::create()
    ->select('u.id')
    ->from('User u');

echo $q->getSqlQuery();

The above call to getSql() would output the following SQL query:

SELECT 
e.id AS e__id 
FROM entity e 
WHERE (e.type = '1')

Notice how the type condition was automatically added to the query so that it will only return records that are of type User.