php use yii db Migration Handles the creation of table list Has foreig

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
<?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);
}
}