In Yii 2.0, when there are multiple associations in the table structure (the same associated field exists in multiple tables), the implementation of query filtering based on ActiveDataFilter
1. Main table: CPA_CHANNEL_APP_TASK and Schedule: CPA_Task: many-to-one. as shown in Figure 1
Figure 1
2, table: cpa_task and Table: CPA_baijia_article|cpa_customize_article|cpa_douyin_article|cp a_netease_article|cpa_qq_article|cpa_weibo_article|cpa_wx_article The association relationship: one-on-one. Table: A record in cpa_task will only exist in Table: CPA_baijia_article|cpa_customize_article|cpa_douyin_article|cp a_netease_article|cpa_qq_article|cpa_weibo_article|cpa_wx_article in one of the tables. As shown in Figure 2, Figure 3, Figure 4
Figure 2
Figure 3
Figure 4
3. Table: CPA_baijia_article|cpa_customize_article|cpa_douyin_article|c PA_Netease_Article|CPA_QQ_Article|CPA_Weibo_Article|CPA_WX_Article Correlation with table: cpa_article_type: many-to-one. As shown in Figure 5, Figure 6, Figure 7
Figure 5
Figure 6
Figure 7
4. At this stage: through the table: cpa_article_type field: code query Main table: cpa_channel_app_task data. as shown in Figure 8
8
5. Edit the search model: /common/logics/channelapptasksearch.php
* @since 1.0
*/
class ChannelAppTaskSearch extends Model
{
public $group_id;
public $task_group_uuid;
public $channel_code;
public $channel_type_code;
public $type;
public $source;
public $source_uuid;
public $source_pub_user_id;
public $channel_app_source_uuid;
public $baijia_article_type_code;
public $customize_article_type_code;
public $douyin_article_type_code;
public $netease_article_type_code;
public $qq_article_type_code;
public $weibo_article_type_code;
public $wx_article_type_code;
public $status;
public $article_title;
public $article_author;
public $article_content;
public $source_article_id;
public $created_at;
/**
* @inheritdoc
*/
public function rules()
{
return [
[['status', 'source_article_id', 'created_at'], 'integer'],
[['group_id', 'channel_code', 'channel_type_code', 'type', 'article_content', 'source', 'baijia_article_type_code', 'customize_article_type_code', 'douyin_article_type_code', 'netease_article_type_code', 'qq_article_type_code', 'weibo_article_type_code', 'wx_article_type_code'], 'string', 'max' => 32],
[['task_group_uuid', 'source_uuid', 'source_pub_user_id', 'channel_app_source_uuid', 'article_author'], 'string', 'max' => 64],
[['article_title'], 'string', 'max' => 255],
[['channel_code'], 'in', 'range' => Channel::find()->select('code')->where(['is_deleted' => Channel::IS_DELETED_NO])->asArray()->column()],
[['channel_type_code'], 'in', 'range' => ChannelType::find()->select('code')->where(['is_deleted' => ChannelType::IS_DELETED_NO])->asArray()->column()],
[['type'], 'in', 'range' => [Task::TYPE_PREVIEW, Task::TYPE_PUB, Task::TYPE_REVOKE]],
[['status'], 'in', 'range' => [ChannelAppTask::STATUS_DISABLED, ChannelAppTask::STATUS_WAIT_PUBLISH, ChannelAppTask::STATUS_PUBLISH, ChannelAppTask::STATUS_PUBLISH_ERROR, ChannelAppTask::STATUS_PLATFORM_REVIEW, ChannelAppTask::STATUS_PLATFORM_PUBLISH, ChannelAppTask::STATUS_PLATFORM_PUBLISHED, ChannelAppTask::STATUS_PLATFORM_UNPUBLISHED, ChannelAppTask::STATUS_PLATFORM_OFFLINE, ChannelAppTask::STATUS_PLATFORM_DELETED]],
[['baijia_article_type_code', 'customize_article_type_code', 'douyin_article_type_code', 'netease_article_type_code', 'qq_article_type_code', 'weibo_article_type_code', 'wx_article_type_code'], 'in', 'range' => ArticleType::find()->select('code')->where(['is_deleted' => ArticleType::IS_DELETED_NO])->asArray()->column()],
];
}
/**
* {@inheritdoc}
*/
public function attributeLabels()
{
return [
'group_id' => Yii::t('model/channel-app-task-search', 'Group ID'),
'task_group_uuid' => Yii::t('model/channel-app-task-search', 'Task Group Uuid'),
'channel_code' => Yii::t('model/channel-app-task-search', 'Channel Code'),
'channel_type_code' => Yii::t('model/channel-app-task-search', 'Channel Type Code'),
'type' => Yii::t('model/channel-app-task-search', 'Type'),
'source' => Yii::t('model/channel-app-task-search', 'Source'),
'source_uuid' => Yii::t('model/channel-app-task-search', 'Source Uuid'),
'source_pub_user_id' => Yii::t('model/channel-app-task-search', 'Source Pub User ID'),
'channel_app_source_uuid' => Yii::t('model/channel-app-task-search', 'Channel App Source Uuid'),
'status' => Yii::t('model/channel-app-task-search', 'Status'),
'baijia_article_type_code' => Yii::t('model/channel-app-task-search', 'Baijia Article Type Code'),
'customize_article_type_code' => Yii::t('model/channel-app-task-search', 'Customize Article Type Code'),
'douyin_article_type_code' => Yii::t('model/channel-app-task-search', 'Douyin Article Type Code'),
'netease_article_type_code' => Yii::t('model/channel-app-task-search', 'Netease Article Type Code'),
'qq_article_type_code' => Yii::t('model/channel-app-task-search', 'Qq Article Type Code'),
'weibo_article_type_code' => Yii::t('model/channel-app-task-search', 'Weibo Article Type Code'),
'wx_article_type_code' => Yii::t('model/channel-app-task-search', 'Wx Article Type Code'),
'article_title' => Yii::t('model/channel-app-task-search', 'Article Title'),
'article_author' => Yii::t('model/channel-app-task-search', 'Article Author'),
'article_content' => Yii::t('model/channel-app-task-search', 'Article Content'),
'source_article_id' => Yii::t('model/channel-app-task-search', 'Source Article ID'),
'created_at' => Yii::t('model/channel-app-task-search', 'Created At'),
];
}
}
6. Edit method file: /api/rests/channel_app_task/indexaction.php, configure AttributeMap, set tables respectively: CPA_Baijia_Article|CPA_Customize_Article|CPA_DOUYIN_AR Ticle|CPA_Netease_Article|CPA_QQ_Article|CPA_WEIBO_Article|CPA_WX_Article The association table: CPA_Article_Type alias: baijia_article_type|customize_article_type|douyin_article_type|ne TEase_Article_Type|QQ_Article_Type|Weibo_Article_Type|WX_Article_Type.
* @since 1.0
*/
class IndexAction extends \yii\rest\IndexAction
{
public $dataFilter = [
'class' => 'yii\data\ActiveDataFilter',
'searchModel' => 'api\models\ChannelAppTaskSearch',
'attributeMap' => [
'group_id' => '{{%task}}.[[group_id]]',
'task_group_uuid' => '{{%channel_app_task}}.[[task_group_uuid]]',
'channel_code' => '{{%channel_app_task}}.[[channel_code]]',
'channel_type_code' => '{{%channel_app_task}}.[[channel_type_code]]',
'type' => '{{%task}}.[[type]]',
'source' => '{{%task}}.[[source]]',
'source_uuid' => '{{%task}}.[[source_uuid]]',
'source_pub_user_id' => '{{%task}}.[[source_pub_user_id]]',
'channel_app_source_uuid' => '{{%channel_app_task}}.[[channel_app_source_uuid]]',
'status' => '{{%channel_app_task}}.[[status]]',
'article_type_code' => '{{%article_type}}.[[code]]',
'baijia_article_type_code' => 'baijia_article_type.[[code]]',
'customize_article_type_code' => 'customize_article_type.[[code]]',
'douyin_article_type_code' => 'douyin_article_type.[[code]]',
'netease_article_type_code' => 'netease_article_type.[[code]]',
'qq_article_type_code' => 'qq_article_type.[[code]]',
'weibo_article_type_code' => 'weibo_article_type.[[code]]',
'wx_article_type_code' => 'wx_article_type.[[code]]',
'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);
}
}
}
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 ChannelAppTask */
$modelClass = $this->modelClass;
$query = $modelClass::find()
->select([
$modelClass::tableName() . '.*',
Task::tableName() . '.group_id',
Task::tableName() . '.type',
Task::tableName() . '.source',
Task::tableName() . '.source_uuid',
Task::tableName() . '.source_pub_user_id',
Task::tableName() . '.source_callback_url',
'pub_log_code' => PubLog::tableName() . '.code',
'pub_log_message' => PubLog::tableName() . '.message',
])
->joinWith(['task.baijiaArticle.articleType baijia_article_type'], true)
->joinWith(['task.customizeArticle.articleType customize_article_type'], true)
->joinWith(['task.douyinArticle.articleType douyin_article_type'], true)
->joinWith(['task.neteaseArticle.articleType netease_article_type'], true)
->joinWith(['task.qqArticle.articleType qq_article_type'], true)
->joinWith(['task.weiboArticle.articleType weibo_article_type'], true)
->joinWith(['task.wxArticle.articleType wx_article_type'], true)
->joinWith(['pubLog'], false)
->where([
$modelClass::tableName() . '.is_deleted' => ChannelAppTask::IS_DELETED_NO,
Task::tableName() . '.is_deleted' => Task::IS_DELETED_NO,
PubLog::tableName() . '.is_deleted' => PubLog::IS_DELETED_NO,
$modelClass::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED,
])
->andWhere(['in', Task::tableName() . '.group_id', $groupIds])
->asArray();
if (!empty($filter)) {
$query->andFilterWhere($filter);
}
return Yii::createObject([
'class' => ActiveDataProvider::className(),
'query' => $query,
'pagination' => [
'params' => $requestParams,
],
'sort' => [
'params' => $requestParams,
],
]);
}
}
7. Edit the serialization file: /api/rests/channel_app_task/serializer.php. Reset data based on channel code.
* @since 1.0
*/
class Serializer extends \yii\rest\Serializer
{
/**
* Serializes a data provider.
* @param DataProviderInterface $dataProvider
* @return array the array representation of the data provider.
*/
protected function serializeDataProvider($dataProvider)
{
if ($this->preserveKeys) {
$models = $dataProvider->getModels();
} else {
$models = array_values($dataProvider->getModels());
}
$models = $this->serializeModels($models);
if (($pagination = $dataProvider->getPagination()) !== false) {
$this->addPaginationHeaders($pagination);
}
if ($this->request->getIsHead()) {
return null;
} elseif ($this->collectionEnvelope === null) {
return $models;
}
$result = [
$this->collectionEnvelope => $models,
];
/* 查询当前分页的租户列表 */
$cmcApiGroupServiceGroups = [];
if (!empty($result['items'])) {
$groupIds = ArrayHelper::getColumn($result['items'], 'group_id');
$groupIdsUnique = array_unique($groupIds);
// HTTP 请求，获取租户列表(二维数组)
$cmcApiGroupServiceGroupList = CmcApiGroupService::getGroupList($groupIdsUnique);
$cmcApiGroupServiceGroups = $cmcApiGroupServiceGroupList['data'];
}
foreach ($result['items'] as $i => $item) {
/* 基于渠道代码重置数据 */
if (in_array($item['channel_code'], [Channel::CODE_BAIJIA, Channel::CODE_CUSTOMIZE, Channel::CODE_DOUYIN, Channel::CODE_NETEASE, Channel::CODE_QQ, Channel::CODE_WEIBO, Channel::CODE_WX])) {
$result['items'][$i]['article_type_id'] = (int) ArrayHelper::getValue($item, ['task', $item['channel_code'] . 'Article', 'articleType', 'id']);
$result['items'][$i]['article_type_code'] = ArrayHelper::getValue($item, ['task', $item['channel_code'] . 'Article', 'articleType', 'code']);
$result['items'][$i]['article_type_name'] = ArrayHelper::getValue($item, ['task', $item['channel_code'] . 'Article', 'articleType', 'name']);
} else {
$result['items'][$i]['article_type_id'] = 0;
$result['items'][$i]['article_type_code'] = '';
$result['items'][$i]['article_type_name'] = '';
}
$result['items'][$i]['id'] = (int) $item['id'];
$result['items'][$i]['task_group_id'] = (int) $item['task_group_id'];
$result['items'][$i]['channel_id'] = (int) $item['channel_id'];
$result['items'][$i]['channel_type_id'] = (int) $item['channel_type_id'];
$result['items'][$i]['channel_app_source_id'] = (int) $item['channel_app_source_id'];
$result['items'][$i]['task_id'] = (int) $item['task_id'];
$result['items'][$i]['status'] = (int) $item['status'];
$result['items'][$i]['is_deleted'] = (int) $item['is_deleted'];
$result['items'][$i]['created_at'] = (int) $item['created_at'];
$result['items'][$i]['updated_at'] = (int) $item['updated_at'];
$result['items'][$i]['deleted_at'] = (int) $item['deleted_at'];
// $result['items'][$i]['source_article_id'] = (int) $item['source_article_id'];
$result['items'][$i]['have_pub_number'] = (int) $item['have_pub_number'];
$result['items'][$i]['pub_log_code'] = isset($item['pub_log_code']) ? (int) $item['pub_log_code'] : 0;
$result['items'][$i]['pub_log_message'] = isset($item['pub_log_message']) ? $item['pub_log_message'] : '';
$result['items'][$i]['group_name'] = $cmcApiGroupServiceGroups[$item['group_id']]['group_name'] ?? '';
/* 销毁属性 */
unset(
$result['items'][$i]['task']
);
}
if ($pagination !== false) {
return ['code' => 10000, 'message' => Yii::t('success', '126006'), 'data' => array_merge($result, $this->serializePagination($pagination))];
}
return ['code' => 10000, 'message' => Yii::t('success', '126006'), 'data' => $result];
}
}
8. Gethttp://api.channel-pub-api.localhost/v1/channel-app-tasks?group_id=c10e87f39873512a16727e17f57456a5&filter[and][0][or][0][baijia_article_type_code]=video&filter[and][0][or][1][customize_article_type_code]=video&filter[and][0][or][2][douyin_article_type_code]=video&filter[and][0][or][3][netease_article_type_code]=video&filter[and][0][or][4][qq_article_type_code]=video&filter[and][0][or][5][weibo_article_type_code]=video&filter[and][0][or][6][wx_article_type_code]=video . response data. as shown in Figure 9
![GET http://api.channel-pub-api.localhost/v1/channel-app-tasks?group_id=c10e87f39873512a16727e17f57456a5&filter[and][0][or][0][baijia_article_type_code]=video&filter[and][0][or][1][customize_article_type_code]=video&filter[and][0][or][2][douyin_article_type_code]=video&filter[and][0][or][3][netease_article_type_code]=video&filter[and][0][or][4][qq_article_type_code]=video&filter[and][0][or][5][weibo_article_type_code]=video&filter[and][0][or][6][wx_article_type_code]=video 。响应数据。](https://www.shuijingwanwq.com/wp-content/uploads/2020/10/9.png)
Figure 9
{
"code": 10000,
"message": "获取渠道的应用的来源列表成功",
"data": {
"items": [
{
"id": 6,
"uuid": "76b69c80d14811eab76454ee75d2ebc1",
"task_group_id": 0,
"task_group_uuid": "",
"channel_id": 4,
"channel_code": "baijia",
"channel_type_id": 5,
"channel_type_code": "baijia",
"channel_app_source_id": 20,
"channel_app_source_uuid": "362d8170d14711eabb5954ee75d2ebc1",
"task_id": 6,
"have_pub_number": 2,
"status": 6,
"is_deleted": 0,
"created_at": 1595991973,
"updated_at": 1595991973,
"deleted_at": 0,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"type": "pub",
"source": "spider",
"source_uuid": "dc1ec2188c4d8cd91111aa5055439b1q",
"source_pub_user_id": "1012",
"source_callback_url": "http://wjdev.chinamcloud.com:8609/cms/api/thirdPush/callBack",
"pub_log_code": 10000,
"pub_log_message": "百家号应用文章发布成功，状态：已发布",
"article_type_id": 3,
"article_type_code": "video",
"article_type_name": "视频",
"group_name": "深圳市"
},
{
"id": 7,
"uuid": "89094034d15911ea912f54ee75d2ebc1",
"task_group_id": 0,
"task_group_uuid": "",
"channel_id": 4,
"channel_code": "baijia",
"channel_type_id": 5,
"channel_type_code": "baijia",
"channel_app_source_id": 20,
"channel_app_source_uuid": "362d8170d14711eabb5954ee75d2ebc1",
"task_id": 7,
"have_pub_number": 2,
"status": 6,
"is_deleted": 0,
"created_at": 1595999304,
"updated_at": 1595999304,
"deleted_at": 0,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"type": "pub",
"source": "spider",
"source_uuid": "dc1ec2188c4d8cd91111aa5055439b1q",
"source_pub_user_id": "1012",
"source_callback_url": "http://wjdev.chinamcloud.com:8609/cms/api/thirdPush/callBack",
"pub_log_code": 10000,
"pub_log_message": "百家号应用文章发布成功，状态：已发布",
"article_type_id": 3,
"article_type_code": "video",
"article_type_name": "视频",
"group_name": "深圳市"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/v1/channel-app-tasks?group_id=c10e87f39873512a16727e17f57456a5&filter%5Band%5D%5B0%5D%5Bor%5D%5B0%5D%5Bbaijia_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B1%5D%5Bcustomize_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B2%5D%5Bdouyin_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B3%5D%5Bnetease_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B4%5D%5Bqq_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B5%5D%5Bweibo_article_type_code%5D=video&filter%5Band%5D%5B0%5D%5Bor%5D%5B6%5D%5Bwx_article_type_code%5D=video&page=1"
}
},
"_meta": {
"totalCount": 2,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
9. Generate SQL statements, in line with expectations. As shown in Figure 10
Figure 10
SELECT `cpa_channel_app_task`.*, `cpa_task`.`group_id`, `cpa_task`.`type`, `cpa_task`.`source`, `cpa_task`.`source_uuid`, `cpa_task`.`source_pub_user_id`, `cpa_task`.`source_callback_url`, `cpa_pub_log`.`code` AS `pub_log_code`, `cpa_pub_log`.`message` AS `pub_log_message` FROM `cpa_channel_app_task` LEFT JOIN `cpa_task` ON `cpa_channel_app_task`.`task_id` = `cpa_task`.`id` LEFT JOIN `cpa_baijia_article` ON `cpa_task`.`id` = `cpa_baijia_article`.`task_id` LEFT JOIN `cpa_article_type` `baijia_article_type` ON `cpa_baijia_article`.`article_type_id` = `baijia_article_type`.`id` LEFT JOIN `cpa_customize_article` ON `cpa_task`.`id` = `cpa_customize_article`.`task_id` LEFT JOIN `cpa_article_type` `customize_article_type` ON `cpa_customize_article`.`article_type_id` = `customize_article_type`.`id` LEFT JOIN `cpa_douyin_article` ON `cpa_task`.`id` = `cpa_douyin_article`.`task_id` LEFT JOIN `cpa_article_type` `douyin_article_type` ON `cpa_douyin_article`.`article_type_id` = `douyin_article_type`.`id` LEFT JOIN `cpa_netease_article` ON `cpa_task`.`id` = `cpa_netease_article`.`task_id` LEFT JOIN `cpa_article_type` `netease_article_type` ON `cpa_netease_article`.`article_type_id` = `netease_article_type`.`id` LEFT JOIN `cpa_qq_article` ON `cpa_task`.`id` = `cpa_qq_article`.`task_id` LEFT JOIN `cpa_article_type` `qq_article_type` ON `cpa_qq_article`.`article_type_id` = `qq_article_type`.`id` LEFT JOIN `cpa_weibo_article` ON `cpa_task`.`id` = `cpa_weibo_article`.`task_id` LEFT JOIN `cpa_article_type` `weibo_article_type` ON `cpa_weibo_article`.`article_type_id` = `weibo_article_type`.`id` LEFT JOIN `cpa_wx_article` ON `cpa_task`.`id` = `cpa_wx_article`.`task_id` LEFT JOIN `cpa_article_type` `wx_article_type` ON `cpa_wx_article`.`article_type_id` = `wx_article_type`.`id` LEFT JOIN `cpa_pub_log` ON `cpa_channel_app_task`.`id` = `cpa_pub_log`.`channel_app_task_id` WHERE ((`cpa_channel_app_task`.`is_deleted`=0) AND (`cpa_task`.`is_deleted`=0) AND (`cpa_pub_log`.`is_deleted`=0) AND (`cpa_channel_app_task`.`status`=6)) AND (`cpa_task`.`group_id` IN ('c10e87f39873512a16727e17f57456a5', '015ce30b116ce86058fa6ab4fea4ac63', '4fd58ceba1fbc537b5402302702131eb', 'e774bfcf8fc4cfe2ce57ac875a266e94', '9852bb7c32a44709cd748180784fcb81')) AND ((baijia_article_type.`code`='video') OR (customize_article_type.`code`='video') OR (douyin_article_type.`code`='video') OR (netease_article_type.`code`='video') OR (qq_article_type.`code`='video') OR (weibo_article_type.`code`='video') OR (wx_article_type.`code`='video')) LIMIT 20
SELECT * FROM `cpa_task` WHERE `id` IN ('6', '7')
SELECT * FROM `cpa_baijia_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_article_type` `baijia_article_type` WHERE `id`='3'
SELECT * FROM `cpa_customize_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_douyin_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_netease_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_qq_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_weibo_article` WHERE `task_id` IN ('6', '7')
SELECT * FROM `cpa_wx_article` WHERE `task_id` IN ('6', '7')
