<?php
use yii\db\Migration;
/**
* Handles the creation of table `list`.
* Has foreign keys to the tables:
*
* - `list`
*/
class m190325_203905_create_list_table extends Migration
{
/**
* {@inheritdoc}
*/
public function safeUp()
{
$this->createTable('list', [
'id' => $this->primaryKey(),
'title' => $this->string(),
'parent_id' => $this->integer(),
]);
// creates index for column `parent_id`
$this->createIndex(
'idx-list-parent_id',
'list',
'parent_id'
);
// add foreign key for table `list`
$this->addForeignKey(
'fk-list-parent_id',
'list',
'parent_id',
'list',
'id',
'CASCADE'
);
}
/**
* {@inheritdoc}
*/
public function safeDown()
{
// drops foreign key for table `list`
$this->dropForeignKey(
'fk-list-parent_id',
'list'
);
// drops index for column `parent_id`
$this->dropIndex(
'idx-list-parent_id',
'list'
);
$this->dropTable('list');
}
}
<?php
/**
* Created with love by АльянсЭкспресс.
* Author: Anochin Nikolay
* Email: titan12345@mail.ru
* Phone: +7 925 174 1314
* Date: 25.03.2019
* Time: 23:43
*/
namespace console\controllers;
use yii\console\Controller;
use yii\helpers\ArrayHelper;
/**
* Class TestController
* @package console\controllers
*/
class TestController extends Controller
{
private function setListFixtures() {
$sql = "
insert into list (title, parent_id) VALUES
('a', null), ('e', 4), ('d', 3), ('b', 1), ('c', 2)
";
\Yii::$app->db->createCommand($sql)->execute();
}
private function echoList($list) {
print_r($list);
var_dump(implode(',', ArrayHelper::getColumn($list, 'title')) . "\n");
}
private function insertBetween($position = 4) {
$sql = "
insert into list (title, parent_id) VALUES
('f', :pos)
";
\Yii::$app->db->createCommand($sql, [':pos' => $position])->execute();
$id = \Yii::$app->db->getLastInsertID();
// Вставим между d и e букву f
$sql = '
update list set parent_id = :id where parent_id = :pos and id != :id
';
\Yii::$app->db->createCommand($sql, [':id' => $id, ':pos' => $position])->execute();
}
public function actionList() {
// $this->setListFixtures();
$sql = '
select list.* from list
left join list child_list on list.id = child_list.id
order by (CASE WHEN list.parent_id IS NULL THEN 0 ELSE 1 END), list.parent_id
';
$res = \Yii::$app->db->createCommand($sql)->queryAll();
// $this->echoList($res);
// $this->insertBetween();
// $res = \Yii::$app->db->createCommand($sql)->queryAll();
$this->echoList($res);
}
}