In Yii 2.0, the sorting of alias fields based on the ActiveDataFilter implementation is sorted
* @since 1.0
*/
class IndexAction extends \yii\rest\IndexAction
{
public $dataFilter = [
'class' => 'yii\data\ActiveDataFilter',
'searchModel' => 'weibo\models\WeiboWeiboConnectWebAppUserSearch',
'attributeMap' => [
'group_id' => '{{%channel_app_source}}.[[group_id]]',
'channel_app_source_uuid' => '{{%channel_app_source}}.[[uuid]]',
'source' => '{{%channel_app_source}}.[]',
'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
'status' => '{{%channel_app_source}}.[[status]]',
'created_at' => '{{%channel_app_source}}.[[created_at]]',
'user_name' => '{{%weibo_weibo_connect_web_app_user}}.[[user_name]]',
'permission' => '{{%channel_app_source}}.[[permission]]',
],
];
/**
* Prepares the data provider that should return the requested collection of the models.
* @return ActiveDataProvider
* @throws InvalidConfigException if the configuration is invalid.
* @throws UnprocessableEntityHttpException
*/
protected function prepareDataProvider()
{
$requestParams = Yii::$app->getRequest()->getBodyParams();
if (empty($requestParams)) {
$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', '224003'), ['first_error' => $firstError])), 224003);
}
}
}
if ($this->prepareDataProvider !== null) {
return call_user_func($this->prepareDataProvider, $this, $filter);
}
/* @var $modelClass WeiboWeiboConnectWebAppUser */
$modelClass = $this->modelClass;
$query = $modelClass::find()
->joinWith(['channelAppSource'])
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->orderBy([ChannelAppSource::tableName() . '.id' => SORT_DESC]);
if (!empty($filter)) {
$query->andFilterWhere($filter);
}
return Yii::createObject([
'class' => ActiveDataProvider::className(),
'query' => $query,
'pagination' => [
'params' => $requestParams,
],
'sort' => [
'params' => $requestParams,
],
]);
}
}
</pre>
<!-- /wp:syntaxhighlighter/code -->
<p>3, gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source]=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 . Response resource list. as shown in Figure 1
Figure 1
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63"
}
},
"_meta": {
"totalCount": 1,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
4. View the SQL statement in the log. as shown in Figure 2
Figure 2
SELECT `cpa_weibo_weibo_connect_web_app_user`.* FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_channel_app_source`.`id` 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') ORDER BY `cpa_channel_app_source`.`id` DESC LIMIT 20
5. Now there is a new requirement, which needs to be sorted in descending order based on the number of articles published by Weibo users. Decided to reuse this interface. Add request parameter: sort=-channel_app_task_count. as shown in Figure 3
Figure 3
6. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status . View SQL statements in the logs. as shown in Figure 4
Figure 4
SELECT `cpa_weibo_weibo_connect_web_app_user`.* FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_channel_app_source`.`id` 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') ORDER BY `cpa_channel_app_source`.`id` DESC, `status` DESC LIMIT 20
7. Edit method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/indexaction.php. Implement the search and sorting of the channel_app_task_count. The inclusion of channel_app_task_count is prerequisite for the channel_app_task field status of 6.
<pre class="wp-block-syntaxhighlighter-code">
* @since 1.0
*/
class IndexAction extends \yii\rest\IndexAction
{
public $dataFilter = [
'class' => 'yii\data\ActiveDataFilter',
'searchModel' => 'weibo\models\WeiboWeiboConnectWebAppUserSearch',
'attributeMap' => [
'group_id' => '{{%channel_app_source}}.[[group_id]]',
'channel_app_source_uuid' => '{{%channel_app_source}}.[[uuid]]',
'source' => '{{%channel_app_source}}.[]',
'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
'status' => '{{%channel_app_source}}.[[status]]',
'created_at' => '{{%channel_app_source}}.[[created_at]]',
'user_name' => '{{%weibo_weibo_connect_web_app_user}}.[[user_name]]',
'permission' => '{{%channel_app_source}}.[[permission]]',
],
];
/**
* Prepares the data provider that should return the requested collection of the models.
* @return ActiveDataProvider
* @throws InvalidConfigException if the configuration is invalid.
* @throws UnprocessableEntityHttpException
*/
protected function prepareDataProvider()
{
$requestParams = Yii::$app->getRequest()->getBodyParams();
if (empty($requestParams)) {
$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', '224003'), ['first_error' => $firstError])), 224003);
}
}
}
if ($this->prepareDataProvider !== null) {
return call_user_func($this->prepareDataProvider, $this, $filter);
}
/* @var $modelClass WeiboWeiboConnectWebAppUser */
$modelClass = $this->modelClass;
$query = $modelClass::find()
->select([
WeiboWeiboConnectWebAppUser::tableName() . '.*',
'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
])
->joinWith(['channelAppSource'])
->joinWith([
'channelAppSource.channelAppTasks' => function ($query) {
$query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
},
], false)
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->groupBy([ChannelAppSource::tableName() . '.id'])
->orderBy(['channel_app_task_count' => SORT_DESC, ChannelAppSource::tableName() . '.id' => SORT_DESC]);
if (!empty($filter)) {
$query->andFilterWhere($filter);
}
return Yii::createObject([
'class' => ActiveDataProvider::className(),
'query' => $query,
'pagination' => [
'params' => $requestParams,
],
'sort' => [
'params' => $requestParams,
],
]);
}
}
</pre>
<!-- /wp:syntaxhighlighter/code -->
<p>8. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source]=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 . Response resource list. View SQL statements in the logs. as shown in Figure 5</p>
<p>[caption id="attachment_4549" align="alignnone" width="1419"]<a href="https://www.shuijingwanwq.com/wp-content/uploads/2020/09/5-2.png"><img class="size-full wp-image-4549" src="https://www.shuijingwanwq.com/wp-content/uploads/2020/09/5-2.png" alt="响应资源列表。查看日志中的 SQL 语句 。" width="1419" height="916"></a> Figure 5[/caption]</p>
<div class="wp-block-syntaxhighlighter-code ">
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"channel_app_task_count": "1",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "7",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": "31",
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": "1",
"is_deleted": "0",
"created_at": "1600331845",
"updated_at": "1600331845",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "6",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": "30",
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": "1",
"is_deleted": "0",
"created_at": "1600328576",
"updated_at": "1600328576",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "5",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": "29",
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": "1",
"is_deleted": "0",
"created_at": "1600328467",
"updated_at": "1600328467",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
</div>
<div class="wp-block-syntaxhighlighter-code ">
SELECT `cpa_weibo_weibo_connect_web_app_user`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_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) 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `cpa_channel_app_source`.`id` DESC, `status` DESC LIMIT 20
</div>
<p>9. Yii uses the yii\data\sort object to represent the relevant information of the sorting scheme. Reference URL:https://www.yiiframework.com/doc/api/2.0/yii-data-sort. Edit method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/indexaction.php. Adjust the SORT configuration.</p>
<div class="wp-block-syntaxhighlighter-code ">
$query = $modelClass::find()
->select([
WeiboWeiboConnectWebAppUser::tableName() . '.*',
'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
])
->joinWith(['channelAppSource'])
->joinWith([
'channelAppSource.channelAppTasks' => function ($query) {
$query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
},
], false)
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->groupBy([ChannelAppSource::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,
],
'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,
],
]);
}
</div>
<p>10. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=Spider&page=1&group_id=015ce30b116ce860 58fa6ab4fea4ac63&sort=-channel_app_task_count . Response resource list. View SQL statements in the logs. in line with expectations. as shown in Figure 6</p>
<p>[caption id="attachment_4550" align="alignnone" width="1420"]<a href="https://www.shuijingwanwq.com/wp-content/uploads/2020/09/6-2.png"><img class="size-full wp-image-4550" src="https://www.shuijingwanwq.com/wp-content/uploads/2020/09/6-2.png" alt="sort=-channel_app_task_count 。响应资源列表。查看日志中的 SQL 语句 。符合预期。" width="1420" height="910"></a> Figure 6[/caption]</p>
<div class="wp-block-syntaxhighlighter-code ">
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"channel_app_task_count": "2",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "6",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": "30",
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": "1",
"is_deleted": "0",
"created_at": "1600328576",
"updated_at": "1600328576",
"deleted_at": "0",
"channel_app_task_count": "1",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "7",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": "31",
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": "1",
"is_deleted": "0",
"created_at": "1600331845",
"updated_at": "1600331845",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "5",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": "29",
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": "1",
"is_deleted": "0",
"created_at": "1600328467",
"updated_at": "1600328467",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
</div>
<div class="wp-block-syntaxhighlighter-code ">
SELECT `cpa_weibo_weibo_connect_web_app_user`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_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) 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 20
</div>
<p>11. The problem at this stage is that when simply obtaining the list of Weibo accounts, there is no need to execute the count() statement based on the number of published articles. This is redundant. However, this problem can be ignored for the time being, and the impact is not large. Edit the serialization file: /weibo/rests/weibo_weibo_connect_web_app_user/serializer.php . Adjust the response field type. to be consistent with the table structure. The final response is as follows.</p>
<div class="wp-block-syntaxhighlighter-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,
];
foreach ($result['items'] as $i => $item) {
$result['items'][$i]['id'] = (int) $item['id'];
$result['items'][$i]['channel_app_source_id'] = (int) $item['channel_app_source_id'];
$result['items'][$i]['weibo_weibo_connect_web_app_id'] = (int) $item['weibo_weibo_connect_web_app_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]['channel_app_task_count'] = (int) $item['channel_app_task_count'];
$result['items'][$i]['source'] = $item['channelAppSource']['source'];
$result['items'][$i]['source_uuid'] = $item['channelAppSource']['source_uuid'];
$result['items'][$i]['permission'] = (int) $item['channelAppSource']['permission'];
/* 销毁属性 */
unset($result['items'][$i]['channelAppSource']);
}
if ($pagination !== false) {
return ['code' => 10000, 'message' => Yii::t('success', '144002'), 'data' => array_merge($result, $this->serializePagination($pagination))];
}
return ['code' => 10000, 'message' => Yii::t('success', '144002'), 'data' => $result];
}
}
</div>
<div class="wp-block-syntaxhighlighter-code ">
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": 4,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": 28,
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": 1,
"is_deleted": 0,
"created_at": 1600259805,
"updated_at": 1600259805,
"deleted_at": 0,
"channel_app_task_count": 2,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 6,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": 30,
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": 1,
"is_deleted": 0,
"created_at": 1600328576,
"updated_at": 1600328576,
"deleted_at": 0,
"channel_app_task_count": 1,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 7,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": 31,
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": 1,
"is_deleted": 0,
"created_at": 1600331845,
"updated_at": 1600331845,
"deleted_at": 0,
"channel_app_task_count": 0,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 5,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": 29,
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": 1,
"is_deleted": 0,
"created_at": 1600328467,
"updated_at": 1600328467,
"deleted_at": 0,
"channel_app_task_count": 0,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
</div>
<p> </p>1. Check the search model file: /common/logics/weiboWeiBoConnectWebAppUsersarch.php.
* @since 1.0
*/
class WeiboWeiboConnectWebAppUserSearch extends Model
{
public $group_id;
public $channel_app_source_uuid;
public $source;
public $source_uuid;
public $user_name;
public $permission;
public $status;
public $created_at;
/**
* @inheritdoc
*/
public function rules()
{
return [
[['status', 'permission', 'created_at'], 'integer'],
[['group_id', 'source', 'user_name'], 'string', 'max' => 32],
[['channel_app_source_uuid', 'source_uuid'], 'string', 'max' => 64],
[['status'], 'in', 'range' => [WeiboWeiboConnectWebAppUser::STATUS_DISABLED, WeiboWeiboConnectWebAppUser::STATUS_ENABLED]],
[['permission'], 'in', 'range' => [ChannelAppSource::PERMISSION_SYNC, ChannelAppSource::PERMISSION_PUB, ChannelAppSource::PERMISSION_SYNC_PUB]],
];
}
}
2. View the method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/indexaction.php.
* @since 1.0
*/
class IndexAction extends \yii\rest\IndexAction
{
public $dataFilter = [
'class' => 'yii\data\ActiveDataFilter',
'searchModel' => 'weibo\models\WeiboWeiboConnectWebAppUserSearch',
'attributeMap' => [
'group_id' => '{{%channel_app_source}}.[[group_id]]',
'channel_app_source_uuid' => '{{%channel_app_source}}.[[uuid]]',
'source' => '{{%channel_app_source}}.[]',
'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
'status' => '{{%channel_app_source}}.[[status]]',
'created_at' => '{{%channel_app_source}}.[[created_at]]',
'user_name' => '{{%weibo_weibo_connect_web_app_user}}.[[user_name]]',
'permission' => '{{%channel_app_source}}.[[permission]]',
],
];
/**
* Prepares the data provider that should return the requested collection of the models.
* @return ActiveDataProvider
* @throws InvalidConfigException if the configuration is invalid.
* @throws UnprocessableEntityHttpException
*/
protected function prepareDataProvider()
{
$requestParams = Yii::$app->getRequest()->getBodyParams();
if (empty($requestParams)) {
$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', '224003'), ['first_error' => $firstError])), 224003);
}
}
}
if ($this->prepareDataProvider !== null) {
return call_user_func($this->prepareDataProvider, $this, $filter);
}
/* @var $modelClass WeiboWeiboConnectWebAppUser */
$modelClass = $this->modelClass;
$query = $modelClass::find()
->joinWith(['channelAppSource'])
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->orderBy([ChannelAppSource::tableName() . '.id' => SORT_DESC]);
if (!empty($filter)) {
$query->andFilterWhere($filter);
}
return Yii::createObject([
'class' => ActiveDataProvider::className(),
'query' => $query,
'pagination' => [
'params' => $requestParams,
],
'sort' => [
'params' => $requestParams,
],
]);
}
}
</pre>
<!-- /wp:syntaxhighlighter/code -->
<p>3, gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source]=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 . Response resource list. as shown in Figure 1Figure 1
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63"
}
},
"_meta": {
"totalCount": 1,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
4. View the SQL statement in the log. as shown in Figure 2
Figure 2
SELECT `cpa_weibo_weibo_connect_web_app_user`.* FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_channel_app_source`.`id` 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') ORDER BY `cpa_channel_app_source`.`id` DESC LIMIT 20
5. Now there is a new requirement, which needs to be sorted in descending order based on the number of articles published by Weibo users. Decided to reuse this interface. Add request parameter: sort=-channel_app_task_count. as shown in Figure 3
Figure 3
6. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status . View SQL statements in the logs. as shown in Figure 4
Figure 4
SELECT `cpa_weibo_weibo_connect_web_app_user`.* FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_channel_app_source`.`id` 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') ORDER BY `cpa_channel_app_source`.`id` DESC, `status` DESC LIMIT 20
7. Edit method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/indexaction.php. Implement the search and sorting of the channel_app_task_count. The inclusion of channel_app_task_count is prerequisite for the channel_app_task field status of 6.
* @since 1.0
*/
class IndexAction extends \yii\rest\IndexAction
{
public $dataFilter = [
'class' => 'yii\data\ActiveDataFilter',
'searchModel' => 'weibo\models\WeiboWeiboConnectWebAppUserSearch',
'attributeMap' => [
'group_id' => '{{%channel_app_source}}.[[group_id]]',
'channel_app_source_uuid' => '{{%channel_app_source}}.[[uuid]]',
'source' => '{{%channel_app_source}}.[]',
'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
'status' => '{{%channel_app_source}}.[[status]]',
'created_at' => '{{%channel_app_source}}.[[created_at]]',
'user_name' => '{{%weibo_weibo_connect_web_app_user}}.[[user_name]]',
'permission' => '{{%channel_app_source}}.[[permission]]',
],
];
/**
* Prepares the data provider that should return the requested collection of the models.
* @return ActiveDataProvider
* @throws InvalidConfigException if the configuration is invalid.
* @throws UnprocessableEntityHttpException
*/
protected function prepareDataProvider()
{
$requestParams = Yii::$app->getRequest()->getBodyParams();
if (empty($requestParams)) {
$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', '224003'), ['first_error' => $firstError])), 224003);
}
}
}
if ($this->prepareDataProvider !== null) {
return call_user_func($this->prepareDataProvider, $this, $filter);
}
/* @var $modelClass WeiboWeiboConnectWebAppUser */
$modelClass = $this->modelClass;
$query = $modelClass::find()
->select([
WeiboWeiboConnectWebAppUser::tableName() . '.*',
'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
])
->joinWith(['channelAppSource'])
->joinWith([
'channelAppSource.channelAppTasks' => function ($query) {
$query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
},
], false)
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->groupBy([ChannelAppSource::tableName() . '.id'])
->orderBy(['channel_app_task_count' => SORT_DESC, ChannelAppSource::tableName() . '.id' => SORT_DESC]);
if (!empty($filter)) {
$query->andFilterWhere($filter);
}
return Yii::createObject([
'class' => ActiveDataProvider::className(),
'query' => $query,
'pagination' => [
'params' => $requestParams,
],
'sort' => [
'params' => $requestParams,
],
]);
}
}
</pre>
<!-- /wp:syntaxhighlighter/code -->
<p>8. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source]=Spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 . Response resource list. View SQL statements in the logs. as shown in Figure 5
Figure 5
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"channel_app_task_count": "1",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "7",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": "31",
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": "1",
"is_deleted": "0",
"created_at": "1600331845",
"updated_at": "1600331845",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "6",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": "30",
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": "1",
"is_deleted": "0",
"created_at": "1600328576",
"updated_at": "1600328576",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "5",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": "29",
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": "1",
"is_deleted": "0",
"created_at": "1600328467",
"updated_at": "1600328467",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
SELECT `cpa_weibo_weibo_connect_web_app_user`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_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) 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `cpa_channel_app_source`.`id` DESC, `status` DESC LIMIT 20
9. Yii uses the yii\data\sort object to represent the relevant information of the sorting scheme. Reference URL:https://www.yiiframework.com/doc/api/2.0/yii-data-sort. Edit method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/indexaction.php. Adjust the SORT configuration.
$query = $modelClass::find()
->select([
WeiboWeiboConnectWebAppUser::tableName() . '.*',
'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
])
->joinWith(['channelAppSource'])
->joinWith([
'channelAppSource.channelAppTasks' => function ($query) {
$query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
},
], false)
->joinWith(['channelAppSource.channel'], false)
->joinWith(['channelAppSource.channelType'], false)
->where([
Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
ChannelAppSource::tableName() . '.is_deleted' => ChannelAppSource::IS_DELETED_NO,
WeiboWeiboConnectWebAppUser::tableName() . '.is_deleted' => WeiboWeiboConnectWebAppUser::IS_DELETED_NO,
])
->asArray()
->groupBy([ChannelAppSource::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,
],
'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,
],
]);
}
10. Gethttp://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=Spider&page=1&group_id=015ce30b116ce860 58fa6ab4fea4ac63&sort=-channel_app_task_count . Response resource list. View SQL statements in the logs. in line with expectations. as shown in Figure 6
Figure 6
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": "4",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": "28",
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": "1",
"is_deleted": "0",
"created_at": "1600259805",
"updated_at": "1600259805",
"deleted_at": "0",
"channel_app_task_count": "2",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "6",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": "30",
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": "1",
"is_deleted": "0",
"created_at": "1600328576",
"updated_at": "1600328576",
"deleted_at": "0",
"channel_app_task_count": "1",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "7",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": "31",
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": "1",
"is_deleted": "0",
"created_at": "1600331845",
"updated_at": "1600331845",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
},
{
"id": "5",
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": "29",
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": "1",
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": "1",
"is_deleted": "0",
"created_at": "1600328467",
"updated_at": "1600328467",
"deleted_at": "0",
"channel_app_task_count": "0",
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": "2"
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
SELECT `cpa_weibo_weibo_connect_web_app_user`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_weibo_weibo_connect_web_app_user` LEFT JOIN `cpa_channel_app_source` ON `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` = `cpa_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) 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` WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0) AND (`cpa_weibo_weibo_connect_web_app_user`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`source`='spider') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 20
11. The problem at this stage is that when simply obtaining the list of Weibo accounts, there is no need to execute the count() statement based on the number of published articles. This is redundant. However, this problem can be ignored for the time being, and the impact is not large. Edit the serialization file: /weibo/rests/weibo_weibo_connect_web_app_user/serializer.php . Adjust the response field type. to be consistent with the table structure. The final response is as follows.
* @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,
];
foreach ($result['items'] as $i => $item) {
$result['items'][$i]['id'] = (int) $item['id'];
$result['items'][$i]['channel_app_source_id'] = (int) $item['channel_app_source_id'];
$result['items'][$i]['weibo_weibo_connect_web_app_id'] = (int) $item['weibo_weibo_connect_web_app_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]['channel_app_task_count'] = (int) $item['channel_app_task_count'];
$result['items'][$i]['source'] = $item['channelAppSource']['source'];
$result['items'][$i]['source_uuid'] = $item['channelAppSource']['source_uuid'];
$result['items'][$i]['permission'] = (int) $item['channelAppSource']['permission'];
/* 销毁属性 */
unset($result['items'][$i]['channelAppSource']);
}
if ($pagination !== false) {
return ['code' => 10000, 'message' => Yii::t('success', '144002'), 'data' => array_merge($result, $this->serializePagination($pagination))];
}
return ['code' => 10000, 'message' => Yii::t('success', '144002'), 'data' => $result];
}
}
{
"code": 10000,
"message": "获取微博的微连接的网页应用的用户列表成功",
"data": {
"items": [
{
"id": 4,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "48558b06f81911ea9c1a54ee75d2ebc1",
"channel_app_source_id": 28,
"channel_app_source_uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "2612590013",
"user_name": "永夜烟",
"avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
"status": 1,
"is_deleted": 0,
"created_at": 1600259805,
"updated_at": 1600259805,
"deleted_at": 0,
"channel_app_task_count": 2,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 6,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "66cd669ef8b911eabf8e54ee75d2ebc1",
"channel_app_source_id": 30,
"channel_app_source_uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "1658397962",
"user_name": "右可在可蜚百里好87fr在章旭飞55",
"avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
"status": 1,
"is_deleted": 0,
"created_at": 1600328576,
"updated_at": 1600328576,
"deleted_at": 0,
"channel_app_task_count": 1,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 7,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "0320c8d6f8c111eaaaba54ee75d2ebc1",
"channel_app_source_id": 31,
"channel_app_source_uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "3762971921",
"user_name": "terryhong123",
"avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
"status": 1,
"is_deleted": 0,
"created_at": 1600331845,
"updated_at": 1600331845,
"deleted_at": 0,
"channel_app_task_count": 0,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
},
{
"id": 5,
"group_id": "015ce30b116ce86058fa6ab4fea4ac63",
"uuid": "25e86d0ef8b911eabdaa54ee75d2ebc1",
"channel_app_source_id": 29,
"channel_app_source_uuid": "25e749a6f8b911ea9ddd54ee75d2ebc1",
"weibo_weibo_connect_web_app_id": 1,
"user_id": "5654576218",
"user_name": "成都索贝视频云计算",
"avatar": "https://tvax1.sinaimg.cn/default/images/default_avatar_male_180.gif?KID=imgbed,tva&Expires=1600339260&ssig=M4tqT%2FNivD",
"status": 1,
"is_deleted": 0,
"created_at": 1600328467,
"updated_at": 1600328467,
"deleted_at": 0,
"channel_app_task_count": 0,
"source": "spider",
"source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
"permission": 2
}
],
"_links": {
"self": {
"href": "http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter%5Bsource%5D=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count"
}
},
"_meta": {
"totalCount": 4,
"pageCount": 1,
"currentPage": 1,
"perPage": 20
}
}
}
