Categories: MySQLMySQL 5.7PHPPHP 7.2Web应用开发Web框架YiiYii 2数据库编程语言

基于 yiisoft/yii2-app-advanced，在 GitHub 上新建仓库 yii2-app-advanced，新建接口应用(实现 RESTful 风格的 Web Service 服务的 API)，实现 ActiveRecord 的软删除，生成 ActiveQuery，自定义查询类 (六) (2)

图10

1、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，201 响应，status 值默认为 1

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 10000,
    "message": "创建用户成功",
    "data": {
        "username": "111111",
        "email": "111111@163.com",
        "password_hash": "$2y$13$DcZR8PZnlOUqWXRqpbfrxelU6uJIq4dTJTsHLVcnUylOnhAX27SQe",
        "auth_key": "dq78OPYSn4yIsUtYI7xp90zhZIVoE3Yi",
        "status": 1,
        "created_at": 1550042976,
        "updated_at": 1550042976,
        "id": 1
    }
}

SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='111111')
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='111111@163.com')
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `status`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$DcZR8PZnlOUqWXRqpbfrxelU6uJIq4dTJTsHLVcnUylOnhAX27SQe', 'dq78OPYSn4yIsUtYI7xp90zhZIVoE3Yi', 1, 1550042976, 1550042976)

2、在 Postman 中，DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 ，200 响应

{
    "code": 10000,
    "message": "删除用户成功"
}

SELECT * FROM `user` WHERE `id`='1'
UPDATE `user` SET `status`=-1, `updated_at`=1550043516 WHERE `id`=1

3、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，422 响应，不符合预期，预期为 201 响应，因为 “username”: “111111” 已经被删除

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 20004,
    "message": "数据验证失败：Username的值\"111111\"已经被占用了。"
}

SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`username`='111111')
SELECT EXISTS(SELECT * FROM `user` WHERE `user`.`email`='111111@163.com')

4、新建 \console\migrations\m180925_054952_add_is_deleted_and_deleted_at_to_user.php。更新字段，status，状态，0：禁用；1：启用，默认：1。新增字段，is_deleted，是否被删除，0：否；1：是，默认：0。新增字段，deleted_at，删除时间，默认：0。

<?php

use yii\db\Migration;

/**
 * Class m180925_054952_add_is_deleted_and_deleted_at_to_user
 */class m180925_054952_add_is_deleted_and_deleted_at_to_user extends Migration
{
    /**
     * {@inheritdoc}
     */    public function safeUp()
    {
        $this->alterColumn('{{%user}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(1)->comment('状态，0：禁用；1：启用'));
        $this->addColumn('{{%user}}', 'is_deleted', $this->smallInteger(6)->notNull()->defaultValue(0)->comment('是否被删除，0：否；1：是')->after('status'));
        $this->addColumn('{{%user}}', 'deleted_at', $this->integer()->notNull()->defaultValue(0)->comment('删除时间')->after('updated_at'));
        $this->dropIndex('username', '{{%user}}');
        $this->dropIndex('email', '{{%user}}');
        $this->createIndex('uc_username_is_deleted_deleted_at', '{{%user}}', ['username', 'is_deleted', 'deleted_at'], $unique = true);
        $this->createIndex('uc_email_is_deleted_deleted_at', '{{%user}}', ['email', 'is_deleted', 'deleted_at'], $unique = true);
    }

    /**
     * {@inheritdoc}
     */    public function safeDown()
    {
        echo "m180925_054952_add_is_deleted_and_deleted_at_to_user cannot be reverted.\n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m180925_054952_add_is_deleted_and_deleted_at_to_user cannot be reverted.\n";

        return false;
    }
    */}

5、新建 \console\migrations\m180925_060709_add_is_deleted_and_deleted_at_to_page.php

<?php

use yii\db\Migration;

/**
 * Class m180925_060709_add_is_deleted_and_deleted_at_to_page
 */class m180925_060709_add_is_deleted_and_deleted_at_to_page extends Migration
{
    /**
     * {@inheritdoc}
     */    public function safeUp()
    {
        $this->alterColumn('{{%page}}', 'status', $this->smallInteger(6)->notNull()->defaultValue(1)->comment('状态，0：禁用；1：草稿；2：发布'));
        $this->addColumn('{{%page}}', 'is_deleted', $this->smallInteger(6)->notNull()->defaultValue(0)->comment('是否被删除，0：否；1：是')->after('status'));
        $this->addColumn('{{%page}}', 'deleted_at', $this->integer()->notNull()->defaultValue(0)->comment('删除时间')->after('updated_at'));
        $this->dropIndex('uc_uuid', '{{%page}}');
        $this->createIndex('uc_uuid_is_deleted_deleted_at', '{{%page}}', ['uuid', 'is_deleted', 'deleted_at'], $unique = true);
    }

    /**
     * {@inheritdoc}
     */    public function safeDown()
    {
        echo "m180925_060709_add_is_deleted_and_deleted_at_to_page cannot be reverted.\n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m180925_060709_add_is_deleted_and_deleted_at_to_page cannot be reverted.\n";

        return false;
    }
    */}

6、新建 \console\migrations\m180925_060709_add_is_deleted_and_deleted_at_to_page.php，将 status 等于 -1 的记录更新为 is_deleted 等于 1，status 等于 0，deleted_at 等于当前记录的 updated_at。

<?php

use yii\db\Migration;
use yii\db\Expression;

/**
 * Class m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page
 */class m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page extends Migration
{
    /**
     * {@inheritdoc}
     */    public function safeUp()
    {
        $this->update('{{%user}}', ['is_deleted' => 1, 'status' => 0, 'deleted_at' => new Expression('updated_at')], ['status' => -1]);
        $this->update('{{%page}}', ['is_deleted' => 1, 'status' => 0, 'deleted_at' => new Expression('updated_at')], ['status' => -1]);
    }

    /**
     * {@inheritdoc}
     */    public function safeDown()
    {
        echo "m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page cannot be reverted.\n";

        return false;
    }

    /*
    // Use up()/down() to run migration code without a transaction.
    public function up()
    {

    }

    public function down()
    {
        echo "m180925_091558_update_is_deleted_value_and_deleted_at_value_to_user_and_page cannot be reverted.\n";

        return false;
    }
    */}

7、执行数据库迁移命令，查看数据库表 user 结构，符合预期，如图10

图10

./yii migrate

8、基于 Gii 重新生成 /common/models 目录中的模型类文件，附加行为至 \common\logics\User.php，软删除时，is_deleted 的值为 1，deleted_at 的值为 当前时间戳

<?php

namespace common\logics;

use Yii;
use yii\base\NotSupportedException;
use yii\behaviors\TimestampBehavior;
use yii2tech\ar\softdelete\SoftDeleteBehavior;
use yii\web\IdentityInterface;
use yii\helpers\ArrayHelper;

/**
 * This is the model class for table "{{%user}}".
 *
 * @property int $id
 * @property string $username
 * @property string $auth_key
 * @property string $password_hash
 * @property string $password_reset_token
 * @property string $email
 * @property int $status
 * @property int $created_at
 * @property int $updated_at
 * @property string $password write-only password
 */class User extends \common\models\User implements IdentityInterface
{
    const STATUS_DISABLED = 0; //状态：禁用
    const STATUS_ENABLED = 1; //状态：启用

    const IS_DELETED_NO = 0; //是否被删除：否
    const IS_DELETED_YES = 1; //是否被删除：是

    const DELETED_AT_DEFAULT = 0; //删除时间：默认值

    /**
     * @inheritdoc
     */    public function behaviors()
    {
        return [
            'timestampBehavior' => [
                'class' => TimestampBehavior::className(),
                'attributes' => [
                    self::EVENT_BEFORE_INSERT => ['created_at', 'updated_at'],
                    self::EVENT_BEFORE_UPDATE => 'updated_at',
                ]
            ],
            'softDeleteBehavior' => [
                'class' => SoftDeleteBehavior::className(),
                'softDeleteAttributeValues' => [
                    'is_deleted' => self::IS_DELETED_YES,
                    'deleted_at' => function ($model) {
                        return time();
                    },
                ],
            ],
        ];
    }

    /**
     * @inheritdoc
     */    public function rules()
    {
        $rules = [
            ['is_deleted', 'default', 'value' => self::IS_DELETED_NO],
            ['status', 'default', 'value' => self::STATUS_ENABLED],
            ['status', 'in', 'range' => [self::STATUS_DISABLED, self::STATUS_ENABLED]],
            ['deleted_at', 'default', 'value' => self::DELETED_AT_DEFAULT],
        ];
        $parentRules = parent::rules();

        return ArrayHelper::merge($rules, $parentRules);
    }

    /**
     * @inheritdoc
     */    public static function findIdentity($id)
    {
        return static::findOne(['id' => $id, 'status' => self::STATUS_ENABLED]);
    }

    /**
     * @inheritdoc
     */    public static function findIdentityByAccessToken($token, $type = null)
    {
        throw new NotSupportedException('"findIdentityByAccessToken" is not implemented.');
    }

    /**
     * Finds user by username
     *
     * @param string $username
     * @return static|null
     */    public static function findByUsername($username)
    {
        return static::findOne(['username' => $username, 'status' => self::STATUS_ENABLED]);
    }

    /**
     * Finds user by password reset token
     *
     * @param string $token password reset token
     * @return static|null
     */    public static function findByPasswordResetToken($token)
    {
        if (!static::isPasswordResetTokenValid($token)) {
            return null;
        }

        return static::findOne([
            'password_reset_token' => $token,
            'status' => self::STATUS_ENABLED,
        ]);
    }

    /**
     * Finds out if password reset token is valid
     *
     * @param string $token password reset token
     * @return bool
     */    public static function isPasswordResetTokenValid($token)
    {
        if (empty($token)) {
            return false;
        }

        $timestamp = (int) substr($token, strrpos($token, '_') + 1);
        $expire = Yii::$app->params['user.passwordResetTokenExpire'];
        return $timestamp + $expire >= time();
    }

    /**
     * @inheritdoc
     */    public function getId()
    {
        return $this->getPrimaryKey();
    }

    /**
     * @inheritdoc
     */    public function getAuthKey()
    {
        return $this->auth_key;
    }

    /**
     * @inheritdoc
     */    public function validateAuthKey($authKey)
    {
        return $this->getAuthKey() === $authKey;
    }

    /**
     * Validates password
     *
     * @param string $password password to validate
     * @return bool if password provided is valid for current user
     */    public function validatePassword($password)
    {
        return Yii::$app->security->validatePassword($password, $this->password_hash);
    }

    /**
     * Generates password hash from password and sets it to the model
     *
     * @param string $password
     */    public function setPassword($password)
    {
        $this->password_hash = Yii::$app->security->generatePasswordHash($password);
    }

    /**
     * Generates "remember me" authentication key
     */    public function generateAuthKey()
    {
        $this->auth_key = Yii::$app->security->generateRandomString();
    }

    /**
     * Generates new password reset token
     */    public function generatePasswordResetToken()
    {
        $this->password_reset_token = Yii::$app->security->generateRandomString() . '_' . time();
    }

    /**
     * Removes password reset token
     */    public function removePasswordResetToken()
    {
        $this->password_reset_token = null;
    }

    /**
     * {@inheritdoc}
     * @return UserQuery the active query used by this AR class.
     */    public static function find()
    {
        return new UserQuery(get_called_class());
    }
}

9、在common/logics目录中的MySQL模型文件为业务逻辑相关，继承至 \common\models\UserQuery.php 数据层，编辑 \common\logics\UserQuery.php，其为活动查询类

<?php

namespace common\logics;

/**
 * This is the ActiveQuery class for [[User]].
 *
 * @see User
 */class UserQuery extends \common\models\UserQuery
{
    // 是否被删除：否
    public function isDeletedNo()
    {
        return $this->andWhere(['is_deleted' => User::IS_DELETED_NO]);
    }

    // 等于 状态：禁用
    public function disabled()
    {
        return $this->andWhere(['status' => User::STATUS_DISABLED]);
    }

    // 等于 状态：启用
    public function enabled()
    {
        return $this->andWhere(['status' => User::STATUS_ENABLED]);
    }
}

10、软删除的状态：-1 的替换

11、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，201 响应，status 值默认为 1

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 10000,
    "message": "创建用户成功",
    "data": {
        "username": "111111",
        "email": "111111@163.com",
        "password_hash": "$2y$13$wm0kbyttNUh.2mPGBRkC8u44c9ZvQLnqfoNyyhafP5d/ELb4KcU6G",
        "auth_key": "qgpNse_KMphc_rskoq3HPA6piQ3KWPKU",
        "is_deleted": 0,
        "status": 1,
        "deleted_at": 0,
        "created_at": 1550046406,
        "updated_at": 1550046406,
        "id": 1
    }
}

SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$wm0kbyttNUh.2mPGBRkC8u44c9ZvQLnqfoNyyhafP5d/ELb4KcU6G', 'qgpNse_KMphc_rskoq3HPA6piQ3KWPKU', 0, 1, 0, 1550046406, 1550046406)

12、在 Postman 中，DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 ，200 响应

{
    "code": 10000,
    "message": "删除用户成功"
}

SELECT * FROM `user` WHERE `id`='1'
UPDATE `user` SET `is_deleted`=1, `deleted_at`=1550046622 WHERE `id`=1

13、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，201 响应，符合预期 “username”: “111111” 创建成功

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 10000,
    "message": "创建用户成功",
    "data": {
        "username": "111111",
        "email": "111111@163.com",
        "password_hash": "$2y$13$PzqSuKYN8Np.criGjatAruZQzz/azR6rlnYX5UNMkoGBiDKv2agWO",
        "auth_key": "tsw1Yir0dtaVbg3jWYDrS0CY09wV2W5c",
        "is_deleted": 0,
        "status": 1,
        "deleted_at": 0,
        "created_at": 1550046672,
        "updated_at": 1550046672,
        "id": 2
    }
}

SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$PzqSuKYN8Np.criGjatAruZQzz/azR6rlnYX5UNMkoGBiDKv2agWO', 'tsw1Yir0dtaVbg3jWYDrS0CY09wV2W5c', 0, 1, 0, 1550046672, 1550046672)

14、在 Postman 中，DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/1 ，200 响应

{
    "code": 226003,
    "message": "用户ID：1，的状态为已删除"
}

SELECT * FROM `user` WHERE `id`='1'

15、在 Postman 中，DELETE http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/2 ，200 响应

{
    "code": 10000,
    "message": "删除用户成功"
}

SELECT * FROM `user` WHERE `id`='2'
UPDATE `user` SET `is_deleted`=1, `deleted_at`=1550047048 WHERE `id`=2

16、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，201 响应，符合预期 “username”: “111111” 创建成功

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 10000,
    "message": "创建用户成功",
    "data": {
        "username": "111111",
        "email": "111111@163.com",
        "password_hash": "$2y$13$QtoJ.eOjzgrLfLTyQIwsf.8kPz8eYW54g3dPVj1QQeAECvLp85aQK",
        "auth_key": "Z1y-Lzliyrgaf0Us5d4LqYHqUtFwtMYK",
        "is_deleted": 0,
        "status": 1,
        "deleted_at": 0,
        "created_at": 1550047120,
        "updated_at": 1550047120,
        "id": 3
    }
}

SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
INSERT INTO `user` (`username`, `email`, `password_hash`, `auth_key`, `is_deleted`, `status`, `deleted_at`, `created_at`, `updated_at`) VALUES ('111111', '111111@163.com', '$2y$13$QtoJ.eOjzgrLfLTyQIwsf.8kPz8eYW54g3dPVj1QQeAECvLp85aQK', 'Z1y-Lzliyrgaf0Us5d4LqYHqUtFwtMYK', 0, 1, 0, 1550047120, 1550047120)

17、在 Postman 中，POST http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users ，422 响应，符合预期 “username”: “111111” 已存在，浏览用户表数据，如图11

图11

{
    "email": "111111@163.com",
    "password": "111111",
    "username": "111111"
}

{
    "code": 226004,
    "message": "数据验证失败：The combination \"111111\"-\"0\"-\"0\" of 用户名, Is Deleted and Deleted At has already been taken."
}

SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='111111@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0))

18、在 Postman 中，PUT http://api.github-shuijingwan-yii2-app-advanced.localhost/v1/users/3 ，200响应，符合预期

{
    "email": "333333@163.com",
    "password": "333333",
    "status": 1
}

{
    "code": 10000,
    "message": "更新用户成功",
    "data": {
        "id": 3,
        "username": "111111",
        "auth_key": "qc5qVXKkHNKJC1ybac6coQqFiePH2hfE",
        "password_hash": "$2y$13$R6dQZgoFVT0mWV9kBl8uo.K7VtdualY.pK0fTtWHMpGfa3cqVIoXe",
        "password_reset_token": null,
        "email": "333333@163.com",
        "status": 1,
        "is_deleted": 0,
        "created_at": 1550047120,
        "updated_at": 1550049609,
        "deleted_at": 0
    }
}

SELECT * FROM `user` WHERE `id`='3'
SELECT EXISTS(SELECT * FROM `user` WHERE (`user`.`email`='333333@163.com') AND ((`id` != '3') AND (`is_deleted`=0)))
SELECT `user`.`id` FROM `user` WHERE (`user`.`username`='111111') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0) LIMIT 2
SELECT `user`.`id` FROM `user` WHERE (`user`.`email`='333333@163.com') AND (`user`.`is_deleted`=0) AND (`user`.`deleted_at`=0) LIMIT 2
UPDATE `user` SET `auth_key`='qc5qVXKkHNKJC1ybac6coQqFiePH2hfE', `password_hash`='$2y$13$R6dQZgoFVT0mWV9kBl8uo.K7VtdualY.pK0fTtWHMpGfa3cqVIoXe', `email`='333333@163.com', `updated_at`=1550049609 WHERE `id`=3

19、建议在查询资源列表与资源详情时，默认加上条件 `is_deleted`=0，以仅查询出未被删除的资源

永夜

Leave a Comment
Share
Published by
永夜
Tags: deleted_atis_deletedSoftDeletesoftDeleteAttributeValuesSoftDeleteBehavioruc_email_is_deleted_deleted_atuc_username_is_deleted_deleted_atUNIQUEyiisoftyiisoft/yii2-app-advanced唯一唯一索引组合唯一索引
5 年 ago

Recent Posts

在 Google Chrome 中由于网站升级，打开空白，仅在无痕模式中可正常打开的解决

1、在 Google Chro…

2 天 ago

在 https://tinys.pl/cn/swap 上用 SOL 购买得到 NUTS，在 欧易 Web3 钱包 中显示数量为 0 的排查分析

1、在 https://tin…

4 天 ago

在 Laravel 9 中，基于 Spatie\QueryBuilder\QueryBuilder 来生成查询 SQL，同一字段可支持：partial、exact

1、参考：在 Laravel …

6 天 ago

基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式

1、现在导出的 Excel 文…

1 周 ago

在 OneKey 钱包中，发送 Optimism 网络中的 USDT 的流程

1、在 OneKey 钱包中，…

2 周 ago

在 Laravel 9 中，复杂的条件验证的实现，基于嵌套数组中的一系列值，确认嵌套数组外层的值是否验证通过

1、现在请求参数如下，只有当 …

3 周 ago