relations.txt

++ Introduction

In Doctrine all record relations are being set with {{hasMany}}, {{hasOne}} methods. Doctrine supports almost all kinds of database relations from simple one-to-one foreign key relations to join table self-referencing relations.

Unlike the column definitions the {{hasMany}} and {{hasOne}} methods are placed within a method called setUp(). Both methods take two arguments: the first argument is a string containing the name of the class and optional alias, the second argument is an array consisting of relation options. The option array contains the following keys:

* **local**, the local field of the relation. Local field is the linked field or fields in the defining class.
* **foreign**, the foreign field of the relation. Foreign field is the linked field or fields in the linked class.
* **refClass**, the name of the reference / join class. This is needed for many-to-many associations.
* **onDelete**, the onDelete integrity action.
* **onUpdate**, the onUpdate integrity action.

So lets take our first example, say we have two classes Forum_Board and Forum_Thread. Here Forum_Board has many Forum_Threads, hence their relation is one-to-many. We don't want to write Forum_ when accessing relations, so we use relation aliases and use the alias Threads.

First lets take a look at the Forum_Board class. It has three columns: name, description and since we didn't specify any primary key, Doctrine auto-creates an id column for it.

We define the relation to the Forum_Thread class by using the hasMany() method. Here the local field is the primary key of the board class whereas the foreign field is the board_id field of the Forum_Thread class.

<code type="php">
class Forum_Board extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 100);
        $this->hasColumn('description', 'string', 5000);
    }
    public function setUp()
    {
        // notice the 'as' keyword here
        $this->hasMany('Forum_Thread as Threads', array('local' => 'id',
                                                        'foreign' => 'board_id'));
    }
}
</code>

Then lets have a peek at the Forum_Thread class. The columns here are irrelevant, but pay attention to how we define the relation. Since each Thread can have only one Board we are using the hasOne() method. Also notice how we once again use aliases and how the local column here is board_id while the foreign column is the id column.

<code type="php">
class Forum_Thread extends Doctrine_Record
{
    public function setTableDefinition() 
    {
        $this->hasColumn('board_id', 'integer', 10);
        $this->hasColumn('title', 'string', 200);
        $this->hasColumn('updated', 'integer', 10);
        $this->hasColumn('closed', 'integer', 1);
    }
    public function setUp() 
    {
        // notice the 'as' keyword here
        $this->hasOne('Forum_Board as Board', array('local' => 'board_id',
                                                    'foreign' => 'id'));
    }
}
</code>

Now we can start using these classes. The same accessors that you've already used for properties are all availible for relations.

<code type="php">
// first create a board
$board = new Forum_Board();
$board->name = 'Some board';

// lets create a new thread
$board->Thread[0]->title = 'new thread';

// save the changes 
$board->save();
</code>

++ Foreign key associations
+++ One-To-One

One-to-one relations are propably the most basic relations. In the following example we have two classes, User and Email with their relation being one-to-one. 

First lets take a look at the Email class. Since we are binding a one-to-one relationship we are using the hasOne() method. Notice how we define the foreign key column (user_id) in the Email class. This is due to a fact that Email is owned by the User class and not the other way around. In fact you should always follow this convention - always place the foreign key in the owned class.

The recommended naming convention for foreign key columns is: [tableName]_[primaryKey]. As here the foreign table is 'user' and its primary key is 'id' we have named the foreign key column as 'user_id'.

<code type="php">
class Email extends Doctrine_Record 
{
    public function setTableDefinition() 
    {
        $this->hasColumn('user_id', 'integer');
        $this->hasColumn('address', 'string', 150);
    }
    public function setUp()
    {
        $this->hasOne('User', array('local' => 'user_id', 'foreign' => 'id'));
    }
}
</code>

The User class is very similar to the Email class. Notice how the local and foreign columns are switched in the hasOne() definition compared to the definition of the Email class.

<code type="php">
class User extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string',50);
        $this->hasColumn('loginname', 'string',20);
        $this->hasColumn('password', 'string',16);
    }
    public function setUp()
    {
        $this->hasOne('Email', array('local' => 'id', 'foreign' => 'user_id'));
    }
}
</code>


+++ One-to-Many, Many-to-One

One-to-Many and Many-to-One relations are very similar to One-to-One relations. The recommended conventions you came in terms with in the previous chapter also apply to one-to-many and many-to-one relations.

In the following example we have two classes: User and Phonenumber. We define their relation as one-to-many (a user can have many phonenumbers). Here once again the Phonenumber is clearly owned by the User so we place the foreign key in the Phonenumber class.

<code type="php">
class User extends Doctrine_Record 
{
    public function setUp()
    {
        $this->hasMany('Phonenumber', array('local' => 'id', 'foreign' => 'user_id'));
    }
    public function setTableDefinition() 
    {
        $this->hasColumn('name', 'string', 50);
        $this->hasColumn('loginname', 'string', 20);
        $this->hasColumn('password', 'string', 16);
    }
}
class Phonenumber extends Doctrine_Record 
{
    public function setTableDefinition() 
    {
        $this->hasColumn('phonenumber', 'string', 50);
        $this->hasColumn('user_id', 'integer');
    }
}
</code>


+++ Tree structure

A tree structure is a self-referencing foreign key relation. The following definition is also called Adjacency List implementation in terms of hierarchical data concepts. 

However this mainly just serves as an example how the self-referencing can be done. The definition above is rarely a good way of expressing hierarchical data, hence you should take a look at chapter [doc hierarchical-data :index :name] for how to set up efficient parent/child relations.

<code type="php">
class Task extends Doctrine_Record 
{
     public function setUp() 
     {
        $this->hasOne('Task as Parent', array('local' => 'parent_id', 'foreign' => 'id'));
        $this->hasMany('Task as Subtask', array('local' => 'id', 'foreign' => 'parent_id'));
    }
    public function setTableDefinition() 
    {
        $this->hasColumn('name', 'string', 100);
        $this->hasColumn('parent_id', 'integer');
    }
}
</code>

++ Join table associations

+++ Many-to-Many

If you are coming from relational database background it may be familiar to you how many-to-many associations are handled: an additional association table is needed.

In many-to-many relations the relation between the two components is always an aggregate relation and the association table is owned by both ends. For example in the case of users and groups: when a user is being deleted, the groups he/she belongs to are not being deleted. However, the associations between this user and the groups he/she belongs to are instead being deleted. This removes the relation between the user and the groups he/she belonged to, but does not remove the user nor the groups.

Sometimes you may not want that association table rows are being deleted when user / group is being deleted. You can override this behaviour by setting the relations to association component (in this case {{Groupuser}}) explicitly. 

In the following example we have Groups and Users of which relation is defined as many-to-many. In this case we also need to define an additional class called {{Groupuser}}.

<code type="php">
class User extends Doctrine_Record 
{
    public function setUp() 
    {
        $this->hasMany('Group', array('local' => 'user_id',    // <- these are the column names
                                      'foreign' => 'group_id', // <- in the association table
            // the following line is needed in many-to-many relations!
                                      'refClass' => 'GroupUser'));

    }
    public function setTableDefinition() 
    {
        $this->hasColumn('name', 'string', 30);
    }
}

class Group extends Doctrine_Record
{
    public function setUp() {
        $this->hasMany('User', array('local' => 'group_id',  // <- these are the column names
                                     'foreign' => 'user_id', // <- in the association table
            // the following line is needed in many-to-many relations!
                                     'refClass' => 'GroupUser'));

        //group is reserved keyword so either do this or enable ATTR_QUOTE_IDENTIFIERS
        $this->setTableName('my_group'); 
    }
    public function setTableDefinition() {
        $this->hasColumn('name', 'string', 30);
    }
}

class GroupUser extends Doctrine_Record
{
    public function setTableDefinition() 
    {
        $this->hasColumn('user_id', 'integer', null, array('primary' => true));
        $this->hasColumn('group_id', 'integer', null, array('primary' => true));
    }
}


$user = new User();

// add two groups
$user->Group[0]->name = 'First Group';

$user->Group[1]->name = 'Second Group';

// save changes into database
$user->save();

// deleting the associations between user and groups it belongs to

$user->Groupuser->delete();

$groups = new Doctrine_Collection($conn->getTable('Group'));

$groups[0]->name = 'Third Group';

$groups[1]->name = 'Fourth Group';

$user->Group[2] = $groups[0];
// $user will now have 3 groups

$user->Group = $groups;
// $user will now have two groups 'Third Group' and 'Fourth Group'

</code>


+++ Self-referencing (Nest relations)
++++ Non-equal nest relations
<code type="php">
class User extends Doctrine_Record 
{
    public function setUp() 
    {
        $this->hasMany('User as Parents', array('local'    => 'parent_id',
                                                'foreign'  => 'child_id',
                                                'refClass' => 'UserReference'
                                                ));

        $this->hasMany('User as Children', array('local'    => 'child_id',
                                                 'foreign'  => 'parent_id',
                                                 'refClass' => 'UserReference'
                                                 ));

    }
    public function setTableDefinition() 
    {
        $this->hasColumn('name', 'string', 30);
    }
}
class UserReference extends Doctrine_Record 
{
    public function setTableDefinition() {
        $this->hasColumn('parent_id', 'integer', null, array('primary' => true));
        $this->hasColumn('child_id', 'integer', null, array('primary' => true));
    }
}
</code>
++++ Equal nest relations

Equal nest relations are perfectly suitable for expressing relations where a class references to itself and the columns within the reference class are equal.

This means that when fetching related records it doesn't matter which column in the reference class has the primary key value of the main class.

The previous clause maybe hard to understand so lets take an example. We define a class called user which can have many friends. Notice here how we use the 'equal' option.

<code type="php">
class User extends Doctrine_Record 
{
    public function setUp() 
    {
        $this->hasMany('User as Friend', array('local'    => 'user1',
                                               'foreign'  => 'user2',
                                               'refClass' => 'UserReference',
                                               'equal'    => true,
                                                ));
    }
    public function setTableDefinition() 
    {
        $this->hasColumn('name', 'string', 30);
    }
}
class UserReference extends Doctrine_Record 
{
    public function setTableDefinition() {
        $this->hasColumn('user1', 'integer', null, array('primary' => true));
        $this->hasColumn('user2', 'integer', null, array('primary' => true));
    }
}
</code>

Now lets define 4 users: Jack Daniels, John Brandy, Mikko Koskenkorva and Stefan Beer with Jack Daniels and John Brandy being buddies and Mikko Koskenkorva being the friend of all of them.

<code type="php">
$daniels = new User();
$daniels->name = 'Jack Daniels';

$brandy = new User();
$brandy->name = 'John Brandy';

$koskenkorva = new User();
$koskenkorva->name = 'Mikko Koskenkorva';

$beer = new User();
$beer->name = 'Stefan Beer';

$daniels->Friend[0] = $brandy;

$koskenkorva->Friend[0] = $daniels;
$koskenkorva->Friend[1] = $brandy;
$koskenkorva->Friend[2] = $beer;

$conn->flush();
</code>

Now if we access for example the friends of John Beer it would return one user 'Mikko Koskenkorva'.


++ Inheritance

Doctrine supports 4 types of inheritance strategies which can be mixed together.

+++ Simple inheritance

Simple inheritance is the simpliest inheritance. In simple inheritance all the child classes share the same columns as the parent.

<code type="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', 'integer', 11);
    }
}

class User extends Entity 
{ }

class Group extends Entity 
{ }
</code>

+++ Class table inheritance

Class table inheritance is the basic inheritance type, yet its internally the most complex one. Class table inheritance allows the child classes to have additional columns compared with the parent.

Internally the parent and the child classes are stored in separate tables, with parent and children containing only their own distinct columns and the shared primary key column(s).

Consider the following class definition.

<code type="php">
class Entity extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 100);
    }
}

class User extends Entity
{
    public function setTableDefinition()
    {
        $this->hasColumn('age', 'integer', 2);
        $this->hasColumn('password', 'string', 16);
    }
}
</code>

When class 'User' is being exported into mysql database Doctrine would issue the following queries:

<code>
CREATE TABLE entity (id INT NOT NULL AUTO_INCREMENT, name VARCHAR(100), PRIMARY KEY(id))
CREATE TABLE user (id INT NOT NULL, age INT, password VARCHAR(16), PRIMARY KEY(id))
</code>

Notice how only the parent has the auto-incremented id column. When no primary keys are set for the entire inheritance tree this is the default strategy Doctrine uses. When setting the primary keys manually all classes in the inheritance tree should share the same primary key definition, with the exception of autoinc/sequential primary key. In class table inheritance only the tree root can contain autoinc/sequential pk .

Whenever you fetch data with DQL from a class that uses class table inheritance, Doctrine is smart enough to produce the necessary joins. Lets say we want to fetch all users with their name starting with letter A. 

<code type="php">
$q = new Doctrine_Query();

$users = $q->from('User u')->where("u.name LIKE 'A%'")->execute();
</code>

Now Doctrine would issue the following query:

<code>
SELECT ... FROM user u LEFT JOIN entity e ON u.id = e.id WHERE u.name LIKE 'A%'
</code>

Doctrine also knows how to perform other operations as multi-table operations. Creating and saving a new user will actually execute two INSERT statements as seen above. Notice how Doctrine is smart enough to attach the newly created entity id for the user record.

<code type="php">
$user = new User();
$user->age = 13;
$user->password = 'secret';
$user->name = 'someone';

$user->save();
</code>

Executes:
<code>
INSERT INTO entity (name) VALUES ('someone')
INSERT INTO user (id, age, password) VALUES (1, 13, 'secret')
</code>

The updates and deletes are also performed to span accross multiple tables. Consider the following example:

<code type="php">
$user->age = 14;
$user->password = 'newpassword';
$user->name = 'newname';

$user->save();

$user->delete();
</code>

The example would execute the following statements:
<code>
UPDATE entity SET name = 'newname' WHERE id = 1
UPDATE user SET age = 14, password = 'newpassword' WHERE id = 1

DELETE FROM entity WHERE id = 1
DELETE FROM user WHERE id = 1
</code>




+++ Concrete inheritance

Concrete inheritance is similar to class table inheritance in a way that it creates separate tables for child classes. However in concrete inheritance each class generates a table which contains all columns (including inherited columns). In order to use concrete inheritance instead of the default class table inheritance you'll need to add explicit parent::setTableDefinition() calls to child classes as shown above.

<code type="php">
class TextItem extends Doctrine_Record
{
    public function setTableDefinition() 
    {
        $this->hasColumn('topic', 'string', 100);
    }
}

class Comment extends TextItem
{
    public function setTableDefinition() 
    {
        parent::setTableDefinition();    

        $this->hasColumn('content', 'string', 300);
    }
}
</code>


In concrete inheritance you don't necessarily have to define additional columns, but in order to make Doctrine create separate tables for each class you'll have to make iterative setTableDefinition() calls.

In the following example we have three database tables called {{entity}}, {{user}} and {{group}}. Users and groups are both entities. The only thing we have to do is write 3 classes ({{Entity}}, {{Group}} and {{User}}) and make iterative {{setTableDefinition}} method calls.

<code type="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', 'integer', 11);
    }
}

class User extends Entity 
{
    public function setTableDefinition() 
    {
        // the following method call is needed in
        // one-table-one-class inheritance
        parent::setTableDefinition();
    }
}

class Group extends Entity 
{
    public function setTableDefinition() 
    {
        // the following method call is needed in
        // one-table-one-class inheritance
        parent::setTableDefinition();
    }
}
</code>

Even though concrete inheritance duplicates schema definitions its in many cases much better inheritance strategy to use than class table inheritance. In class table inheritance an inheritance tree of depth N uses N number of joins for SELECTs and executes N number of statements for each manipulation operation whereas in concrete inheritance every operation can be invoked by single statement.


+++ Column aggregation inheritance

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
call the {{Doctrine_Table::setSubclasses()}} method from the parent class.

<code type="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', 'integer', 11);

        // this column is used for column
        // aggregation inheritance
        $this->hasColumn('type', 'integer', 11);
        $this->setSubclasses(array(
            'User'  => array('type' => 1),
            'Group' => array('type' => 2)
        ));
    }
}

class User extends Entity {}
class Group extends Entity {}

</code>

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.

<code type="php">
$user = new User();
$user->name = 'Bjarte S. Karlsen';
$user->username = 'meus';
$user->password = 'rat';
$user->save();

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

$q = new Doctrine_Query();
$user = $q->from('Entity')->where('id = ?')->fetchOne(array($user->id));
assert($user instanceOf User);

$q = new Doctrine_Query();
$group = $q->from('Entity')->where('id = ?')->fetchOne(array($group->id));
assert($group instanceOf Group);
</code>

++ Foreign key constraints
+++ Introduction

A foreign key constraint specifies that the values in a column (or a group of columns) must match the values appearing in some row of another table. In other words foreign key constraints maintain the referential integrity between two related tables.

Say you have the product table with the following definition:

<code type="php">
class Product extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('id', 'integer', null, 'primary');
        $this->hasColumn('name', 'string');
        $this->hasColumn('price', 'decimal', 18);
    }
}
</code>

Let's also assume you have a table storing orders of those products. We want to ensure that the order table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:

<code type="php">
class Order extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('order_id', 'integer', null, 'primary');
        $this->hasColumn('product_id', 'integer');
        $this->hasColumn('quantity', 'integer');
    }
    public function setUp()
    {
        $this->hasOne('Product', array('local' => 'product_id', 'foreign' => 'id'));

        // foreign key columns should *always* have indexes

        $this->index('product_id', array('fields' => 'product_id'));
    }
}
</code>

When exported the class {{Order}} would execute the following SQL:

<code type="sql">
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_id integer REFERENCES products (id),
    quantity integer,
    INDEX product_id_idx (product_id)
)
</code>

Now it is impossible to create orders with product_no entries that do not appear in the products table.

We say that in this situation the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.


+++ Integrity actions

//CASCADE//:
Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

//SET NULL// :
Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

//NO ACTION// :
In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table.

//RESTRICT// :
Rejects the delete or update operation for the parent table. NO ACTION and RESTRICT are the same as omitting the ON DELETE or ON UPDATE clause.

//SET DEFAULT// :

In the following example we define two classes, User and Phonenumber with their relation being one-to-many. We also add a foreign key constraint with onDelete cascade action. This means that everytime a users is being deleted its associated phonenumbers will also be deleted.

<code type="php">
class User extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 50);
        $this->hasColumn('loginname', 'string', 20);
        $this->hasColumn('password', 'string', 16);
    }
    public function setUp()
    {
        $this->index('id_idx', array('fields' => 'id'));

        $this->hasMany('Phonenumber', array('local' => 'id', 
                                            'foreign' => 'user_id'));
    }
}
class Phonenumber extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('phonenumber', 'string', 50);
        $this->hasColumn('user_id', 'integer');
    }
    public function setUp()
    {
        $this->index('user_id_idx', array('fields' => 'user_id'));

        $this->hasOne('User', array('local' => 'user_id',
                                    'foreign' => 'id',
                                    'onDelete' => 'CASCADE'));
    }
}
</code>