【唐诗】《春望》 杜甫：國破山河在，城春草木深。感時花濺淚，恨別鳥驚心。烽火連三月，家書抵萬金。白頭搔更短，渾欲不勝簪。

永夜

没有不值得去解决的问题，也没有不值得去学习的技术！

Sorting by Alias Fields in count() With ActiveDataFilter in Yii 2.0

sort=-channel_app_task_count . The resource list is returned. View the SQL statement in the logs. It meets expectations.

作者：

永夜

在

, , , , , , ,
Views: 58

1. View the search model file: /common/logics/WeiboWeiboConnectWebAppUserSearch.php.

PHP
<?php

namespace common\logics;

use Yii;
use yii\base\Model;

/**
 * WeiboWeiboConnectWebAppUserSearch represents the model behind the search form about `common\logics\WeiboWeiboConnectWebAppUser`.
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @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.

PHP
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

namespace weibo\rests\weibo_weibo_connect_web_app_user;

use Yii;
use weibo\models\Channel;
use weibo\models\ChannelType;
use weibo\models\ChannelAppSource;
use weibo\models\WeiboWeiboConnectWebAppUser;
use yii\data\ActiveDataProvider;
use yii\base\InvalidConfigException;
use yii\web\UnprocessableEntityHttpException;

/**
 * 获取微博的微连接的网页应用的用户列表：/weibo-weibo-connect-web-app-users(weibo-weibo-connect-web-app-user/index)
 *
 * For more details and usage information on IndexAction, see the [guide article 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' => 'weibo\models\WeiboWeiboConnectWebAppUserSearch',
        'attributeMap' => [
            'group_id' => '{{%channel_app_source}}.[[group_id]]',
            'channel_app_source_uuid' => '{{%channel_app_source}}.[[uuid]]',
            'source' => '{{%channel_app_source}}.[<pre class="brush: plain; title: ; notranslate" title="">]',
            '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,
            ],
        ]);
    }
}
JSON

{
    "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 logs. See Figure 2.

View the SQL statement in the logs.
Figure 2
SQL

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 to sort in descending order based on the number of published articles by Weibo users. I decided to reuse this interface. Add the request parameter: sort=-channel_app_task_count. See Figure 3.

Now there is a new requirement to sort in descending order based on the number of published articles by Weibo users. I decided to reuse this interface. Add the request parameter: sort=-channel_app_task_count.
Figure 3

6. GET http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source][/source]=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status . View the SQL statement in the logs. See Figure 4.

View the SQL statement in the logs.
Figure 4
SQL

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 the method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/IndexAction.php. Implement the search and sorting for the field: channel_app_task_count. The prerequisite for including channel_app_task_count in the statistics is that the value of the status field in channel_app_task is 6.

PHP
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
 
namespace weibo\rests\weibo_weibo_connect_web_app_user;
 
use Yii;
use weibo\models\Channel;
use weibo\models\ChannelType;
use weibo\models\ChannelAppSource;
use weibo\models\WeiboWeiboConnectWebAppUser;
use weibo\models\ChannelAppTask;
use yii\data\ActiveDataProvider;
use yii\base\InvalidConfigException;
use yii\web\UnprocessableEntityHttpException;
 
/**
 * 获取微博的微连接的网页应用的用户列表：/weibo-weibo-connect-web-app-users(weibo-weibo-connect-web-app-user/index)
 *
 * For more details and usage information on IndexAction, see the [guide article 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' => '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,
            ],
        ]);
    }
}
JSON

{
    "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
        }
    }
}
SQL

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 information related to the sorting scheme. Reference URL: https://www.yiiframework.com/doc/api/2.0/yii-data-sort . Edit the method entry file: /weibo/rests/weibo_weibo_connect_web_app_user/IndexAction.php. Adjust the sort configuration.

PHP

        $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. GET http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter[source][/source]=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count . The resource list is returned. View the SQL statement in the logs. It meets expectations. See Figure 6.

sort=-channel_app_task_count . The resource list is returned. View the SQL statement in the logs. It meets expectations.
Figure 6
JSON

{
    "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
        }
    }
}
SQL

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 current issue is that even when simply fetching the Weibo account list without needing to sort by the number of published articles, the count() statement is still executed. This is redundant. However, this problem can be ignored for now as the impact is minimal. Edit the serialization file: /weibo/rests/weibo_weibo_connect_web_app_user/Serializer.php . Adjust the response field types to keep them consistent with the table structure. The final response is as follows.

PHP
<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
 
namespace weibo\rests\weibo_weibo_connect_web_app_user;
 
use Yii;
use yii\data\DataProviderInterface;
 
/**
 * Serializer converts resource objects and collections into array representation.
 *
 * Serializer is mainly used by REST controllers to convert different objects into array representation
 * so that they can be further turned into different formats, such as JSON, XML, by response formatters.
 *
 * The default implementation handles resources as [[Model]] objects and collections as objects
 * implementing [[DataProviderInterface]]. You may override [[serialize()]] to handle more types.
 *
 * @author Qiang Wang <shuijingwanwq@163.com>
 * @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];
    }
}
JSON

{
    "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
        }
    }
}

关于作者 · 持续学习与技术实践

我是一名拥有 15 年以上开发经验的技术从业者，自 2013 年起持续运营个人技术博客，记录工作与个人项目中遇到的真实问题，以及解决问题过程中的探索、实践和思考。

除了技术写作，我也在持续开发和维护自己的独立项目，探索新技术和新工具在实际场景中的应用。我始终相信，没有不值得去解决的问题，也没有不值得去学习的技术。

如果这篇文章对你有所帮助，欢迎浏览博客中的其他文章，也欢迎交流技术经验与想法。

关于我  ·  GitHub  ·  邮件联系

更多文章