In Yii 2.0, when connecting two tables, specify some additional conditions in the ON section of the join query, based on the implementation of the ActiveDataFilter query filter
1. The current requirements are as follows. Based on the time range of article publishing, it is necessary to filter account rankings based on the number of articles published. as shown in Figure 1
Figure 1
2. View the method file of the obtained account list, the code is as follows
* @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, gethttp://api.channel-pub-api.localhost/v1/channel-app-sources?group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count。Sometimes, when connecting two tables, you may need to specify some additional conditions in the ON section of the join query. This can be done by calling the yii\db\ActiveQuery::onCondition() method, as shown below:
->joinWith([
'channelAppTasks' => function ($query) {
$query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
},
], false)
4. SQL section: 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) Represents an association table When cpa_channel_app_task, you need to specify its status equal to 6. The resulting SQL statement is as follows
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. Now, since the creation time of the records to support the creation time of the table CPA_CHANNEL_APP_TASK needs to be realized, the following SQL query needs to be implemented
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. Edit the method file to obtain the account list, add a new filter to configure its propertiesFilterAttributeName=>channel_app_task_filter, . The code is as follows
* @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. Gethttp://api.channel-pub-api.localhost/v1/channel-app-sources?filter[status]=1&per-page=5&channel_app_task_filter[created_at][gte]=159591435&channel_app_task_filter[created_at][lte]=1600334194&group_id=C10E87F39873512A1672 7E17F57456A5&sort=-channel_app_task_count . The response is as follows, as expected, as shown in Figure 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)
Figure 2
{
"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": "华栖云秀",
"avatar": "http://inews.gtimg.com/newsapp_ls/0/4867371999_200200/0",
"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": {
"href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=1"
},
"next": {
"href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=2"
},
"last": {
"href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=2"
}
},
"_meta": {
"totalCount": 9,
"pageCount": 2,
"currentPage": 1,
"perPage": 5
}
}
}
8. The final generated SQL is as follows, as shown in Figure 3
Figure 3
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
