Defining Relationships Between Models

Consider comments on a blog post. Posts is the native table, and Comments is the foreign table. One Post has many Comments. The foreign table key is post_posts_id relating to the native table key post_id.

The Posts table is the ONE in the one-to-many relationship. The Vendor_Model_Posts file uses the _hasMany($name, [$opts]) method to indicate that a single post record has many comments.

In the Vendor_Model_Posts file, the relationship would be defined as follows:

<?php
$this->_hasMany('comments');
?>

The above code is in its simplest form, without any extra options. It is possible to do this because the tables use the correct naming convention for the foreign columns names1). If the foreign key column was named 'posts_post_id', then you would have to be explicit when defining the _hasMany relationship using an option. The relationship would be defined like this:

<?php
// Here, the column does not follow the naming convention so we 
// define it explicitly. 
$this->_hasMany('comments', array(
    'foreign_col'=>'posts_post_id'
));
 
// Or use the virtual element foreign_key (see below)
$this->_hasMany('comments', array(
    'foreign_key'=>'posts_post_id'
));
?>

The Comments table is the MANY in the one-to-many relationship. The Vendor_Model_Comments file uses the _belongsTo($name, [$opts]) method to indicate that a Comment record belongs to a Post record. In the Vendor_Model_Comments file, you would define your relationship as follows:

<?php
$this->_belongsTo('posts');
?>

Again, this is shown in its simplest form, without any options. If you needed to customize the columns, you could use one or or both of 'foreign_col' and 'native_col' as shown in the following:

<?php
$this->_belongsTo('posts', array(
    'foreign_col'=>'post_id',
    'native_col'=>'post_post_id'
));
?>

You typically do not need to define either of these if you use the aforementioned naming convention for your foreign column names.

Note: You could also use 'foreign_key' in place of 'foreign_col' and 'native_col'. 'foreign_key is a virtual element that automatically populates the 'native_col' or 'foreign_col' value for you, based on the association type. This will be used only when 'native_col' and 'foreign_col' are not set.

Consider a relationship where a blog post can belong to multiple categories and each category can have many posts. This is a classic many-to-many. In order to accomplish this, an junction table (join table) must exist. In Solar, you would call this the 'through' table. A post can have many categories through a join table, and a category may have many posts through the join table. Consider the illustration below.

In the Posts model (Vendor_Model_Posts) the relationship is defined using the _hasManyThrough($name, $through, [$opts]) method and the _hasMany($name, [$opts]) method.

// The many to many relationship 
// The tables use the column naming convention so no options are required.
$this->_hasManyThrough('categories', 'categories_posts');
 
// the relationship to the junction table (through)
// You can specify other options, if needed, see above
$this->_hasMany('categories_posts');

You can also specify the columns if you aren't using the naming convention Solar expects.

$this->_hasManyThrough('categories', 'categories_posts', array(
    'foreign_key'=>'category_category_id',
    'through_key'=>'post_post_id'
));
 
// There are several other options as well.
// $through_table
// $through_native_col
// $through_foreign_col

(need to elaborate on each of these other options, plus more)

In the Categories Model (Vendor_Model_Categories) you define similar relationships.

// The many to many relationship 
// The tables use the column naming convention so no options are required.
$this->_hasManyThrough('posts', 'categories_posts');
 
// the relationship to the junction table (through)
// You can specify other options, if needed, see above
$this->_hasMany('categories_posts');

Finally, in the junction model (Vendor_Model_CategoriesPosts) you specify two simple _belongsTo() relationships.

// the relationship to the posts table
$this->_belongsTo('posts');
 
// the relationship to the categories table
$this->_belongsTo('categories');

As before, you can add options if needed to specify the native and foreign columns.

A one-to-one relationship occurs when you have one row in the native table that matches a single row in the foreign table.

In the Users model (Vendor_Model_Users) you define the relationship using the _hasOne($name, [$opts]) method.

// the relationship to the profiles table
$this->_hasOne('profiles');
 
// if required, define the foreign key
$this->_hasOne('profiles', array(
    'foreign_key'=>'user_userid'
));

In the Profiles model (Vendor_Model_Profiles) you define the relationship using the _belongsTo($name, [$opts]) method.

// the relationship to the users table
$this->_belongsTo('users');