Foreign Key Associations

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
      foreignType: one

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.