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'=>4,'name'=>'Bathroom','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
c.id, c.name, (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), c.name
Then, in our [model].php file, we are going to add the following simple function:
public function getGroupedCategories() {
$connection = Yii::app()->db;
$sql='SELECT c.id, c.name, (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), c.name';
$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
was very helpful