Join Table Associations

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_Core::ATTR_QUOTE_IDENTIFIER attribute so that the reserved word is escaped with quotes.

$manager->setAttribute(Doctrine_Core::Doctrine_Core::ATTR_QUOTE_IDENTIFIER, 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_Core::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
        (
        )

)