Associations - Linking Tables Together¶

HasOne Associations¶

Let’s set up a Users table with a hasOne relationship to the Addresses table.

First, your database tables need to be keyed correctly. For a hasOne relationship to work, one table has to contain a foreign key that points to a record in the other table. In this case, the Addresses table will contain a field called ‘user_id’. The basic pattern is:

hasOne: the other model contains the foreign key.

Once you create the UsersTable and AddressesTable classes, you can make the association with the following code:

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasOne('Addresses');
    }
}

If you need more control, you can define your associations using the setters. For example, you might want to limit the association to include only certain records:

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasOne('Addresses')
            ->setName('Addresses')
            ->setConditions(['Addresses.primary' => '1'])
            ->setDependent(true);
    }
}

If you want to break different addresses into multiple associations, you can do something like:

class UsersTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasOne('HomeAddress', [
                'className' => 'Addresses'
            ])
            ->setProperty('home_address')
            ->setConditions(['HomeAddress.label' => 'Home'])
            ->setDependent(true);
        $this->hasOne('WorkAddress', [
                'className' => 'Addresses'
            ])
            ->setProperty('work_address')
            ->setConditions(['WorkAddress.label' => 'Work'])
            ->setDependent(true);
    }
}

Possible keys for hasOne association arrays include:

• className: The class name of the other table. This is the same name used when getting an instance of the table. In the ‘Users hasOne Addresses’ example, it should be ‘Addresses’. The default value is the name of the association.

• foreignKey: The name of the foreign key column in the other table. The default value is the underscored, singular name of the current model, suffixed with ‘_id’ such as ‘user_id’ in the above example.

• bindingKey: The name of the column in the current table used to match the foreignKey. The default value is the primary key of the current table such as ‘id’ of Users in the above example.

• conditions: An array of find() compatible conditions such as ['Addresses.primary' => true]

• joinType: The type of the join used in the SQL query. Accepted values are ‘LEFT’ and ‘INNER’. You can use ‘INNER’ to get results only where the association is set. The default value is ‘LEFT’.

• dependent: When the dependent key is set to true, and an entity is deleted, the associated model records are also deleted. In this case we set it to true so that deleting a User will also delete her associated Address.

• cascadeCallbacks: When this and dependent are true, cascaded deletes will load and delete entities so that callbacks are properly triggered. When false, deleteAll() is used to remove associated data and no callbacks are triggered.

• propertyName: The property name that should be filled with data from the associated table into the source table results. By default this is the underscored & singular name of the association so address in our example.

• strategy: The query strategy used to load matching record from the other table. Accepted values are ‘join’ and ‘select’. Using ‘select’ will generate a separate query. The default is ‘join’.

• finder: The finder method to use when loading associated records.

Once this association has been defined, find operations on the Users table can contain the Address record if it exists:

// In a controller or table method.
$query = $users->find('all')->contain(['Addresses']);
foreach ($query as $user) {
    echo $user->address->street;
}

The above would emit SQL that is similar to:

SELECT * FROM users INNER JOIN addresses ON addresses.user_id = users.id;

BelongsTo Associations¶

Now that we have Address data access from the User table, let’s define a belongsTo association in the Addresses table in order to get access to related User data. The belongsTo association is a natural complement to the hasOne and hasMany associations - it allows us to see related data from the other direction.

When keying your database tables for a belongsTo relationship, follow this convention:

belongsTo: the current model contains the foreign key.

We can define the belongsTo association in our Addresses table as follows:

class AddressesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Users');
    }
}

We can also define a more specific relationship using the setters:

class AddressesTable extends Table
{
    public function initialize(array $config)
    {
        // Prior to 3.4 version, use foreignKey() and joinType()
        $this->belongsTo('Users')
            ->setForeignKey('user_id')
            ->setJoinType('INNER');
    }
}

Possible keys for belongsTo association arrays include:

• className: The class name of the other table. This is the same name used when getting an instance of the table. In the ‘Addresses belongsTo Users’ example, it should be ‘Users’. The default value is the name of the association.

• foreignKey: The name of the foreign key column in the current table. The default value is the underscored, singular name of the other model, suffixed with ‘_id’ such as ‘user_id’ in the above example.

• bindingKey: The name of the column in the other table used to match the foreignKey. The default value is the primary key of the other table such as ‘id’ of Users in the above example.

• conditions: An array of find() compatible conditions or SQL strings such as ['Users.active' => true]

• joinType: The type of the join used in the SQL query. Accepted values are ‘LEFT’ and ‘INNER’. You can use ‘INNER’ to get results only where the association is set. The default value is ‘LEFT’.

• propertyName: The property name that should be filled with data from the associated table into the source table results. By default this is the underscored & singular name of the association so user in our example.

• strategy: The query strategy used to load matching record from the other table. Accepted values are ‘join’ and ‘select’. Using ‘select’ will generate a separate query. The default is ‘join’.

• finder: The finder method to use when loading associated records.

Once this association has been defined, find operations on the Addresses table can contain the User record if it exists:

// In a controller or table method.
$query = $addresses->find('all')->contain(['Users']);
foreach ($query as $address) {
    echo $address->user->username;
}

The above would output SQL similar to:

SELECT * FROM addresses LEFT JOIN users ON addresses.user_id = users.id;

HasMany Associations¶

An example of a hasMany association is “Articles hasMany Comments”. Defining this association will allow us to fetch an article’s comments when the article is loaded.

When creating your database tables for a hasMany relationship, follow this convention:

hasMany: the other model contains the foreign key.

We can define the hasMany association in our Articles model as follows:

class ArticlesTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasMany('Comments');
    }
}

We can also define a more specific relationship using the setters:

class ArticlesTable extends Table
{
    public function initialize(array $config)
    {
        $this->hasMany('Comments')
            ->setForeignKey('article_id')
            ->setDependent(true);
    }
}

Sometimes you may want to configure composite keys in your associations:

// Within ArticlesTable::initialize() call
$this->hasMany('Comments')
    ->setForeignKey([
        'article_id',
        'article_hash'
    ]);

Relying on the example above, we have passed an array containing the desired composite keys to setForeignKey(). By default the bindingKey would be automatically defined as id and hash respectively, but let’s assume that you need to specify different binding fields than the defaults. You can setup it manually with setBindingKey():

// Within ArticlesTable::initialize() call
$this->hasMany('Comments')
    ->setForeignKey([
        'article_id',
        'article_hash'
    ])
    ->setBindingKey([
        'whatever_id',
        'whatever_hash'
    ]);

Like hasOne associations, foreignKey is in the other (Comments) table and bindingKey is in the current (Articles) table.

Possible keys for hasMany association arrays include:

• className: The class name of the other table. This is the same name used when getting an instance of the table. In the ‘Articles hasMany Comments’ example, it should be ‘Comments’. The default value is the name of the association.

• foreignKey: The name of the foreign key column in the other table. The default value is the underscored, singular name of the current model, suffixed with ‘_id’ such as ‘article_id’ in the above example.

• bindingKey: The name of the column in the current table used to match the foreignKey. The default value is the primary key of the current table such as ‘id’ of Articles in the above example.

• conditions: an array of find() compatible conditions or SQL strings such as ['Comments.visible' => true]

• sort: An array of find() compatible order clauses or SQL strings such as ['Comments.created' => 'ASC']

• dependent: When dependent is set to true, recursive model deletion is possible. In this example, Comment records will be deleted when their associated Article record has been deleted.

• cascadeCallbacks: When this and dependent are true, cascaded deletes will load and delete entities so that callbacks are properly triggered. When false, deleteAll() is used to remove associated data and no callbacks are triggered.

• propertyName: The property name that should be filled with data from the associated table into the source table results. By default this is the underscored & plural name of the association so comments in our example.

• strategy: Defines the query strategy to use. Defaults to ‘select’. The other valid value is ‘subquery’, which replaces the IN list with an equivalent subquery.

• saveStrategy: Either ‘append’ or ‘replace’. Defaults to ‘append’. When ‘append’ the current records are appended to any records in the database. When ‘replace’ associated records not in the current set will be removed. If the foreign key is a nullable column or if dependent is true records will be orphaned.

• finder: The finder method to use when loading associated records.

Once this association has been defined, find operations on the Articles table can contain the Comment records if they exist:

// In a controller or table method.
$query = $articles->find('all')->contain(['Comments']);
foreach ($query as $article) {
    echo $article->comments[0]->text;
}

The above would output SQL similar to:

SELECT * FROM articles;
SELECT * FROM comments WHERE article_id IN (1, 2, 3, 4, 5);

When the subquery strategy is used, SQL similar to the following will be generated:

SELECT * FROM articles;
SELECT * FROM comments WHERE article_id IN (SELECT id FROM articles);

You may want to cache the counts for your hasMany associations. This is useful when you often need to show the number of associated records, but don’t want to load all the records just to count them. For example, the comment count on any given article is often cached to make generating lists of articles more efficient. You can use the CounterCacheBehavior to cache counts of associated records.

You should make sure that your database tables do not contain columns that match association property names. If for example you have counter fields that conflict with association properties, you must either rename the association property, or the column name.

BelongsToMany Associations¶

An example of a BelongsToMany association is “Article BelongsToMany Tags”, where the tags from one article are shared with other articles. BelongsToMany is often referred to as “has and belongs to many”, and is a classic “many to many” association.

The main difference between hasMany and BelongsToMany is that the link between the models in a BelongsToMany association is not exclusive. For example, we are joining our Articles table with a Tags table. Using ‘funny’ as a Tag for my Article, doesn’t “use up” the tag. I can also use it on the next article I write.

Three database tables are required for a BelongsToMany association. In the example above we would need tables for articles, tags and articles_tags. The articles_tags table contains the data that links tags and articles together. The joining table is named after the two tables involved, separated with an underscore by convention. In its simplest form, this table consists of article_id and tag_id.

belongsToMany requires a separate join table that includes both model names.

We can define the belongsToMany association in both our models as follows:

// In src/Model/Table/ArticlesTable.php
class ArticlesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Tags');
    }
}

// In src/Model/Table/TagsTable.php
class TagsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Articles');
    }
}

We can also define a more specific relationship using configuration:

// In src/Model/Table/TagsTable.php
class TagsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Articles', [
            'joinTable' => 'articles_tags',
        ]);
    }
}

Possible keys for belongsToMany association arrays include:

• className: The class name of the other table. This is the same name used when getting an instance of the table. In the ‘Articles belongsToMany Tags’ example, it should be ‘Tags’. The default value is the name of the association.

• joinTable: The name of the join table used in this association (if the current table doesn’t adhere to the naming convention for belongsToMany join tables). By default this table name will be used to load the Table instance for the join table.

• foreignKey: The name of the foreign key that references the current model found on the join table, or list in case of composite foreign keys. This is especially handy if you need to define multiple belongsToMany relationships. The default value for this key is the underscored, singular name of the current model, suffixed with ‘_id’.

• bindingKey: The name of the column in the current table, that will be used for matching the foreignKey. Defaults to the primary key.

• targetForeignKey: The name of the foreign key that references the target model found on the join model, or list in case of composite foreign keys. The default value for this key is the underscored, singular name of the target model, suffixed with ‘_id’.

• conditions: An array of find() compatible conditions. If you have conditions on an associated table, you should use a ‘through’ model, and define the necessary belongsTo associations on it.

• sort: an array of find() compatible order clauses.

• dependent: When the dependent key is set to false, and an entity is deleted, the data of the join table will not be deleted.

• through: Allows you to provide either the alias of the Table instance you want used on the join table, or the instance itself. This makes customizing the join table keys possible, and allows you to customize the behavior of the pivot table.

• cascadeCallbacks: When this is true, cascaded deletes will load and delete entities so that callbacks are properly triggered on join table records. When false, deleteAll() is used to remove associated data and no callbacks are triggered. This defaults to false to help reduce overhead.

• propertyName: The property name that should be filled with data from the associated table into the source table results. By default this is the underscored & plural name of the association, so tags in our example.

• strategy: Defines the query strategy to use. Defaults to ‘select’. The other valid value is ‘subquery’, which replaces the IN list with an equivalent subquery.

• saveStrategy: Either ‘append’ or ‘replace’. Defaults to ‘replace’. Indicates the mode to be used for saving associated entities. The former will only create new links between both side of the relation and the latter will do a wipe and replace to create the links between the passed entities when saving.

• finder: The finder method to use when loading associated records.

Once this association has been defined, find operations on the Articles table can contain the Tag records if they exist:

// In a controller or table method.
$query = $articles->find('all')->contain(['Tags']);
foreach ($query as $article) {
    echo $article->tags[0]->text;
}

The above would output SQL similar to:

SELECT * FROM articles;
SELECT * FROM tags
INNER JOIN articles_tags ON (
  tags.id = article_tags.tag_id
  AND article_id IN (1, 2, 3, 4, 5)
);

When the subquery strategy is used, SQL similar to the following will be generated:

SELECT * FROM articles;
SELECT * FROM tags
INNER JOIN articles_tags ON (
  tags.id = article_tags.tag_id
  AND article_id IN (SELECT id FROM articles)
);

Student BelongsToMany Course
Course BelongsToMany Student

id | student_id | course_id

id | student_id | course_id | days_attended | grade

class StudentsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Courses', [
            'through' => 'CoursesMemberships',
        ]);
    }
}

class CoursesTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsToMany('Students', [
            'through' => 'CoursesMemberships',
        ]);
    }
}

class CoursesMembershipsTable extends Table
{
    public function initialize(array $config)
    {
        $this->belongsTo('Students');
        $this->belongsTo('Courses');
    }
}

Default Association Conditions¶

The finder option allows you to use a custom finder to load associated record data. Using a finder method lets you encapsulate and reuse your query logic:

// When the authors association is loaded,
// AuthorsTable::findActive() will be called.
$this->belongsTo('Authors')
    ->setFinder('active')

// Pass options to the association finder
// The passed options will be added to the ``$options`` parameter
// of findActive()
$this->belongsTo('Authors')
    ->setFinder(['active' => ['temporary' => true]]);

There are some limitations when using finders to load data in associations that are loaded using joins (belongsTo/hasOne). Only the following aspects of the query will be applied to the root query:

• WHERE conditions.

• Additional joins.

• Contained associations.

Other aspects of the query, such as selected columns, order, group by, having and other sub-statements, will not be applied to the root query. Associations that are not loaded through joins (hasMany/belongsToMany), do not have the above restrictions and can also use result formatters or map/reduce functions.

Loading Associations¶

Once you’ve defined your associations you can eager load associations when fetching results.