Yii формируем рекурсивный dropDownList

имеем
$opts = CHtml::listData(Department::model()->findAll('id_parent =5'), 'id', 'name');
echo '<pre>';
var_dump($opts);
?>
<hr />
<div class="column">  
    <?php
    $arr_bit[] = 5; //пункт по умолчанию 
    echo $form->dropDownList($model, 'level_id', $opts, $arr_bit, array
        (
        'empty' => Yii::t('default', 'выберите горизонт'),
            )
    );
    ?> 

получили примерно
array(2) {
  [24]=>
  string(19) "Участок №3"
  [22]=>
  string(19) "Участок №1"
}

нужно привести рекурсивный запрос вида
 WITH RECURSIVE temp1 (id,id_parent,name,PATH, LEVEL, myname, TYPE, hidden) AS
  ( SELECT T1.id,
           T1.id_parent,
           T1.name,
           CAST (T1.id AS VARCHAR (50)) AS PATH,
                1,
                CAST (T1.name AS VARCHAR (255)) AS myname,
                     t1.TYPE AS TYPE,
                                t1.hidden AS hidden
   FROM vgok_site.t_department T1
   WHERE T1.id = 5
     AND t1.hidden = 0
   UNION SELECT T2.id,
                T2.id_parent,
                T2.name,
                CAST (temp1.PATH ||'->'|| T2.id AS VARCHAR(50)) ,LEVEL + 1,
                CAST ((repeat(' _ ', LEVEL+1)||T2.name) AS VARCHAR(255)), t2.TYPE,
                                                                                                                                      t2.hidden
   FROM vgok_site.t_department T2
   INNER JOIN temp1 ON(temp1.id= T2.id_parent
                       AND t2.hidden = 0))
SELECT *
FROM temp1
ORDER BY PATH DESC LIMIT 100

к такому же виду
добиться этого можно следующим кодом

$criteria = new CDbCriteria;
//Рекурсивно выбираем потомков
$connection = Yii::app()->db;
$sql = "
  WITH RECURSIVE temp1 ( id,id_parent,name,PATH, LEVEL, myname , type, hidden) AS (
  SELECT T1.id,T1.id_parent, T1.name, CAST (T1.id AS VARCHAR (50)) as PATH, 1 ,
  CAST (T1.name AS VARCHAR (255)) as myname, t1.type as type, t1.hidden as hidden
  FROM vgok_site.t_department T1 WHERE T1.id = 5  and t1.hidden = 0
  union
  select T2.id, T2.id_parent, T2.name, CAST ( temp1.PATH ||'->'|| T2.id AS VARCHAR(50)) ,LEVEL + 1 ,
  CAST ((repeat(' _ ', LEVEL+1)||T2.name) AS VARCHAR(255)), t2.type, t2.hidden
  FROM vgok_site.t_department T2 INNER JOIN temp1 ON( temp1.id= T2.id_parent and t2.hidden = 0))
  select * from temp1
  ORDER BY PATH desc LIMIT 100
  ";
$dataReader = $connection->createCommand($sql)->query();
$rows = $dataReader->readAll();
for($i = 0, $cnt = count($rows); $i < $cnt; $i++) //формируем столбцы
{
    $opts[$rows[$i]['id']] = $rows[$i]['name'];
}
?>
<hr />
<div class="column">  
    <?php
    $arr_bit[] = 5; //пункт по умолчанию 
    echo $form->dropDownList($model, 'level_id', $opts, $arr_bit, array
        (
        'empty' => Yii::t('default', 'выберите горизонт'),
            )
    );
    ?> 

Комментарии

Популярные сообщения из этого блога

Пишем логи на C# (.NET). Легкий способ.

Учебник yii2