嵌套集合模型 [ 2.0 版本 ]

<?php

namespace app\modules\Category\models;

use Yii;
use yii\db\ActiveRecord;
use yii\db\Query;

/**
 * This is the model class for table "{{%category}}".
 *
 * @property integer $id
 * @property string $name
 * @property integer $lft
 * @property integer $rgt
 */
class Category extends ActiveRecord
{
    public $parent;
    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{%category}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['name'], 'required'],
            [['lft', 'rgt'], 'integer'],
            [['name', 'parent'], 'string', 'max' => 20],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id'     => 'ID',
            'name'   => 'Name',
            'lft'    => 'Lft',
            'rgt'    => 'Rgt',
            'parent' => 'Parent',
        ];
    }

    public function beforeSave($insert)
    {
        if (parent::beforeSave($insert)) {
            if ($insert) {
                if (!$this->parent) {
                    # first level
                    $rgt       = static::find()->max('rgt');
                    $this->lft = $rgt + 1;
                    $this->rgt = $rgt + 2;
                } else {
                    $rgt = (new Query())->select('rgt')->from(self::tableName())->where(['id' => $this->parent])->one();
                    $rgt = $rgt['rgt'];
                    Yii::$app->db->createCommand('update {{%category}} set lft = lft + 2 where lft >= :lft', [':lft' => $rgt])->execute();
                    Yii::$app->db->createCommand('update {{%category}} set rgt = rgt + 2 where rgt >= :rgt', [':rgt' => $rgt])->execute();
                    $this->lft = $rgt;
                    $this->rgt = $rgt + 1;
                }
            } else {
                # update
                $old_parent = static::getParent($this->lft, $this->rgt);
                if ($this->parent != $old_parent['id']) {
                    # change parent
                    $db = Yii::$app->db;
                    Yii::$app->db->transaction(function ($db) {
                        $db->createCommand('UPDATE {{%category}} SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN :lft AND :rgt', [':lft' => $this->lft, ':rgt' => $this->rgt])->execute();
                        $db->createCommand('UPDATE {{%category}} SET rgt = rgt - 2 WHERE rgt > :rgt', [':rgt' => $this->rgt])->execute();
                        $db->createCommand('UPDATE {{%category}} SET lft = lft  - 2 WHERE lft > :rgt', [':rgt' => $this->rgt])->execute();
                        if ($this->parent) {
                            $new_parent = (new Query())->select('rgt')->from(self::tableName())->where(['id' => $this->parent])->one();
                            $db->createCommand('update {{%category}} set lft = lft + 2 where lft >= :lft', [':lft' => $new_parent['rgt']])->execute();
                            $db->createCommand('update {{%category}} set rgt = rgt + 2 where rgt >= :rgt', [':rgt' => $new_parent['rgt']])->execute();
                            $this->lft = $new_parent['rgt'];
                            $this->rgt = $new_parent['rgt'] + 1;
                        } else {
                            $rgt       = static::find()->max('rgt');
                            $this->lft = $rgt + 1;
                            $this->rgt = $rgt + 2;
                        }

                    });

                }
            }
            return true;
        } else {
            return false;
        }
    }

    /**
     * [rewriteDelete description]
     * @Author   Cheng
     * @Date     2017-05-03
     * @param    int        $id
     * @param    boolean    $subset       Delete the subset and default to false
     * @return   mixed
     */
    public static function rewriteDelete($id, $subset = false)
    {
        $model = static::find()->select(['lft', 'rgt'])->where(['id' => $id])->asArray()->one();
        $db    = Yii::$app->db;
        return Yii::$app->db->transaction(function ($db) use ($model, $subset) {
            if ($subset) {
                $model['width'] = $model['rgt'] - $model['lft'] + 1;
                $db->createCommand('DELETE FROM {{%category}} WHERE lft BETWEEN :lft AND :rgt', [':lft' => $model['lft'], ':rgt' => $model['rgt']])->execute();
                $db->createCommand('UPDATE {{%category}} SET rgt = rgt - :width WHERE rgt > :rgt', [':width' => $model['width'], ':rgt' => $model['rgt']])->execute();
                $db->createCommand('UPDATE {{%category}} SET lft = lft - :width WHERE lft > :rgt', [':width' => $model['width'], ':rgt' => $model['rgt']])->execute();
            } else {
                $db->createCommand('DELETE FROM {{%category}} WHERE lft = :lft', [':lft' => $model['lft']])->execute();
                $db->createCommand('UPDATE {{%category}} SET rgt = rgt - 1, lft = lft - 1 WHERE lft BETWEEN :lft AND :rgt', [':lft' => $model['lft'], ':rgt' => $model['rgt']])->execute();
                $db->createCommand('UPDATE {{%category}} SET rgt = rgt - 2 WHERE rgt > :rgt', [':rgt' => $model['rgt']])->execute();
                $db->createCommand('UPDATE {{%category}} SET lft = lft  - 2 WHERE lft > :rgt', [':rgt' => $model['rgt']])->execute();
            }
        });
    }

    /**
     * [generateSelect description]
     * @Author Cheng
     * @Date   2017-05-03
     * @param  [type]     $tip [description]
     * @return [type]          [description]
     */
    public static function generateSelect($tip = null)
    {
        $data = self::getTree();
        if (!$tip) {
            $tip = [['id' => 0, 'name' => '---请选择---']];
        }
        return array_merge($tip, $data);
    }

    /**
     * [getTree description]
     * @Author Cheng
     * @Date   2017-05-03
     * @return [type]     [description]
     */
    public static function getTree()
    {
        $model = (new Query())
            ->select(['node.id', 'name' => 'CONCAT( REPEAT("└─", COUNT(parent.name) - 1), node.name)', 'level' => 'COUNT(parent.name)'])
            ->from(['node' => self::tableName(), 'parent' => self::tableName()])
            ->where('node.lft between parent.lft and parent.rgt')
            ->groupBy('node.name')
            ->orderBy(['node.lft' => SORT_ASC])
            ->all();
        return $model;
    }

    /**
     * Get the current first parent 
     * @Author Cheng
     * @Date   2017-05-03
     * @param  int     $lft [description]
     * @param  int     $rgt [description]
     * @return array          [description]
     */
    public static function getParent($lft, $rgt)
    {
        $model = (new Query())
            ->select('*')
            ->from(self::tableName())
            ->where(['and', ['<', 'lft', $lft], ['>', 'rgt', $rgt]])
            ->OrderBy(['lft'=>SORT_DESC])
            ->limit(1)
            ->one();
        return $model;
    }

    /**
     * Get all the current subsets
     * @Author Cheng
     * @Date   2017-05-03
     * @param  int     $lft [description]
     * @param  int     $rgt [description]
     * @return array          [description]
     */
    public static function getChilds($lft, $rgt)
    {
        $model = (new Query())
            ->select('*')
            ->from(self::tableName())
            ->where(['and', ['>', 'lft', $lft], ['<', 'rgt', $rgt]])
            ->all();
        return $model;
    }
}