Categories: PHPPHP 7.4SQLWeb应用开发Web框架YiiYii 2编程语言

在 Yii 2.0 中，基于 ActiveDataFilter 实现的 count() 的别名字段的排序

图6

1、查看搜索模型文件：/common/logics/WeiboWeiboConnectWebAppUserSearch.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、查看方法入口文件：/weibo/rests/weibo_weibo_connect_web_app_user/IndexAction.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}}.[[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,
            ],
        ]);
    }
}

3、GET http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 。响应资源列表。如图1

图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、查看日志中的 SQL 语句 。如图2

图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、现在有一个新的需求，需要基于微博用户的发布文章的数量降序排序。决定复用此接口。添加请求参数：sort=-channel_app_task_count。如图3

图3

6、GET http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-status 。查看日志中的 SQL 语句 。如图4

图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、编辑方法入口文件：/weibo/rests/weibo_weibo_connect_web_app_user/IndexAction.php。实现字段：channel_app_task_count 的搜索与排序。channel_app_task_count 的纳入统计的前提是 channel_app_task 的字段 status 的值为 6。

<?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]]',
            '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,
            ],
        ]);
    }
}

8、GET http://api.channel-pub-api.localhost/weibo/v1/weibo-weibo-connect-web-app-users?filter=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63 。响应资源列表。查看日志中的 SQL 语句 。如图5

图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 使用 yii\data\Sort 对象来代表排序方案的有关信息。参考网址：https://www.yiiframework.com/doc/api/2.0/yii-data-sort 。编辑方法入口文件：/weibo/rests/weibo_weibo_connect_web_app_user/IndexAction.php。调整 sort 配置。

        $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=spider&page=1&group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count 。响应资源列表。查看日志中的 SQL 语句 。符合预期。如图6

图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、现阶段存在的问题是，单纯地仅是获取微博帐号列表时，无需要基于发布文章数量排行时，也会执行 count() 语句。此时是冗余的。不过暂时可以忽略此问题，影响不大。编辑序列化文件：/weibo/rests/weibo_weibo_connect_web_app_user/Serializer.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];
    }
}


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

 

永夜

Leave a Comment
Share
Published by
永夜
Tags: ActiveDataFilterascattributeMapchannel_app_task_countcount()dataFilterdefaultOrderdescfilterORDER BYsearchModelSortSORT_ASCSORT_DESCYii 2.0Yii 2.0.x排序统计降序
4 年 ago

Recent Posts

在 Laravel 9 中 job 运行报错：has been attempted too many times or run too long. The job may have previously timed out.

1、在 Laravel 9 中…

5 天 ago

Laravel 9 中的 Redis 门面 hset 不允许传递多个字段/值

1、尝试基于 Redis 门面…

7 天 ago

在 Google Chrome 中由于网站升级，打开空白，仅在无痕模式中可正常打开的解决

1、在 Google Chro…

2 周 ago

在 https://tinys.pl/cn/swap 上用 SOL 购买得到 NUTS，在 欧易 Web3 钱包 中显示数量为 0 的排查分析

1、在 https://tin…

2 周 ago

在 Laravel 9 中，基于 Spatie\QueryBuilder\QueryBuilder 来生成查询 SQL，同一字段可支持：partial、exact

1、参考：在 Laravel …

2 周 ago

基于 PHP-Xlswriter 导出 Excel 文件时，设置日期列的格式

1、现在导出的 Excel 文…

2 周 ago