To install Doctrine via SVN is very easy. You can download any version of Doctrine from the SVN server: http://svn.doctrine-project.org

To check out a specific version you can use the following command from your terminal:

svn co http://svn.doctrine-project.org/branches/1.0 .

If you do not have a SVN client, chose one from the list below. Find the Checkout option and enter http://svn.doctrine-project.org/1.0 in the path or repository url parameter. There is no need for a username or password to check out Doctrine.

• TortoiseSVN a Windows application that integrates into Windows Explorer
• svnx a Mac OS X GUI svn application
• Eclipse has SVN integration through the subeclipse plugin
• Versions a subversion client for the mac

Updating Doctrine with SVN is just as easy as installing. Simply execute the following command from your terminal:

$ svn update

You can read about the PHP autoloading on the php website. Using the autoloader allows us to lazily load classes as they are requested instead of pre-loading all classes. This is a huge benefit to performance.

The way the Doctrine autoloader works is simple. Because our class names and paths are related, we can determine the path to a Doctrine class based on its name.

Imagine we have a class named Doctrine_Some_Class and we instantiate an instance of it:

$class = new Doctrine_Some_Class();

The above code will trigger a call to the Doctrine::autoload() function and pass it the name of the class instantiated. The class name string is manipulated and transformed in to a path and required. Below is some pseudo code that shows how the class is found and required:

class Doctrine
{
    public function autoload($className)
    {
        $classPath = str_replace('_', '/', $className) . '.php';
        $path = '/path/to/doctrine/' . $classPath;
        require_once($path);
        return true;
    }
}

In the above example the Doctrine_Some_Class can be found at /path/to/doctrine/Doctrine/Some/Class.php.

Obviously the real Doctrine::autoload() function is a bit more complex and has some error checking to ensure the file exists but the above code demonstrates how it works.

Let's consider we have a mysql database called doctrine_test with a single table named user. The user table has been created with the following sql statement:

CREATE TABLE user (
   id bigint(20) NOT NULL auto_increment,
   first_name varchar(255) default NULL,
   last_name varchar(255) default NULL,
   username varchar(255) default NULL,
   password varchar(255) default NULL,
   type varchar(255) default NULL,
   is_active tinyint(1) default '1',
   is_super_admin tinyint(1) default '0',
   created_at TIMESTAMP,
   updated_at TIMESTAMP,
   PRIMARY KEY  (id)
) ENGINE=InnoDB

Now we would like to convert it into Doctrine_Record class. With Doctrine this is easy! Remember our test script we created in the Getting Started chapter? We're going to use that generate our models.

First we need to modify our bootstrap.php to use the MySQL database instead of sqlite memory:

// bootstrap.php

// ...
$conn = Doctrine_Manager::connection('mysql://root:mys3cr3et@localhost/doctrine_test', 'doctrine');
// ...

You can use the $conn->createDatabase() method to create the database if it does not already exist and the connected user has permission to create databases. Then use the above provided CREATE TABLE statement to create the table.

Now we need a place to store our generated classes so lets create a directory named models in the doctrine_test directory:

$ mkdir doctrine_test/models

Now we just need to add the code to our test.php script to generate the model classes:

// test.php

// ...
Doctrine::generateModelsFromDb('models', array('doctrine'), array('generateTableClasses' => true));

The generateModelsFromDb method only requires one parameter and it is the import directory (the directory where the generated record files will be written to). The second argument is an array of database connection names to generate models for, and the third is the array of options to use for the model building.

That's it! Now there should be a file called BaseUser.php in your doctrine_test/models/generated directory. The file should look like the following:

// models/generated/BaseUser.php

/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
abstract class BaseUser extends Doctrine_Record
{
  public function setTableDefinition()
  {
    $this->setTableName('user');
    $this->hasColumn('id', 'integer', 8, array('type' => 'integer', 'length' => 8, 'primary' => true, 'autoincrement' => true));
    $this->hasColumn('first_name', 'string', 255, array('type' => 'string', 'length' => 255));
    $this->hasColumn('last_name', 'string', 255, array('type' => 'string', 'length' => 255));
    $this->hasColumn('username', 'string', 255, array('type' => 'string', 'length' => 255));
    $this->hasColumn('password', 'string', 255, array('type' => 'string', 'length' => 255));
    $this->hasColumn('type', 'string', 255, array('type' => 'string', 'length' => 255));
    $this->hasColumn('is_active', 'integer', 1, array('type' => 'integer', 'length' => 1, 'default' => '1'));
    $this->hasColumn('is_super_admin', 'integer', 1, array('type' => 'integer', 'length' => 1, 'default' => '0'));
    $this->hasColumn('created_at', 'timestamp', null, array('type' => 'timestamp', 'notnull' => true));
    $this->hasColumn('updated_at', 'timestamp', null, array('type' => 'timestamp', 'notnull' => true));
  }
}

You should also have a file called User.php in your doctrine_test/models directory. The file should look like the following:

// models/User.php

/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class User extends BaseUser
{

}

Doctrine will automatically generate a skeleton Doctrine_Table class for the model at doctrine_test/models/UserTable.php because we passed the option generateTableClasses with a value of true. The file should look like the following:

// models/UserTable.php

/**
 * This class has been auto-generated by the Doctrine ORM Framework
 */
class UserTable extends Doctrine_Table
{

}

You can place custom functions inside the User and UserTable classes to customize the functionality of your models. Below are some examples:

// models/User.php

// ...
class User extends BaseUser
{
    public function setPassword($password)
    {
        return $this->_set('password', md5($password));
    }
}

In order for the above password accessor overriding to work properly you must enabled the auto_accessor_override attribute in your bootstrap.php file like done below.

// bootstrap.php

// ...
$manager->setAttribute(Doctrine::ATTR_AUTO_ACCESSOR_OVERRIDE, true);

Now when you try and set a users password it will be md5 encrypted. First we need to modify our bootstrap.php file to include some code for autoloading our models from the models directory:

// bootstrap.php

// ...
Doctrine::loadModels('models');

The model loading is fully explained later in the Autoloading Models section of this chapter.

Now we can modify test.php to include some code which will test the changes we made to the User model:

// test.php

// ...

$user = new User();
$user->username = 'jwage';
$user->password = 'changeme';

echo $user->password; // outputs md5 hash and not changeme

Now when you execute test.php from your terminal you should see the following:

$ php test.php
4cb9c8a8048fd02294477fcb1a41191a

Here is an example of some custom functions you might add to the UserTable class:

// models/UserTable.php

// ...
class UserTable extends Doctrine_Table
{
    public function getCreatedToday()
    {
        $today = date('Y-m-d h:i:s', strtotime(date('Y-m-d')));
        return $this->createQuery('u')
            ->where('u.created_at > ?', $today)
            ->execute();
    }
}

In order for custom Doctrine_Table classes to be loaded you must enable the autoload_table_classes attribute in your bootstrap.php file like done below.

// boostrap.php

// ...
$manager->setAttribute(Doctrine::ATTR_AUTOLOAD_TABLE_CLASSES, true);

Now you have access to this function when you are working with the UserTable instance:

// test.php

// ...
$usersCreatedToday = Doctrine::getTable('User')->getCreatedToday();

All DBMS provide multiple choice of data types for the information that can be stored in their database table fields. However, the set of data types made available varies from DBMS to DBMS.

To simplify the interface with the DBMS supported by Doctrine, a base set of data types was defined. Applications may access them independently of the underlying DBMS.

The Doctrine applications programming interface takes care of mapping data types when managing database options. It is also able to convert that is sent to and received from the underlying DBMS using the respective driver.

The following data type examples should be used with Doctrine's createTable() method. The example array at the end of the data types section may be used with createTable() to create a portable table on the DBMS of choice (please refer to the main Doctrine documentation to find out what DBMS back ends are properly supported). It should also be noted that the following examples do not cover the creation and maintenance of indices, this chapter is only concerned with data types and the proper usage thereof.

It should be noted that the length of the column affects in database level type as well as application level validated length (the length that is validated with Doctrine validators).

Example 1. Column named 'content' with type 'string' and length 3000 results in database type 'TEXT' of which has database level length of 4000. However when the record is validated it is only allowed to have 'content' -column with maximum length of 3000.

Example 2. Column with type 'integer' and length 1 results in 'TINYINT' on many databases.

In general Doctrine is smart enough to know which integer/string type to use depending on the specified length.

Within the Doctrine API there are a few modifiers that have been designed to aid in optimal table design. These are:

• The notnull modifiers
• The length modifiers
• The default modifiers
• unsigned modifiers for some field definitions, although not all DBMS's support this modifier for integer field types.
• collation modifiers (not supported by all drivers)
• fixed length modifiers for some field definitions.

Building upon the above, we can say that the modifiers alter the field definition to create more specific field types for specific usage scenarios. The notnull modifier will be used in the following way to set the default DBMS NOT NULL Flag on the field to true or false, depending on the DBMS's definition of the field value: In PostgreSQL the "NOT NULL" definition will be set to "NOT NULL", whilst in MySQL (for example) the "NULL" option will be set to "NO". In order to define a "NOT NULL" field type, we simply add an extra parameter to our definition array (See the examples in the following section)

'sometime' = array(
    'type'    => 'time',
    'default' => '12:34:05',
    'notnull' => true,
),

Using the above example, we can also explore the default field operator. Default is set in the same way as the notnull operator to set a default value for the field. This value may be set in any character set that the DBMS supports for text fields, and any other valid data for the field's data type. In the above example, we have specified a valid time for the "Time" data type, '12:34:05'. Remember that when setting default dates and times, as well as datetimes, you should research and stay within the epoch of your chosen DBMS, otherwise you will encounter difficult to diagnose errors!

'sometext' = array(
    'type'   => 'string',
    'length' => 12,
),

The above example will create a character varying field of length 12 characters in the database table. If the length definition is left out, Doctrine will create a length of the maximum allowable length for the data type specified, which may create a problem with some field types and indexing. Best practice is to define lengths for all or most of your fields.

The boolean data type represents only two values that can be either 1 or 0. Do not assume that these data types are stored as integers because some DBMS drivers may implement this type with single character text fields for a matter of efficiency. Ternary logic is possible by using null as the third possible value that may be assigned to fields of this type.

The next several examples are not meant for you to use and give them a try. They are simply for demonstrating purposes to show you how to use the different Doctrine data types using PHP code or YAML schema files.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('booltest', 'boolean');
    }
}

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

---
Test:
  columns:
    booltest: boolean

The integer type is the same as integer type in PHP. It may store integer values as large as each DBMS may handle.

Fields of this type may be created optionally as unsigned integers but not all DBMS support it. Therefore, such option may be ignored. Truly portable applications should not rely on the availability of this option.

The integer type maps to different database type depending on the column length.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('integertest', 'integer', 4, array(
                'unsigned' => true
            )
        );
    }
}

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

---
Test:
  columns:
    integertest:
      type: integer(4)
      unsigned: true

The float data type may store floating point decimal numbers. This data type is suitable for representing numbers withina large scale range that do not require high accuracy. The scale and the precision limits of the values that may be stored in a database depends on the DBMS that it is used.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('floattest', 'float');
    }
}

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

---
Test:
  columns:
    floattest: float

The decimal data type may store fixed precision decimal numbers. This data type is suitable for representing numbers that require high precision and accuracy.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('decimaltest', 'decimal');
    }
}

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

---
Test:
  columns:
    decimaltest: decimal

You can specify the length of the decimal just like you would set the length of any other column and you can specify the scale as an option in the third argument:

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('decimaltest', 'decimal', 18, array(
                'scale' => 2
            )
        );
    }
}

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

---
Test:
  columns:
    decimaltest:
      type: decimal(18)
      scale: 2

The text data type is available with two options for the length: one that is explicitly length limited and another of undefined length that should be as large as the database allows.

The length limited option is the most recommended for efficiency reasons. The undefined length option allows very large fields but may prevent the use of indexes, nullability and may not allow sorting on fields of its type.

The fields of this type should be able to handle 8 bit characters. Drivers take care of DBMS specific escaping of characters of special meaning with the values of the strings to be converted to this type.

By default Doctrine will use variable length character types. If fixed length types should be used can be controlled via the fixed modifier.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('stringtest', 'string', 200, array(
                'fixed' => true
            )
        );
    }
}

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

---
Test:
  columns:
    stringtest:
      type: string(200)
      fixed: true

This is the same as the 'array' type in PHP.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('arraytest', 'array', 10000);
    }
}

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

---
Test:
  columns:
    arraytest: array(10000)

Doctrine supports objects as column types. Basically you can set an object to a field and Doctrine handles automatically the serialization / unserialization of that object.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('objecttest', 'object');
    }
}

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

---
Test:
  columns:
    objecttest: object

The array and object types simply serialize the data when persisting to the database and unserialize the data when pulling from the database.

Blob (Binary Large OBject) data type is meant to store data of undefined length that may be too large to store in text fields, like data that is usually stored in files.

Blob fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS supports a feature usually known as "full text search".

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('blobtest', 'blob');
    }
}

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

---
Test:
  columns:
    blobtest: blob

Clob (Character Large OBject) data type is meant to store data of undefined length that may be too large to store in text fields, like data that is usually stored in files.

Clob fields are meant to store only data made of printable ASCII characters whereas blob fields are meant to store all types of data.

Clob fields are usually not meant to be used as parameters of query search clause (WHERE) unless the underlying DBMS supports a feature usually known as "full text search".

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('clobtest', 'clob');
    }
}

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

---
Test:
  columns:
    clobtest: clob

The timestamp data type is a mere combination of the date and the time of the day data types. The representation of values of the time stamp type is accomplished by joining the date and time string values in a single string joined by a space. Therefore, the format template is YYYY-MM-DD HH:MI:SS. The represented values obey the same rules and ranges described for the date and time data types.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('timestamptest', 'timestamp');
    }
}

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

---
Test:
  columns:
    timestamptest: timestamp

The time data type may represent the time of a given moment of the day. DBMS independent representation of the time of the day is also accomplished by using text strings formatted according to the ISO-8601 standard.

The format defined by the ISO-8601 standard for the time of the day is HH:MI:SS where HH is the number of hour the day from 00 to 23 and MI and SS are respectively the number of the minute and of the second from 00 to 59. Hours, minutes and seconds numbered below 10 should be padded on the left with 0.

Some DBMS have native support for time of the day formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between time values as well sort query results by fields of this type.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('timetest', 'time');
    }
}

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

---
Test:
  columns:
    timetest: time

The date data type may represent dates with year, month and day. DBMS independent representation of dates is accomplished by using text strings formatted according to the IS0-8601 standard.

The format defined by the ISO-8601 standard for dates is YYYY-MM-DD where YYYY is the number of the year (Gregorian calendar), MM is the number of the month from 01 to 12 and DD is the number of the day from 01 to 31. Months or days numbered below 10 should be padded on the left with 0.

Some DBMS have native support for date formats, but for others the DBMS driver may have to represent them as integers or text values. In any case, it is always possible to make comparisons between date values as well sort query results by fields of this type.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('datetest', 'date');
    }
}

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

---
Test:
  columns:
    datetest: date

Doctrine has a unified enum type. The possible values for the column can be specified on the column definition with Doctrine_Record::hasColumn()

If you wish to use native enum types for your DBMS if it supports it then you must set the following attribute:

$conn->setAttribute(Doctrine::ATTR_USE_NATIVE_ENUM, true);

Here is an example of how to specify the enum values:

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('enumtest', 'enum', null,
            array('values' => array('php', 'java', 'python'))
        );
    }
}

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

---
Test:
  columns:
    enumtest:
      type: enum
      values: [php, java, python]

Gzip datatype is the same as string except that its automatically compressed when persisted and uncompressed when fetched. This datatype can be useful when storing data with a large compressibility ratio, such as bitmap images.

class Test extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('gziptest', 'gzip');
    }
}

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

---
Test:
  columns:
    gziptest: gzip

The family of php functions for compressing are used internally for compressing and uncompressing the contents of the gzip column type.

One-to-one relations are probably 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'.

// models/Email.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'
            )
        );
    }
}

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

---
# schema.yml

# ...
Email:
  columns:
    user_id: integer
    address: string(150)
  relations:
    User:
      local: user_id
      foreign: id

When using YAML schema files it is not required to specify the relationship on the opposite end(User) because the relationship is automatically flipped and added for you. The relationship will be named the name of the class. So in this case the relationship on the User side will be called Email and will be many. If you wish to customize this you can use the foreignAlias and foreignType options.

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

// models/User.php

class User extends BaseUser
{
    public function setUp()
    {
        parent::setUp();

        $this->hasOne('Email', array(
                'local' => 'id',
                'foreign' => 'user_id'
            )
        );
    }
}

Notice how we override the setUp() method and call parent::setUp(). This is because the BaseUser class which is generated from YAML or from an existing database contains the main setUp() method and we override it in the User class to add an additional relationship.

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

---
# schema.yml

# ...
User:
# ...
  relations:
    # ...
    Email:
      local: id
      foreign: user_id

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.

// models/User.php

class User extends BaseUser
{
    public function setUp()
    {
        parent::setUp();

        // ...

        $this->hasMany('Phonenumber as Phonenumbers', array(
                'local' => 'id',
                'foreign' => 'user_id'
            )
        );
    }
}

// models/Phonenumber.php

class Phonenumber extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('user_id', 'integer');
        $this->hasColumn('phonenumber', 'string', 50);
    }

    public function setUp()
    {
        $this->hasOne('User', array(
                'local' => 'user_id',
                'foreign' => 'id'
            )
        );
    }
}

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

---
# schema.yml

# ...
User:
# ...
  relations:
    # ...
    Phonenumbers:
      type: many
      class: Phonenumber
      local: id
      foreign: user_id

Phonenumber:
  columns:
    user_id: integer
    phonenumber: string(50)
  relations:
    User:
      local: user_id
      foreign: id

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.

// models/Task.php

class Task extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string', 100);
        $this->hasColumn('parent_id', 'integer');
    }

    public function setUp()
    {
        $this->hasOne('Task as Parent', array(
                'local' => 'parent_id',
                'foreign' => 'id'
            )
        );

        $this->hasMany('Task as Subtasks', array(
                'local' => 'id',
                'foreign' => 'parent_id'
            )
        );
    }
}

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

---
# schema.yml

# ...
Task:
  columns:
    name: string(100)
    parent_id: integer
  relations:
    Parent:
      class: Task
      local: parent_id
      foreign: id
      foreignAlias: Subtasks

The above implementation is purely an example and is not the most efficient way to store and retrieve hierarchical data. Check the NestedSet behavior included in Doctrine for the recommended way to deal with hierarchical data.

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 behavior 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.

class User extends BaseUser
{
    public function setUp()
    {
        parent::setUp();

        // ...

        $this->hasMany('Group as Groups', array(
                'local' => 'user_id',
                'foreign' => 'group_id',
                'refClass' => 'UserGroup'
            )
        );
    }
}

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

---
User:
# ...
  relations:
    # ...
    Groups:
      class: Group
      local: user_id
      foreign: group_id
      refClass: UserGroup

The above refClass option is required when setting up many-to-many relationships.

// models/Group.php

class Group extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->setTableName('groups');
        $this->hasColumn('name', 'string', 30);
    }

    public function setUp()
    {
        $this->hasMany('User as Users', array(
                'local' => 'group_id',
                'foreign' => 'user_id',
                'refClass' => 'UserGroup'
            )
        );
    }
}

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

---
# schema.yml

# ...
Group:
  tableName: groups
  columns:
    name: string(30)
  relations:
    Users:
      class: User
      local: group_id
      foreign: user_id
      refClass: UserGroup

Please note that group is a reserved keyword so that is why we renamed the table to groups using the setTableName method. The other option is to turn on identifier quoting using the Doctrine::ATTR_QUOTE_IDENTIFIERS attribute so that the reserved word is escaped with quotes.

$manager->setAttribute(Doctrine::Doctrine::ATTR_QUOTE_IDENTIFIERS, true);

// models/UserGroup.php

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

        $this->hasColumn('group_id', 'integer', null, array(
                'primary' => true
            )
        );
    }
}

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

---
# schema.yml

# ...
UserGroup:
  columns:
    user_id:
      type: integer
      primary: true
    group_id:
      type: integer
      primary: true

Notice how the relationship is bi-directional. Both User has many Group and Group has many User. This is required by Doctrine in order for many-to-many relationships to fully work.

Now lets play around with the new models and create a user and assign it some groups. First create a new User instance:

// test.php

// ...
$user = new User();

Now add two new groups to the User:

// test.php

// ...
$user->Groups[0]->name = 'First Group';

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

Now you can save the groups to the database:

// test.php

// ...
$user->save();

Now you can delete the associations between user and groups it belongs to:

// test.php

// ...
$user->UserGroup->delete();

$groups = new Doctrine_Collection(Doctrine::getTable('Group'));

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

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

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

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

$user->save();

Now if we inspect the $user object data with the Doctrine_Record::toArray():

// test.php

// ...
print_r($user->toArray(true));

The above example would produce the following output:

$ php test.php
Array
(
    [id] => 1
    [is_active] => 1
    [is_super_admin] => 0
    [first_name] =>
    [last_name] =>
    [username] => default username
    [password] =>
    [type] =>
    [created_at] => 2009-01-20 16:48:57
    [updated_at] => 2009-01-20 16:48:57
    [Groups] => Array
        (
            [0] => Array
                (
                    [id] => 3
                    [name] => Third Group
                )

            [1] => Array
                (
                    [id] => 4
                    [name] => Fourth Group
                )

        )

    [UserGroup] => Array
        (
        )

)

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:

// models/Product.php

class Product extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->hasColumn('name', 'string');
        $this->hasColumn('price', 'decimal', 18);
        $this->hasColumn('discounted_price', 'decimal', 18);
    }

    public function setUp()
    {
        $this->hasMany('Order as Orders', array(
                'local' => 'id',
                'foreign' => 'product_id'
            )
        );
    }
}

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

---
# schema.yml

# ...
Product:
  columns:
    name:
      type: string
    price:
      type: decimal(18)
    discounted_price:
      type: decimal(18)
  relations:
    Orders:
      class: Order
      local: id
      foreign: product_id

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:

// models/Order.php

class Order extends Doctrine_Record
{
    public function setTableDefinition()
    {
        $this->setTableName('orders');
        $this->hasColumn('product_id', 'integer');
        $this->hasColumn('quantity', 'integer');
    }

    public function setUp()
    {
        $this->hasOne('Product', array(
                'local' => 'product_id',
                'foreign' => 'id'
            )
        );
    }
}

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

---
# schema.yml

# ...
Order:
  tableName: orders
  columns:
    product_id: integer
    quantity: integer
  relations:
    Product:
      local: product_id
      foreign: id

Foreign key columns are automatically indexed by Doctrine to ensure optimal performance when issuing queries involving the foreign key.

When exported the class Order would execute the following SQL:

CREATE TABLE orders (
    id integer PRIMARY KEY auto_increment,
    product_id integer REFERENCES products (id),
    quantity integer,
    INDEX product_id_idx (product_id)
)

Now it is impossible to create orders with a product_id that does not appear in the product 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.

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 every time a user is being deleted its associated phonenumbers will also be deleted.

The integrity constraints listed above are case sensitive and must be in upper case when being defined in your schema. Below is an example where the database delete cascading is used.

class Phonenumber extends Doctrine_Record
{
    // ...

    public function setUp()
    {
        parent::setUp();

        // ...

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

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

---
# schema.yml

# ...
Phonenumber:
# ...
  relations:
    # ...
    User:
      local: user_id
      foreign: id
      onDelete: CASCADE

Notice how the integrity constraints are placed on the side where the foreign key exists. This is required in order for the integrity constraints to be exported to your database properly.