Setup Multi-selects with optgroups

Well, I did this more as a helper for others, but I also wrote this, to get some feedback, in case I am just "doing it wrong".

In this example, we have a table with a parent-child category relationship, and we want the select box to be grouped according the top level parent.

Our data looks something similar to the following:

id  name         parent sort 

1   Residential  0      NULL

2   Industrial   0      NULL

3   Bedroom      1      NULL

4   Bathroom     1      NULL

5   Kitchen      1      NULL

6   Dining Room  1      NULL

7   All Products 1      1

8   All Products 2      1

From what I know, the default CHtml::listData w/ findall will not handle this. Therefore, we are going to create a custom function to group the data.

In the end, the data returned needs to look something like this:

return array(

    array('id'=>7,'name'=>'All Products','group'=>'Residential'),


    array('id'=>8,'name'=>'All Products','group'=>'Industrial'),


To do that, we will need to structure our SQL so that it looks something like this:

id  name         group

7   All Products Residential

4   Bathroom     Residential

3   Bedroom      Residential

6   Dining Room  Residential

5   Kitchen      Residential

8   All Products Industrial

Which we can achieve with the following SQL:

SELECT,, (Select Name from Category WHERE id = c.parent) AS `group` 


  Category c 


  c.parent > 0 


  c.parent, COALESCE(c.sort,~0),

Then, in our [model].php file, we are going to add the following simple function:

public function getGroupedCategories() { 

      $connection = Yii::app()->db;

      $sql='SELECT,, (Select Name from Category WHERE id = c.parent) AS `group` FROM Category c WHERE c.parent > 0 ORDER BY c.parent, COALESCE(c.sort,~0),';

      $t = $connection->createCommand($sql)->queryAll();            

      return $t;


This function will return the data in a format that we require. And is what is going to populate the CHtml::listData with data instead of the standard findall.

To use this function, we will need to replace the default HTML block doing the lookup in our _form.php file:

In our example, it originally looked like this:

<?php echo $form->dropDownList($model, 'categories', CHtml::listData(

         Category::model()->findAll(), 'id', 'name'),

         array('multiple'=>'multiple', 'size'=>5)



So, with some simple changes, it will end up looking like this:

<?php echo $form->dropDownList($model, 'categories', CHtml::listData(

         $model->getGroupedCategories(), 'id', 'name', 'group'),

         array('multiple'=>'multiple', 'size'=>5)



Thats all. We now have a multi-select box with groups.

I hope this helps some people out. Please feel free chime in, if you think there is a better way to accomplish this.

If you like it, I can do more, I have a couple of others in mind.

Here are some links that helped me out:

how can i generate a select list with optgroup - Yii Framework Forum

Creating a database-driven hierarchical Structure combined with CMenu and superfish | Wiki | Yii Framework

Working with Databases: Database Access Objects | The Definitive Guide to Yii | Yii Framework

If you are looking for something a bit more fancy, you can always try this (which looks nice, but wasn’t what I was going for):

asmselectex | Extension | Yii Framework

Thanks :D was very helpful