在 Yii 2.0 中，需要同时基于 MySQL ActiveRecord 与 Redis ActiveRecord 查询资源的实现
1、在 Postman 中，打开网址：http://api.pcs-api.localhost/v1/plan-config-column-users?login_id=2e368664c41b8bf511bcc9c65d86dbc3&login_tid=685f805f71faedfe215f55bbb01b50c6&filter[config_column_id]=1 ，仅支持字段：config_column_id 的查询，响应如下：
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 | { "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, }, ... ], "_links": { "self": { } }, "_meta": { "totalCount": 1023, "pageCount": 1, "currentPage": 1, "perPage": 1023 } }} |
2、SQL 语句如下：
1 | 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、现有新的需求，需要支持字段：user_nick 的模糊查询，但是字段：user_nick 不存在于 MySQL ActiveRecord 中，而是存在于 Redis ActiveRecord 中。且 Redis ActiveRecord 不支持 like 操作符。如图1
图1
4、虽然 Redis ActiveRecord 不支持 like 操作符，但是其支持 in 操作符，因此，决定先支持字段：config_column_id 的查询，在此查询结果中，获取所有用户的 user_id(MySQL)，然后基于 in 操作符获取到 Redis ActiveRecord 的所有用户的 user_nick(Redis)。
1 2 3 4 5 6 7 8 9 10 11 12 | // 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索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;} |
1 2 3 4 5 6 | Array( [0] => 8 [1] => 29 ...) |
1 2 3 4 5 6 | Array( [8] => 13281105967_nick [29] => cgl_nick ...) |
5、使用 PHP 过滤类似于 SQL LIKE’％search％’ 的数组中的值，参考问答：filter values from an array similar to SQL LIKE ‘%search%’ using PHP，网址：https://stackoverflow.com/questions/5808923/filter-values-from-an-array-similar-to-sql-like-search-using-php ，如图2
图2
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | // 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索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;} |
1 2 3 4 | Array( [8] => 13281105967_nick) |
6、在 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 如下：符合预期，如图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 如下：符合预期](http://www.shuijingwanwq.com/wp-content/uploads/2019/07/3-5.png)
图3
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | // 判断用户昵称是否为空，如果不为空，则查询当前分页的框架服务控制台用户列表，然后基于用户昵称模糊搜索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)]);} |
1 | 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 |
近期评论