基于 yiisoft/yii2-app-advanced，在 GitHub 上新建仓库 yii2-app-advanced，新建接口应用(实现 RESTful 风格的 Web Service 服务的 API)，实现 ActiveRecord 的软删除，生成 ActiveQuery，自定义查询类 (六) (2)
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，以仅查询出未被删除的资源
近期评论