In Yii 2.0, you need to query resources based on MySQL ActiveRecord and Redis ActiveRecord
1. In Postman, open the URL:http://api.pcs-api.localhost/v1/plan-config-column-users?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=685f805f71faedfe215f55bbb01b50c6&filter[config_column_id]=1 , only the fields are supported: config_column_id queries, the response is as follows:
{
"code": 10000,
"message": "获取选题栏目人员配置列表成功",
"data": {
"items": [
{
"id": 1,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"user_id": 8,
"config_column_id": 1,
"login_name": "13281105967",
"user_nick": "13281105967_nick",
"user_mobile": "13281105967",
"role_code": "column_manager",
"status": 1,
"is_not_isolated": 0,
"is_deleted": 0,
"created_at": 1556526988,
"updated_at": 1556536370,
"deleted_at": 0,
"user_pic": "https://pgcupload.flydev.chinamcloud.cn/uploads/cmc_user_avatar/20190219/1550570817-4LLQJJ.png"
},
{
"id": 4280,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"user_id": 29,
"config_column_id": 1,
"login_name": "cgl",
"user_nick": "cgl_nick",
"user_mobile": "",
"role_code": "default_role",
"status": 1,
"is_not_isolated": 0,
"is_deleted": 0,
"created_at": 1562569842,
"updated_at": 1562569842,
"deleted_at": 0,
"user_pic": "https://pgcupload.flydev.chinamcloud.cn/uploads/cmc_user_avatar/default_header.png"
},
...
],
"_links": {
"self": {
"href": "http://api.pcs-api.localhost/v1/plan-config-column-users?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=685f805f71faedfe215f55bbb01b50c6&filter%5Bconfig_column_id%5D=1&per-page=1023&page=1"
}
},
"_meta": {
"totalCount": 1023,
"pageCount": 1,
"currentPage": 1,
"perPage": 1023
}
}
}
2. The SQL statement is as follows:
SELECT * FROM `pa_config_column_user` WHERE (`config_column_id` IN (SELECT `pa_config_column`.`id` FROM `pa_config_column` LEFT JOIN `pa_config_column_user` ON `pa_config_column`.`id` = `pa_config_column_user`.`config_column_id` AND `pa_config_column_user`.`user_id` = '8' WHERE (`pa_config_column`.`is_deleted`=0) AND (`pa_config_column`.`status`=1) AND (`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`is_deleted`=0) AND (`pa_config_column_user`.`status`=1) AND (`pa_config_column_user`.`user_id`='8') ORDER BY `pa_config_column`.`id` DESC)) AND ((`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`is_deleted`=0) AND (`pa_config_column_user`.`status`=1)) AND (`config_column_id`='1') LIMIT 1023
3. Existing new requirements need to support the fuzzy query of user_nick, but the field: user_nick does not exist in mysql ActiveRecord, but exists in Redis ActiveRecord. And Redis ActiveRecord does not support the Like operator. as shown in Figure 1
Figure 1
4. Although Redis ActiveRecord does not support the LIKE operator, it supports the IN operator. Therefore, it is decided to support the query of the field: CONFIG_COLUMN_ID. In this query result, get the user_id(mysql), and then get the user_nick(redis) of all users to the redis ActiveRecord based on the in operator.
// 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索
if (!$filterUserNickIsEmpty) {
$likeQuery = clone $query;
$configColumnUserUserIds = $likeQuery->select(['user_id'])->asArray()->column();
print_r($configColumnUserUserIds);
/* 查询当前分页的框架服务控制台用户列表 */
$redisCmcConsoleUserItems = RedisCmcConsoleUser::find()->where(['in', 'id', $configColumnUserUserIds])->indexBy('id')->asArray()->all();
$redisCmcConsoleUserUserNicks = ArrayHelper::getColumn($redisCmcConsoleUserItems, 'user_nick');
print_r($redisCmcConsoleUserUserNicks);
exit;
}
Array
(
[0] => 8
[1] => 29
...
)
Array
(
[8] => 13281105967_nick
[29] => cgl_nick
...
)
5. Use PHP to filter similar to SQL Like%search%The value in the array, refer to Q&A: filter values from an array similar to sql like%search%using php, URL:https://stackoverflow.com/questions/5808923/filter-values-from-an-array-similar-to-sql-like-search-using-php, as shown in Figure 2
Figure 2
// 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索
if (!$filterUserNickIsEmpty) {
$likeQuery = clone $query;
$configColumnUserUserIds = $likeQuery->select(['user_id'])->asArray()->column();
/* 查询当前分页的框架服务控制台用户列表 */
$redisCmcConsoleUserItems = RedisCmcConsoleUser::find()->where(['in', 'id', $configColumnUserUserIds])->indexBy('id')->asArray()->all();
$redisCmcConsoleUserUserNicks = ArrayHelper::getColumn($redisCmcConsoleUserItems, 'user_nick');
// 使用 PHP 过滤类似于 SQL LIKE'％search％' 的数组中的值
$input = preg_quote($filterUserNick, '~'); // don't forget to quote input string!
$redisCmcConsoleUserLikeUserNicks = preg_grep('~' . $input . '~', $redisCmcConsoleUserUserNicks);
print_r($redisCmcConsoleUserLikeUserNicks);
exit;
}
Array
(
[8] => 13281105967_nick
)
6. In Postman, open the URL:http://api.pcs-api.localhost/v1/plan-config-column-users?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=685f805f71faedfe215f55bbb01b50c6&filter[config_column_id]=1&filter[user_nick][like]=Test , based on the user’s nickname fuzzy search to obtain: $rediscConsoleUserLikeUserNicks, and then obtain its array key, as a new search condition, the final generated sql is as follows: as expected, as shown in Figure 3
![在 Postman 中，打开网址：http://api.pcs-api.localhost/v1/plan-config-column-users?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=685f805f71faedfe215f55bbb01b50c6&filter[config_column_id]=1&filter[user_nick][like]=test ，基于用户昵称模糊搜索获取到：$redisCmcConsoleUserLikeUserNicks，再获取其数组键，作为新的搜索条件，最终生成的 SQL 如下：符合预期](https://www.shuijingwanwq.com/wp-content/uploads/2019/07/3-5.png)
Figure 3
// 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索
if (!$filterUserNickIsEmpty) {
$likeQuery = clone $query;
$configColumnUserUserIds = $likeQuery->select(['user_id'])->asArray()->column();
/* 查询当前分页的框架服务控制台用户列表 */
$redisCmcConsoleUserItems = RedisCmcConsoleUser::find()->where(['in', 'id', $configColumnUserUserIds])->indexBy('id')->asArray()->all();
$redisCmcConsoleUserUserNicks = ArrayHelper::getColumn($redisCmcConsoleUserItems, 'user_nick');
// 使用 PHP 过滤类似于 SQL LIKE'％search％' 的数组中的值
$input = preg_quote($filterUserNick, '~'); // don't forget to quote input string!
$redisCmcConsoleUserLikeUserNicks = preg_grep('~' . $input . '~', $redisCmcConsoleUserUserNicks);
$query->andWhere(['in', 'user_id', array_keys($redisCmcConsoleUserLikeUserNicks)]);
}
SELECT * FROM `pa_config_column_user` WHERE (`config_column_id` IN (SELECT `pa_config_column`.`id` FROM `pa_config_column` LEFT JOIN `pa_config_column_user` ON `pa_config_column`.`id` = `pa_config_column_user`.`config_column_id` AND `pa_config_column_user`.`user_id` = '8' WHERE (`pa_config_column`.`is_deleted`=0) AND (`pa_config_column`.`status`=1) AND (`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`is_deleted`=0) AND (`pa_config_column_user`.`status`=1) AND (`pa_config_column_user`.`user_id`='8') ORDER BY `pa_config_column`.`id` DESC)) AND ((`pa_config_column_user`.`group_id`='015ce30b116ce86058fa6ab4fea4ac63') AND (`pa_config_column_user`.`is_deleted`=0) AND (`pa_config_column_user`.`status`=1)) AND (`config_column_id`='1') AND (`user_id` IN (185, 186, 187, 191, 194, 204, 205, 206, 207, 208, 1051, 1177, 1178, 1180, 1220, 1339)) LIMIT 16
