Relations

ActiveRecords could have relationship between each other. Relationship between two AR classes is directly associated with the relationship between the database tables represented by the AR classes.

'VarName'=>array('RelationType', 'ClassName', 'ForeignKey', ...additional options)

RelationType:
BELONGS_TO: if the relationship between table A and B is one-to-many, then B belongs to A (e.g. Post belongs to User);
HAS_MANY: if the relationship between table A and B is one-to-many, then A has many B (e.g. User has many Post);
HAS_ONE: this is special case of HAS_MANY where A has at most one B (e.g. User has at most one Profile);
MANY_MANY: this corresponds to the many-to-many relationship in database. An associative table is needed to break a many-to-many relationship into one-to-many relationships, as most DBMS do not support many-to-many relationship directly. In our example database schema, the tbl_post_category serves for this purpose. In AR terminology, we can explain MANY_MANY as the combination of BELONGS_TO and HAS_MANY. For example, Post belongs to many Category and Category has many Post.

class Post extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'author'=>array(self::BELONGS_TO, 'User', 'author_id'),
            'categories'=>array(self::MANY_MANY, 'Category',
                'tbl_post_category(post_id, category_id)'),
        );
    }
}
 
class User extends CActiveRecord
{
    ......
 
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}


//this is RULES
class EmailGroup extends CActiveRecord {
   ...
   public function rules() {
      return array(
        array('name, description', 'required'),
        array('name', 'length', 'max'=>20),
        array('description', 'length', 'max'=>45),
        array('name, description', 'safe', 'on'=>'search'),
 
        array('name', 'unique', 'on'=>'insert', 'message'=>'This value already exists!')
      );
   }
   ...
}

The simplest way of performing relational query is by reading a relational property of an AR instance. If the property is not accessed previously, a relational query will be initiated, which joins the two related tables and filters with the primary key of the current AR instance.

// retrieve the post whose ID is 10
$post=Post::model()->findByPk(10);
// retrieve the post's author: a relational query will be performed here 
//(lazy loading approach)
$author=$post->author;

//The eager loading approach retrieves the related AR instances 
//together with the main AR instance(s). 
//This is accomplished by using the with() method
$posts=Post::model()->with('author')->findAll();
$posts=Post::model()->with('author','categories')->findAll();

//We can also do nested eager loading. 
//Instead of a list of relationship names, 
//we pass in a hierarchical representation 
//of relationship names to the with() method
$posts=Post::model()->with(
    'author.profile',
    'author.posts',
    'categories')->findAll();

$criteria=new CDbCriteria;
$criteria->with=array(
    'author.profile',
    'author.posts',
    'categories',
);
$posts=Post::model()->findAll($criteria);
//OR
$posts=Post::model()->findAll(array(
    'with'=>array(
        'author.profile',
        'author.posts',
        'categories',
    )
));

Perform query using relation but don’t want to get related models.

$users=User::model()->with(array(
    'posts'=>array(
        // we don't want to select posts
        'select'=>false,
        // but want to get only users with published posts
        'joinType'=>'INNER JOIN',
        'condition'=>'posts.published=1',
    ),
))->findAll();

Relational Query Options

select: a list of columns to be selected for the related AR class. It defaults to ‘*’, meaning all columns. Column names referenced in this option should be disambiguated.
condition: the WHERE clause. It defaults to empty. Column names referenced in this option should be disambiguated.
params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.
on: the ON clause. The condition specified here will be appended to the joining condition using the AND operator. Column names referenced in this option should be disambiguated. This option does not apply to MANY_MANY relations.
order: the ORDER BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.
with: a list of child related objects that should be loaded together with this object. Be aware that using this option inappropriately may form an infinite relation loop.
joinType: type of join for this relationship. It defaults to LEFT OUTER JOIN.
alias: the alias for the table associated with this relationship. It defaults to null, meaning the table alias is the same as the relation name.
together: whether the table associated with this relationship should be forced to join together with the primary table and other tables. This option is only meaningful for HAS_MANY and MANY_MANY relations. If this option is set false, the table associated with the HAS_MANY or MANY_MANY relation will be joined with the primary table in a separate SQL query, which may improve the overall query performance since less duplicated data is returned. If this option is set true, the associated table will always be joined with the primary table in a single SQL query, even if the primary table is paginated. If this option is not set, the associated table will be joined with the primary table in a single SQL query only when the primary table is not paginated. For more details, see the section “Relational Query Performance”.
join: the extra JOIN clause. It defaults to empty. This option has been available since version 1.1.3.
group: the GROUP BY clause. It defaults to empty. Column names referenced in this option should be disambiguated.
having: the HAVING clause. It defaults to empty. Column names referenced in this option should be disambiguated.
index: the name of the column whose values should be used as keys of the array that stores related objects. Without setting this option, an related object array would use zero-based integer index. This option can only be set for HAS_MANY and MANY_MANY relations.
scopes: scopes to apply. In case of a single scope can be used like ‘scopes’=>’scopeName’, in case of multiple scopes can be used like ‘scopes’=>array(‘scopeName1′,’scopeName2’). This option has been available since version 1.1.9.

In addition, the following options are available for certain relationships during lazy loading:

limit: limit of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
offset: offset of the rows to be selected. This option does NOT apply to BELONGS_TO relation.
through: name of the model’s relation that will be used as a bridge when getting related data. Can be set only for HAS_ONE and HAS_MANY. This option has been available since version 1.1.7.

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                            'order'=>'posts.create_time DESC',
                            'with'=>'categories'),
            'profile'=>array(self::HAS_ONE, 'Profile', 'owner_id'),
        );
    }
}

Disambiguating Column Names
When a column name appears in two or more tables being joined together, it needs to be disambiguated. This is done by prefixing the column name with its table’s alias name.

$posts=Post::model()->with('comments')->findAll(array(
    'order'=>'t.create_time, comments.create_time'
));

//Disambiguate columns in named scope
public function scopes()
{
    $t=$this->getTableAlias(false);
    return array(
        'published'=>array('condition'=>"$t.published=1"),
    );
}

//Using alias to avoid ambiguous
public function scopes()
{
  return array(
    'discount'=>array(
      'condition'=>'price.discount=1',
      'alias'=>'price',
    ),
  );
}

Dynamic Relational Query Options
We can use dynamic relational query options in both with() and the with option. The dynamic options will overwrite existing options as specified in the relations() method.

User::model()->with(array(
    'posts'=>array('order'=>'posts.create_time ASC'),
    'profile',
))->findAll();

Dynamic query options can also be used when using the lazy loading approach to perform relational query. To do so, we should call a method whose name is the same as the relation name and pass the dynamic query options as the method parameter.

$user=User::model()->findByPk(1);
$posts=$user->posts(array('condition'=>'status=1'));

By default, Yii uses eager loading, i.e., generating a single SQL statement, except when LIMIT is applied to the primary model. We can set the together option in the relation declarations to be true to force a single SQL statement even when LIMIT is used.

public function relations()
{
    return array(
        'comments' => array(self::HAS_MANY, 'Comment', 'post_id',
                        'together'=>false),
    );
}

//We can also dynamically set this option when we perform the eager loading:
$posts = Post::model()->with(
            array('comments'=>array(
                'together'=>false
            ))
        )->findAll();

Statistical Query
Yii also supports the so-called statistical query (or aggregational query). It refers to retrieving the aggregational information about the related objects, such as the number of comments for each post, a statistical query will calculate the COUNT expression.

class Post extends CActiveRecord
{
    public function relations()
    {
        return array(
            'commentCount'=>array(self::STAT, 'Comment', 'post_id'),
            'categoryCount'=>array(
                self::STAT, 'Category', 'post_category(post_id, category_id)'
            ),
        );
    }
}

//With the above declaration, we can retrieve the number of comments for a post
$posts=Post::model()->with('commentCount', 'categoryCount')->findAll();

We can customize it by specifying additional options

select: the statistical expression. Defaults to COUNT(*), meaning the count of child objects.
defaultValue: the value to be assigned to those records that do not receive a statistical query result. For example, if a post does not have any comments, its commentCount would receive this value. The default value for this option is 0.
condition: the WHERE clause. It defaults to empty.
params: the parameters to be bound to the generated SQL statement. This should be given as an array of name-value pairs.
order: the ORDER BY clause. It defaults to empty.
group: the GROUP BY clause. It defaults to empty.
having: the HAVING clause. It defaults to empty.

Relational Query with Named Scopes
In the first form, named scopes are applied to the main model.

$posts=Post::model()->published()->recently()->with('comments')->findAll();

In the second form, named scopes are applied to the related models.

$posts=Post::model()->with('comments:recently:approved')->findAll();
// or since 1.1.7
$posts=Post::model()->with(array(
    'comments'=>array(
        'scopes'=>array('recently','approved')
    ),
))->findAll();
// or since 1.1.7
$posts=Post::model()->findAll(array(
    'with'=>array(
        'comments'=>array(
            'scopes'=>array('recently','approved')
        ),
    ),
));

Occasionally you may need to retrieve a scoped relationship using a lazy-loading approach, instead of the normal eager loading method shown above.

// note the repetition of the relationship name, which is necessary 
$approvedComments = $post->comments('comments:approved');

Named scopes can also be specified in the with option of the relational rules

class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>'comments:approved'),
        );
    }
}
 
// or since 1.1.7
class User extends CActiveRecord
{
    public function relations()
    {
        return array(
            'posts'=>array(self::HAS_MANY, 'Post', 'author_id',
                'with'=>array(
                    'comments'=>array(
                        'scopes'=>'approved'
                    ),
                ),
            ),
        );
    }
}

It’s possible to pass parameters for relational named scopes.

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                'rated'=>5,
            ),
        ),
    ),
));
 
class Post extends CActiveRecord
{
    ......
    public function rated($rating)
    {
        $this->getDbCriteria()->mergeWith(array(
            'condition'=>'rating=:rating',
            'params'=>array(':rating'=>$rating),
        ));
        return $this;
    }
    ......
}

Relational Query with through

// key1 is a key defined in relation specified in through (posts is this case).
// key2 is a key defined in a model relation points to (Comment in this case).
'comments'=>array(self::HAS_MANY,'Comment',array('key1'=>'key2'),'through'=>'posts'),

A bit more complex example is getting all comments for all users of a particular group. In this case we have to use several relations with through in a single model:

class Group extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'roles'=>array(self::HAS_MANY,'Role','group_id'),
            'users'=>array(
                self::HAS_MANY,'User',array('user_id'=>'id'),'through'=>'roles'
            ),
            'comments'=>array(
                self::HAS_MANY,'Comment',array('id'=>'user_id'),'through'=>'users'
            ),
        );
    }
}

//Usage examples

// get all groups with all corresponding users
$groups=Group::model()->with('users')->findAll();
 
// get all groups with all corresponding users and roles
$groups=Group::model()->with('roles','users')->findAll();
 
// get all users and roles where group ID is 1
$group=Group::model()->findByPk(1);
$users=$group->users;
$roles=$group->roles;
 
// get all comments where group ID is 1
$group=Group::model()->findByPk(1);
$comments=$group->comments;

An example of using HAS_ONE with through is getting user address where user is bound to address using profile.

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'profile'=>array(self::HAS_ONE,'Profile','user_id'),
            'address'=>array(
                self::HAS_ONE,'Address',array('id'=>'profile_id'),
                    'through'=>'profile'
            ),
        );
    }
}

//Usage examples
// get address of a user whose ID is 1
$user=User::model()->findByPk(1);
$address=$user->address;

through can be used for a model bound to itself using a bridge model. In our case it’s a user mentoring other users:

class User extends CActiveRecord
{
    ...
    public function relations()
    {
        return array(
            'mentorships'=>array(
                self::HAS_MANY,'Mentorship','teacher_id','joinType'=>'INNER JOIN'
            ),
            'students'=>array(
                self::HAS_MANY,'User',array('student_id'=>'id'),
                    'through'=>'mentorships','joinType'=>'INNER JOIN'
            ),
        );
    }
}

//Usage examples
// get all students taught by teacher whose ID is 1
$teacher=User::model()->findByPk(1);
$students=$teacher->students;

Pass multiple parameters to relational named scopes.

$users=User::model()->findAll(array(
    'with'=>array(
        'posts'=>array(
            'scopes'=>array(
                // passing only one parameter
                'rated'=>5,
                // passing multiple parameters
                // must be in the same order as function declaration
                'anotherScope'=>array(5, true),
            ),
        ),
    ),
));

additional info about MANY_TO_MANY configuration
If you declare a many to many relationship, the order of keys inside the jointable declaration must be ‘my_id, other_id’:

class Post extends CActiveRecord
{
  public function relations()
  {
    return array(
        'categories'=>array(self::MANY_MANY, 'Category',
            'tbl_post_category(post_id, category_id)'),
    );
  }
}
class Category extends CActiveRecord
{
  public function relations()
  {
    return array(
        'Posts'=>array(self::MANY_MANY, 'Post',
            'tbl_post_category(category_id, post_id)'),
    );
  }
}

In order to use relational AR, it is recommended that primary-foreign key constraints are declared for tables that need to be joined. The constraints will help to keep the consistency and integrity of the relational data.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Recent Posts
Recent Comments
Archives
Categories
%d bloggers like this: