Many to Many Relationships in Yii

Overview

Some time ago I said that I would write an article about how to deal with a many to many relationship in Yii. If you remember I was working on a web site with some very basic database maintenance requirements. In this case I have a set of standards and a set of categories. Logically there is a many to many relationship between the standards and the categories. Any one standard can be associated with multiple categories, and any one category can be associated with multiple standards.

The Schema

Standards

Field	Type	Key
id	numeric	yes
description	character	no

Categories

Field	Type	Key
id	numeric	yes
description	character	no

Linkages

Field	Type	Key
standard_id	numeric	yes
category_id	numeric	yes

The Approach

Since I only have a limited number of categories per standard, it would be nice if I could just list the categories on the maintenance page with check boxes and allow the user to select the appropriate categories for the standard. It turns out that this is a bit more work than populating a drop down list, but not much more. I will need to take the following steps:

1. I need to make sure that I have defined the many to many relationship in my standards model.
2. I need to add the check box list to my view and populate it with all the available categories.
3. I need to add an attribute to my model to hold the array of selected categories
4. I need to add a method to my standards model that actually builds an array of selected categories for a given standard.
5. I need to add some code to my create and update actions in my standards controller which will be responsible for loading the new attribute, and for saving the selected categories to the linkage file.
6. I need to add a method to my categories model that will replace the current selected categories with the newly selected categories.

The Relationship

You will need to create a many to many relationship in your Standards model  this relationship will tell Active Record how to retrieve all the categories for a given standard. It looks like this:

$categories=array(

          self::MANY_MANY,

          'Categories',

          'linkages(standard_id,category_id)'

     )

You can add a similar relation to the Categories model to point back to the Standards if you wish. I define all my relations up front whether I am using them or not. That way if I need the relation, it is there. And Yii's lazy loading prevents querying the relations unless I use it, or explicitly use a with() or through() method.

The View

Here is where things start looking very familiar to those who have used the drop down list. To display my list of check boxes on my view, I am first going to have to generate the list with CHtml::listData(), then pass that list to the CActiveForm::CheckBoxList() method. It looks like this:

<div class="radio-list">
 <?php echo $form->labelEx($model,'cl_categories'); ?>
 <?php $list = CHtml::listData(
     Categories::model()->findAll(),'id','description'); ?>
 <?php echo $form->checkBoxList($model,'cl_categories',$list); ?>
 <?php echo $form->error($model,'cl_categories'); ?>
</div>

Notice that my attribute cl_categories is not part of the data model. I will have to add this attribute to the Standards model for this to work. The categories attribute added to the Standards model via the relationship described above will contain an array of Categories models. Unfortunately the CheckBoxList() method requires an array of values rather than an array of models, and the $_POST['cl_categories'] variable will also be an array of values rather than an array of models. So we need a temporary attribute here which I have named cl_categories. The $list array built by listData() contains the list of category descriptions with their ids. The category id will be mapped to the value of the check box, and and the category description will be mapped to the check box label.

The Standards Model

So back to the Standards model, we need a temporary attribute (cl_categories) to hold an array of category id values to be used both to populate the check box list, and to return the selected categories from the view. This is accomplished by adding a var $cl_categories; line to the beginning of the Standards model. We also need a method to populate this temporary attribute from the $categories relation.  That will look like this:

/**

 * Loads categories from model

 */

public function loadCategories()

{

    $this->cl_categories = array();

    foreach ($this->categories as $category) {

        $this->cl_categories[] = $category->id;

    }

    return $this;

}

The Standards Controller

A few simple changes in the controller will allow us to use this new standards model attribute and pass the correct values to the view. I will make similar changes to both the create and update actions.  I present the update action here for example with notes on how the create action differs.

/**

 * Updates a particular model.

 * If update is successful, the browser will be redirected to the
 * 'view' page.

 * @param integer $id the ID of the model to be updated

 */

public function actionUpdate($id)

{

    // For the create action, model creation is simply

    // $model = new Standards;

    $model=$this->loadModel($id)

            ->loadCategories();

    if(isset($_POST['Standards']))

    {

        $model->attributes=$_POST['Standards'];

        if ($model->save()) {

            Linkages::model()->saveAll($model->id, 

                    $model->cl_categories);

            $this->redirect(array('view','id'=>$model->id));

        }

    }

    $this->render('update',array(

            'model'=>$model,

        ));

}

You will notice that this differs from the normal Gii generated update action in that I have chained the call to loadCategories() to the end of loadModel(id). This causes attribute cl_categories to be populated from the categories relation after the model object is created and loaded. I have also added a call to save the categories once the Standards model has been successfully saved. I should wrap the saves in a transaction since it involves multiple files, but will leave that to you as an exercise.

You may be wondering how this all works since Yii does lazy loading, and won't this cause a lot of extra queries? Yes, yes it will. But we can alleviate those issues by telling the Standards controller to go ahead and query the Categories relation when it loads the Standards model.  This happens in the loadModel($id) method. Like so:

$model=Standards::model()->with(array('categories'))

        ->findByPk($id);

You see the extra piece? that with(array('categories')) in there tells findByPk($id) to go ahead and load the categories relation with the Standards model.

The Linkages Model

One thing remains I need to add some code to the Linkages model to replace the category id values for the selected standard with the new category id values selected by the user.

/**

 * Replaces job categories with current array of categories

 */

public function saveAll($standard, array $categories)

{

    self::model()

        ->deleteAllByAttributes(array('standard_id'=>$standard));

    foreach ($categories as $category) {

        $model = new Categories();

        $model->standard_id = $standard;

        $model->category_id = $category;

        $model->save(false);

    }

}

Final Notes

Thanks for hanging in there with me.  This has been quite a post. If you find you need to process a many to many relationship on a Yii web page, maybe you should try using the CheckBoxList(). It is as natural a process as they come. Let me know how it works for you.