在 Yii 2.0 中，当连接两个表时，在 JOIN 查询的 ON 部分中指定一些额外的条件，基于 ActiveDataFilter 查询过滤器的实现
1、现在的需求如下，需要基于文章发布的时间范围，筛选基于文章发布数量的帐号排行。如图1
图1
2、查看获取帐号列表的方法文件，代码如下
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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 | <?php/** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */namespace api\rests\channel_app_source;use Yii;use api\models\Channel;use api\models\ChannelType;use api\models\ChannelAppSource;use api\models\ChannelAppTask;use api\services\CmcApiGroupService;use yii\base\InvalidConfigException;use yii\data\DataFilter;use yii\data\ActiveDataProvider;use yii\helpers\ArrayHelper;use yii\web\UnprocessableEntityHttpException;use yii\web\HttpException;use yii\web\ServerErrorHttpException;/** * 获取渠道的应用的来源列表：/channel-app-sources(channel-app-source/index) * * For more details and usage information on IndexAction, see the [guide channel_app_source on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */class IndexAction extends \yii\rest\IndexAction{ public $dataFilter = [ 'class' => 'yii\data\ActiveDataFilter', 'searchModel' => 'api\models\ChannelAppSourceSearch', 'attributeMap' => [ 'group_id' => '{{%channel_app_source}}.[[group_id]]', 'uuid' => '{{%channel_app_source}}.[[uuid]]', 'channel_code' => '{{%channel_app_source}}.[[channel_code]]', 'channel_type_code' => '{{%channel_app_source}}.[[channel_type_code]]', 'source' => '{{%channel_app_source}}.[[source]]', 'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]', 'permission' => '{{%channel_app_source}}.[[permission]]', 'status' => '{{%channel_app_source}}.[[status]]', 'created_at' => '{{%channel_app_source}}.[[created_at]]', ], ]; /** * Prepares the data provider that should return the requested collection of the models. * @return mixed|object|ActiveDataProvider|DataFilter|null * @throws HttpException * @throws InvalidConfigException if the configuration is invalid. * @throws ServerErrorHttpException * @throws UnprocessableEntityHttpException */ protected function prepareDataProvider() { $requestParams = Yii::$app->getRequest()->getQueryParams(); $filter = null; if ($this->dataFilter !== null) { $this->dataFilter = Yii::createObject($this->dataFilter); if ($this->dataFilter->load($requestParams)) { $filter = $this->dataFilter->build(); if ($filter === false) { $firstError = ''; foreach ($this->dataFilter->getFirstErrors() as $message) { $firstError = $message; break; } throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013); } } } if ($this->prepareDataProvider !== null) { return call_user_func($this->prepareDataProvider, $this, $filter); } // 获取租户ID列表(二维数组，场景：筛选用户时，添加默认条件：租户ID仅限于当前租户ID|其所管辖的下级租户ID) $groupListTwo = CmcApiGroupService::getGroupListTwo(Yii::$app->params['groupId'], 'group_sub_tree', '1'); $groupIds = ArrayHelper::getColumn($groupListTwo, 'group_id'); /* @var $modelClass ChannelAppSource */ $modelClass = $this->modelClass; $query = $modelClass::find() ->select([ $modelClass::tableName() . '.*', 'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count' ]) ->joinWith('channel') ->joinWith('channelType') ->joinWith('baijiaApp') ->joinWith('qqCwApp') ->joinWith('qqTpAppPenguin') ->joinWith('wxApp') ->joinWith('weiboWeiboConnectWebAppUser') ->joinWith('customizeApp') ->joinWith('neteaseTpAppUser') ->joinWith([ 'channelAppTasks' => function ($query) { $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]); }, ], false) ->where([ Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO, ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO, $modelClass::tableName() . '.is_deleted' => $modelClass::IS_DELETED_NO, ]) ->andWhere(['in', $modelClass::tableName() . '.group_id', $groupIds]) ->asArray() ->groupBy([$modelClass::tableName() . '.id']); if (!empty($filter)) { $query->andFilterWhere($filter); } return Yii::createObject([ 'class' => ActiveDataProvider::className(), 'query' => $query, 'pagination' => [ 'params' => $requestParams, ], 'sort' => [ 'defaultOrder' => [ 'id' => SORT_DESC, ], 'attributes' => [ 'id' => [ 'asc' => ['id' => SORT_ASC], 'desc' => ['id' => SORT_DESC], 'default' => SORT_ASC, ], 'fans_count' => [ 'asc' => ['fans_count' => SORT_ASC, 'id' => SORT_ASC], 'desc' => ['fans_count' => SORT_DESC, 'id' => SORT_DESC], 'default' => SORT_ASC, ], 'channel_app_task_count' => [ 'asc' => ['channel_app_task_count' => SORT_ASC, 'id' => SORT_ASC], 'desc' => ['channel_app_task_count' => SORT_DESC, 'id' => SORT_DESC], 'default' => SORT_ASC, ], ], 'params' => $requestParams, ], ]); }} |
3、GET http://api.channel-pub-api.localhost/v1/channel-app-sources?group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count。 有时，当连接两个表时，你可能需要在 JOIN 查询的 ON 部分中指定一些额外的条件。这可以通过调用 yii\db\ActiveQuery::onCondition() 方法来完成，如下所示：
1 2 3 4 5 | ->joinWith([ 'channelAppTasks' => function ($query) { $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]); },], false) |
4、SQL 部分：LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND (`cpa_channel_app_task`.`status`=6) 表示关联表 cpa_channel_app_task 时，需要指定其 status 等于 6。最终生成的 SQL 语句如下
1 | SELECT `cpa_channel_app_source`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_channel_app_source` LEFT JOIN `cpa_channel` ON `cpa_channel_app_source`.`channel_id` = `cpa_channel`.`id` LEFT JOIN `cpa_channel_type` ON `cpa_channel_app_source`.`channel_type_id` = `cpa_channel_type`.`id` LEFT JOIN `cpa_qq_cw_app` ON `cpa_channel_app_source`.`id` = `cpa_qq_cw_app`.`channel_app_source_id` LEFT JOIN `cpa_qq_tp_app_penguin` ON `cpa_channel_app_source`.`id` = `cpa_qq_tp_app_penguin`.`channel_app_source_id` LEFT JOIN `cpa_wx_app` ON `cpa_channel_app_source`.`id` = `cpa_wx_app`.`channel_app_source_id` LEFT JOIN `cpa_weibo_weibo_connect_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_customize_app` ON `cpa_channel_app_source`.`id` = `cpa_customize_app`.`channel_app_source_id` LEFT JOIN `cpa_netease_tp_app_user` ON `cpa_channel_app_source`.`id` = `cpa_netease_tp_app_user`.`channel_app_source_id` LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND (`cpa_channel_app_task`.`status`=6) WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`group_id` IN ('015ce30b116ce86058fa6ab4fea4ac63', '4fd58ceba1fbc537b5402302702131eb')) GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 20 |
5、现在由于需要支持表 cpa_channel_app_task 的记录的创建时间的筛选，因此，需要实现如下的 SQL 查询
1 | LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND ((`cpa_channel_app_task`.`status`=6) AND ((`cpa_channel_app_task`.`created_at` >= '1600334142') AND (`cpa_channel_app_task`.`created_at` <= '1600334142'))) |
6、编辑获取帐号列表的方法文件，新增加一个过滤器，配置其属性 ‘filterAttributeName’ => ‘channel_app_task_filter’, 。代码如下
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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 | <?php/** * @link http://www.yiiframework.com/ * @copyright Copyright (c) 2008 Yii Software LLC * @license http://www.yiiframework.com/license/ */namespace api\rests\channel_app_source;use Yii;use api\models\Channel;use api\models\ChannelType;use api\models\ChannelAppSource;use api\models\ChannelAppTask;use api\services\CmcApiGroupService;use yii\base\InvalidConfigException;use yii\data\DataFilter;use yii\data\ActiveDataProvider;use yii\helpers\ArrayHelper;use yii\web\UnprocessableEntityHttpException;use yii\web\HttpException;use yii\web\ServerErrorHttpException;/** * 获取渠道的应用的来源列表：/channel-app-sources(channel-app-source/index) * * For more details and usage information on IndexAction, see the [guide channel_app_source on rest controllers](guide:rest-controllers). * * @author Qiang Wang <shuijingwanwq@163.com> * @since 1.0 */class IndexAction extends \yii\rest\IndexAction{ public $dataFilter = [ 'class' => 'yii\data\ActiveDataFilter', 'searchModel' => 'api\models\ChannelAppSourceSearch', 'attributeMap' => [ 'group_id' => '{{%channel_app_source}}.[[group_id]]', 'uuid' => '{{%channel_app_source}}.[[uuid]]', 'channel_code' => '{{%channel_app_source}}.[[channel_code]]', 'channel_type_code' => '{{%channel_app_source}}.[[channel_type_code]]', 'source' => '{{%channel_app_source}}.[[source]]', 'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]', 'permission' => '{{%channel_app_source}}.[[permission]]', 'status' => '{{%channel_app_source}}.[[status]]', 'created_at' => '{{%channel_app_source}}.[[created_at]]', ], ]; public $channelAppTaskDataFilter = [ 'class' => 'yii\data\ActiveDataFilter', 'filterAttributeName' => 'channel_app_task_filter', 'searchModel' => 'api\models\ChannelAppSourceSearch', 'attributeMap' => [ 'created_at' => '{{%channel_app_task}}.[[created_at]]', ], ]; /** * Prepares the data provider that should return the requested collection of the models. * @return mixed|object|ActiveDataProvider|DataFilter|null * @throws HttpException * @throws InvalidConfigException if the configuration is invalid. * @throws ServerErrorHttpException * @throws UnprocessableEntityHttpException */ protected function prepareDataProvider() { $requestParams = Yii::$app->getRequest()->getQueryParams(); $filter = null; if ($this->dataFilter !== null) { $this->dataFilter = Yii::createObject($this->dataFilter); if ($this->dataFilter->load($requestParams)) { $filter = $this->dataFilter->build(); if ($filter === false) { $firstError = ''; foreach ($this->dataFilter->getFirstErrors() as $message) { $firstError = $message; break; } throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013); } } } $channelAppTaskFilter = null; if ($this->channelAppTaskDataFilter !== null) { $this->channelAppTaskDataFilter = Yii::createObject($this->channelAppTaskDataFilter); if ($this->channelAppTaskDataFilter->load($requestParams)) { $channelAppTaskFilter = $this->channelAppTaskDataFilter->build(); if ($channelAppTaskFilter === false) { $firstError = ''; foreach ($this->channelAppTaskDataFilter->getFirstErrors() as $message) { $firstError = $message; break; } throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013); } } } if ($this->prepareDataProvider !== null) { return call_user_func($this->prepareDataProvider, $this, $filter); } // 获取租户ID列表(二维数组，场景：筛选用户时，添加默认条件：租户ID仅限于当前租户ID|其所管辖的下级租户ID) $groupListTwo = CmcApiGroupService::getGroupListTwo(Yii::$app->params['groupId'], 'group_sub_tree', '1'); $groupIds = ArrayHelper::getColumn($groupListTwo, 'group_id'); /* @var $modelClass ChannelAppSource */ $modelClass = $this->modelClass; Yii::$app->params['channelAppTaskFilter'] = $channelAppTaskFilter; $query = $modelClass::find() ->select([ $modelClass::tableName() . '.*', 'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count' ]) ->joinWith('channel') ->joinWith('channelType') ->joinWith('baijiaApp') ->joinWith('douyinWeiboAppUser') ->joinWith('qqCwApp') ->joinWith('qqTpAppPenguin') ->joinWith('wxApp') ->joinWith('weiboWeiboConnectWebAppUser') ->joinWith('customizeApp') ->joinWith('neteaseTpAppUser') ->joinWith([ 'channelAppTasks' => function ($query) { $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED])->andOnCondition(Yii::$app->params['channelAppTaskFilter']); }, ], false) ->where([ Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO, ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO, $modelClass::tableName() . '.is_deleted' => $modelClass::IS_DELETED_NO, ]) ->andWhere(['in', $modelClass::tableName() . '.group_id', $groupIds]) ->asArray() ->groupBy([$modelClass::tableName() . '.id']); if (!empty($filter)) { $query->andFilterWhere($filter); } return Yii::createObject([ 'class' => ActiveDataProvider::className(), 'query' => $query, 'pagination' => [ 'params' => $requestParams, ], 'sort' => [ 'defaultOrder' => [ 'id' => SORT_DESC, ], 'attributes' => [ 'id' => [ 'asc' => ['id' => SORT_ASC], 'desc' => ['id' => SORT_DESC], 'default' => SORT_ASC, ], 'fans_count' => [ 'asc' => ['fans_count' => SORT_ASC, 'id' => SORT_ASC], 'desc' => ['fans_count' => SORT_DESC, 'id' => SORT_DESC], 'default' => SORT_ASC, ], 'channel_app_task_count' => [ 'asc' => ['channel_app_task_count' => SORT_ASC, 'id' => SORT_ASC], 'desc' => ['channel_app_task_count' => SORT_DESC, 'id' => SORT_DESC], 'default' => SORT_ASC, ], ], 'params' => $requestParams, ], ]); }} |
7、GET http://api.channel-pub-api.localhost/v1/channel-app-sources?filter[status]=1&per-page=5&channel_app_task_filter[created_at][gte]=1595991435&channel_app_task_filter[created_at][lte]=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count 。响应如下，符合预期，如图2
![GET http://api.channel-pub-api.localhost/v1/channel-app-sources?filter[status]=1&per-page=5&channel_app_task_filter[created_at][gte]=1595991435&channel_app_task_filter[created_at][lte]=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count 。响应如下，符合预期](https://www.shuijingwanwq.com/wp-content/uploads/2020/10/2-3.png)
图2
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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 | { "code": 10000, "message": "获取渠道的应用的来源列表成功", "data": { "items": [ { "id": 28, "group_id": "015ce30b116ce86058fa6ab4fea4ac63", "group_name": "深圳市", "uuid": "4851b0eef81911ea8c9954ee75d2ebc1", "channel_id": 3, "channel_code": "weibo", "channel_type_id": 4, "channel_type_code": "weibo_weibo_connect_web", "name": "永夜烟", "avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7", "fans_count": 0, "source": "spider", "source_uuid": "825e6d5e36468cc4bf536799ce3565cf", "permission": 2, "status": 1, "is_deleted": 0, "created_at": 1600259805, "updated_at": 1600259805, "deleted_at": 0, "channel_app_task_count": 2, "channel_name": "微博", "channel_type_name": "微博的微连接的网页应用" }, { "id": 20, "group_id": "015ce30b116ce86058fa6ab4fea4ac63", "group_name": "深圳市", "uuid": "362d8170d14711eabb5954ee75d2ebc1", "channel_id": 4, "channel_code": "baijia", "channel_type_id": 5, "channel_type_code": "baijia", "name": "栖云天下通", "avatar": "", "fans_count": 0, "source": "spider", "source_uuid": "dc1ec2188c4d8cd91111aa5055439b1w", "permission": 2, "status": 1, "is_deleted": 0, "created_at": 1595991435, "updated_at": 1595991435, "deleted_at": 0, "channel_app_task_count": 2, "channel_name": "百家号", "channel_type_name": "百家号" }, { "id": 32, "group_id": "015ce30b116ce86058fa6ab4fea4ac63", "group_name": "深圳市", "uuid": "aaa98ad6fed211ea9bc954ee75d2ebc1", "channel_id": 1, "channel_code": "qq", "channel_type_id": 2, "channel_type_code": "qq_tp", "name": "华栖云秀", "fans_count": 0, "source": "spider", "source_uuid": "825e6d5e36468cc4bf536799ce3565cf", "permission": 3, "status": 1, "is_deleted": 0, "created_at": 1600999133, "updated_at": 1600999133, "deleted_at": 0, "channel_app_task_count": 0, "channel_name": "企鹅号", "channel_type_name": "企鹅号的第三方服务平台应用" }, { "id": 31, "group_id": "015ce30b116ce86058fa6ab4fea4ac63", "group_name": "深圳市", "uuid": "03111c38f8c111eaa0f154ee75d2ebc1", "channel_id": 3, "channel_code": "weibo", "channel_type_id": 4, "channel_type_code": "weibo_weibo_connect_web", "name": "terryhong123", "avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83", "fans_count": 0, "source": "spider", "source_uuid": "825e6d5e36468cc4bf536799ce3565cf", "permission": 2, "status": 1, "is_deleted": 0, "created_at": 1600331844, "updated_at": 1600331844, "deleted_at": 0, "channel_app_task_count": 0, "channel_name": "微博", "channel_type_name": "微博的微连接的网页应用" }, { "id": 30, "group_id": "015ce30b116ce86058fa6ab4fea4ac63", "group_name": "深圳市", "uuid": "66cc5808f8b911eaa86f54ee75d2ebc1", "channel_id": 3, "channel_code": "weibo", "channel_type_id": 4, "channel_type_code": "weibo_weibo_connect_web", "name": "右可在可蜚百里好87fr在章旭飞55", "avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU", "fans_count": 0, "source": "spider", "source_uuid": "825e6d5e36468cc4bf536799ce3565cf", "permission": 2, "status": 1, "is_deleted": 0, "created_at": 1600328576, "updated_at": 1600328576, "deleted_at": 0, "channel_app_task_count": 0, "channel_name": "微博", "channel_type_name": "微博的微连接的网页应用" } ], "_links": { "self": { }, "next": { }, "last": { } }, "_meta": { "totalCount": 9, "pageCount": 2, "currentPage": 1, "perPage": 5 } }} |
8、最终生成的 SQL 如下，如图3
图3
1 | SELECT `cpa_channel_app_source`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_channel_app_source` LEFT JOIN `cpa_channel` ON `cpa_channel_app_source`.`channel_id` = `cpa_channel`.`id` LEFT JOIN `cpa_channel_type` ON `cpa_channel_app_source`.`channel_type_id` = `cpa_channel_type`.`id` LEFT JOIN `cpa_baijia_app` ON `cpa_channel_app_source`.`id` = `cpa_baijia_app`.`channel_app_source_id` LEFT JOIN `cpa_douyin_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_douyin_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_qq_cw_app` ON `cpa_channel_app_source`.`id` = `cpa_qq_cw_app`.`channel_app_source_id` LEFT JOIN `cpa_qq_tp_app_penguin` ON `cpa_channel_app_source`.`id` = `cpa_qq_tp_app_penguin`.`channel_app_source_id` LEFT JOIN `cpa_wx_app` ON `cpa_channel_app_source`.`id` = `cpa_wx_app`.`channel_app_source_id` LEFT JOIN `cpa_weibo_weibo_connect_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_customize_app` ON `cpa_channel_app_source`.`id` = `cpa_customize_app`.`channel_app_source_id` LEFT JOIN `cpa_netease_tp_app_user` ON `cpa_channel_app_source`.`id` = `cpa_netease_tp_app_user`.`channel_app_source_id` LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND ((`cpa_channel_app_task`.`status`=6) AND ((`cpa_channel_app_task`.`created_at` >= '1595991435') AND (`cpa_channel_app_task`.`created_at` <= '1600334194'))) WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`group_id` IN ('c10e87f39873512a16727e17f57456a5', '015ce30b116ce86058fa6ab4fea4ac63', '4fd58ceba1fbc537b5402302702131eb', 'e774bfcf8fc4cfe2ce57ac875a266e94', '9852bb7c32a44709cd748180784fcb81')) AND (`cpa_channel_app_source`.`status`='1') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 5 |
近期评论