4.6. Native Model "Has Many" Related Foreign Records "Through" An Association

4.6.1. Setup

In our example domain, a blog entry "has many" tags, but because a tag also "has many" blog entries, there has to be an association table mapping between the two. This kind of many-to-many relationship requires a third model to be present; it models the association mapping through which the native and foreign models relate to each other.

As such, setup for a many-to-many relationship is slightly more complicated than a regular has-many/belongs-to. First you have to relate to the association model, then you have to relate to the foreign model through the association. Do so for each side of the many-to-many relationship.

<?php
class Acme_Model_Blogs extends Acme_Sql_Model
{
    protected function _setup()
    {
        // the "through" related association
        $this->_hasMany('taggings');
        
        // has many tags, through this relationship name
        $this->_hasManyThrough('tags', 'taggings');
    }
}

class Acme_Model_Tags extends Acme_Sql_Model
{
    protected function _setup()
    {
        // the "through" related association
        $this->_hasMany('taggings');
        
        // has many tags, through this relationship name
        $this->_hasManyThrough('blogs', 'taggings');
    }
}

Finally, in the association model, state that it belongs-to both sides of the many-to-many relationship.

<?php
class Acme_Model_Taggings extends Acme_Sql_Model
{
    protected function _setup()
    {
        $this->_belongsTo('blog');
        $this->_belongsTo('tag');
    }
}

4.6.2. SQL for Lazy-Load

The above code creates a pseudo-property on each blog record called $tags that gets lazy-loaded by a fetch from the tags entry in the model catalog. That is, each time you ask for a $blog_record->tags, the model system will fetch the related tag collection from the database and place it in $tags for you. The SQL for the lazy-load looks similar to this:

SELECT tags.*
FROM tags AS tags
LEFT JOIN taggings AS taggings ON taggings.tag_id = tags.id
WHERE taggings.blog_id = {$blog_record->id}

Similarly, the above code creates a pseudo-property on each tag record called $blogs that gets lazy-loaded by a fetch from the blogs entry in the model catalog. That is, each time you ask for a $tag_record->blogs, the model system will fetch the related blog collection from the database and place it in $blogs for you. The SQL for the lazy-load looks similar to this:

SELECT blogs.*
FROM blogs AS blogs
LEFT JOIN taggings AS taggings ON taggings.blog_id = blogs.id
WHERE taggings.tag_id = {$tag_record->id}
[Warning] Lazy vs. Eager Loading

Beware the dreaded N+1 problem! If you fetch a collection of 10 blog records and loop through them, displaying the $blog_record->tags each time, you will generate 10 additional SELECT queries (one for each related blog) for a total of 11 queries. To avoid this, eager-fetch the related tags when you fetch the blog collection; more on eager fetching in a later section.

4.6.3. Foreign Key

In a has-many-through relationship, the foreign key exists in the "through" association model/table, and identifies which mappings belong to the native model. In our example domain, where a blog has many tags through taggings, this means the blog foreign key blog_id is on the taggings table, and maps to the primary key on the blogs table. Similarly, where a tag has many blogs through taggings, the tag foreign key tag_idis on the taggings table, and maps to the primary key on the tags table.

4.6.4. Through Key

In a has-many-through relationship, there is a second foreign key to keep track of, the "through" key. The "through" key exists in the "through" model/table, and identifies which mappings belong to the foreign model. In our example domain, where a blog has many tags through taggings, the through key tag_idis on the taggings table, and maps to the primary key on the tags table. Similarly, where a tag has many blogs through taggings, the through key blog_idis on the taggings table, and maps to the primary key on the blogs table.

4.6.5. Relationship Definition

Although the Solar model system makes some assumptions about how tables and foreign keys are named, these assumptions are not hard-coded. You can define every aspect of the foreign relationship using an array of key-value pairs passed to the _hasManyThrough() method.

<?php
        $this->_hasManyThrough('foreigns', 'mappings', array(
            'option' => 'value',
            // ...
        ));
string foreign_name

Normally, the model system expects the foreign model to be in the catalog uner the plural form of the related name. E.g., {native} _hasManyThrough('foreigns', 'mappings') means the foreign model on the other side of the mapping relationship uses the catalog name 'foreigns'.

Use the foreign_name option to specify a different catalog name for the foreign model on the other side of the related mappings. This allows you to name the relationship "foo" (and thereby the record property for the foreign) but use the catalog name "bar" to do the work. (The foreign_class option takes precedence over foreign_name.)

string foreign_key

Normally, the model system expects the foreign key on the foreign table to be the same as the foreign model's primary_col value.

Use the foreign_key option to specify a different column in the foreign table. (This option will be ignored if a specific native_col or foreign_col option is set.)

string through_key

Normally, the model system expects the through key on the through table to be the same as the one defined by the foreign model's foreign_col value.

Use the through_key option to specify a different column in the through table. (This option will be ignored if a specific native_col or foreign_col option is set.)

array cols

Fetch these columns for the foreign records.

array conditions

Additional conditions on the foreign table when fetching related records. These will be used as WHERE conditions, or as JOIN ON conditions, as appropriate for the kind of fetch being performed (e.g. lazy vs. eager fetch).

array through_conditions

Additional conditions on the through table when fetching related records. These will be used as WHERE conditions, or as JOIN ON conditions, as appropriate for the kind of fetch being performed (e.g. lazy vs. eager fetch).

array order

Additional ORDER clauses when fetching related records.

[Note] Note

The following are some less common, but more advanced and more finely grained, options for defining relationships:

string foreign_class

The class name of the foreign model on the other side of the association mapping. Default is the first matching class for the relationship name, as loaded from the parent class stack.

string foreign_alias

Aliases the foreign table using this name. Default is the relationship name.

string foreign_col

The name of the column to join with in the foreign table, matching against the through_foreign_col column in the through table.

string through_foreign_col

The name of the column to join with in the through table, matching against the foreign_col column in the foreign table.

string through native_col

The name of column to join with in the through table, matching against the native_col column in the native table.

string native_col

The name of column to join with in the native table, matching against the through_native_col column in the through table.

string native_by

The strategy to be used for connecting to native records when eager-fetching: 'wherein', meaning a "WHERE IN (...)" a list of native IDs, or 'select', meaning a join against a sub-SELECT.

int wherein_max

When picking a native-by strategy, use 'wherein' for up to this many records in the native result; after this point, use a 'select' strategy.

string merge

Indicates the strategy to use for merging joined foreign rows; 'server' means the database will do it via a single SELECT combined into the native fetch (only possible with to-one relationships), whereas 'client' means PHP will do it, using one additional SELECT for the relationship (always for to-many relationships, optionally for to-one relationships) and then merging the rows in a PHP loop.



Local