Categories: MySQLMySQL 5.7PHPPHP 7.4SQLWeb应用开发Web框架YiiYii 2Yii2 数据库迁移数据库编程语言

在 Yii 2.0 中，当连接两个表时，在 JOIN 查询的 ON 部分中指定一些额外的条件，基于 ActiveDataFilter 查询过滤器的实现

图1

1、现在的需求如下，需要基于文章发布的时间范围，筛选基于文章发布数量的帐号排行。如图1

图1

2、查看获取帐号列表的方法文件，代码如下

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\channel_app_source;

use Yii;
use api\models\Channel;
use api\models\ChannelType;
use api\models\ChannelAppSource;
use api\models\ChannelAppTask;
use api\services\CmcApiGroupService;
use yii\base\InvalidConfigException;
use yii\data\DataFilter;
use yii\data\ActiveDataProvider;
use yii\helpers\ArrayHelper;
use yii\web\UnprocessableEntityHttpException;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;

/**
 * 获取渠道的应用的来源列表：/channel-app-sources(channel-app-source/index)
 *
 * For more details and usage information on IndexAction, see the [guide channel_app_source 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' => 'api\models\ChannelAppSourceSearch',
        'attributeMap' => [
            'group_id' => '{{%channel_app_source}}.[[group_id]]',
            'uuid' => '{{%channel_app_source}}.[[uuid]]',
            'channel_code' => '{{%channel_app_source}}.[[channel_code]]',
            'channel_type_code' => '{{%channel_app_source}}.[[channel_type_code]]',
            'source' => '{{%channel_app_source}}.[[source]]',
            'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
            'permission' => '{{%channel_app_source}}.[[permission]]',
            'status' => '{{%channel_app_source}}.[[status]]',
            'created_at' => '{{%channel_app_source}}.[[created_at]]',
        ],
    ];

    /**
     * Prepares the data provider that should return the requested collection of the models.
     * @return mixed|object|ActiveDataProvider|DataFilter|null
     * @throws HttpException
     * @throws InvalidConfigException if the configuration is invalid.
     * @throws ServerErrorHttpException
     * @throws UnprocessableEntityHttpException
     */    protected function prepareDataProvider()
    {
        $requestParams = Yii::$app->getRequest()->getQueryParams();

        $filter = null;
        if ($this->dataFilter !== null) {
            $this->dataFilter = Yii::createObject($this->dataFilter);
            if ($this->dataFilter->load($requestParams)) {
                $filter = $this->dataFilter->build();
                if ($filter === false) {
                    $firstError = '';
                    foreach ($this->dataFilter->getFirstErrors() as $message) {
                        $firstError = $message;
                        break;
                    }
                    throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013);
                }
            }
        }

        if ($this->prepareDataProvider !== null) {
            return call_user_func($this->prepareDataProvider, $this, $filter);
        }

        // 获取租户ID列表(二维数组，场景：筛选用户时，添加默认条件：租户ID仅限于当前租户ID|其所管辖的下级租户ID)
        $groupListTwo = CmcApiGroupService::getGroupListTwo(Yii::$app->params['groupId'], 'group_sub_tree', '1');
        $groupIds = ArrayHelper::getColumn($groupListTwo, 'group_id');

        /* @var $modelClass ChannelAppSource */        $modelClass = $this->modelClass;

        $query = $modelClass::find()
            ->select([
                $modelClass::tableName() . '.*',
                'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
            ])
            ->joinWith('channel')
            ->joinWith('channelType')
            ->joinWith('baijiaApp')
            ->joinWith('qqCwApp')
            ->joinWith('qqTpAppPenguin')
            ->joinWith('wxApp')
            ->joinWith('weiboWeiboConnectWebAppUser')
            ->joinWith('customizeApp')
            ->joinWith('neteaseTpAppUser')
            ->joinWith([
                'channelAppTasks' => function ($query) {
                    $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
                },
            ], false)
            ->where([
                Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
                ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
                $modelClass::tableName() . '.is_deleted' => $modelClass::IS_DELETED_NO,
            ])
            ->andWhere(['in', $modelClass::tableName() . '.group_id', $groupIds])
            ->asArray()
            ->groupBy([$modelClass::tableName() . '.id']);
        if (!empty($filter)) {
            $query->andFilterWhere($filter);
        }

        return Yii::createObject([
            'class' => ActiveDataProvider::className(),
            'query' => $query,
            'pagination' => [
                'params' => $requestParams,
            ],
            'sort' => [
                'defaultOrder' => [
                    'id' => SORT_DESC,
                ],
                'attributes' => [
                    'id' => [
                        'asc' => ['id' => SORT_ASC],
                        'desc' => ['id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                    'fans_count' => [
                        'asc' => ['fans_count' => SORT_ASC, 'id' => SORT_ASC],
                        'desc' => ['fans_count' => SORT_DESC, 'id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                    'channel_app_task_count' => [
                        'asc' => ['channel_app_task_count' => SORT_ASC, 'id' => SORT_ASC],
                        'desc' => ['channel_app_task_count' => SORT_DESC, 'id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                ],
                'params' => $requestParams,
            ],
        ]);
    }
}

3、GET http://api.channel-pub-api.localhost/v1/channel-app-sources?group_id=015ce30b116ce86058fa6ab4fea4ac63&sort=-channel_app_task_count。 有时，当连接两个表时，你可能需要在 JOIN 查询的 ON 部分中指定一些额外的条件。这可以通过调用 yii\db\ActiveQuery::onCondition() 方法来完成，如下所示：

    ->joinWith([
        'channelAppTasks' => function ($query) {
            $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED]);
        },
    ], false)

4、SQL 部分：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) 表示关联表 cpa_channel_app_task 时，需要指定其 status 等于 6。最终生成的 SQL 语句如下

SELECT `cpa_channel_app_source`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_channel_app_source` LEFT JOIN `cpa_channel` ON `cpa_channel_app_source`.`channel_id` = `cpa_channel`.`id` LEFT JOIN `cpa_channel_type` ON `cpa_channel_app_source`.`channel_type_id` = `cpa_channel_type`.`id` LEFT JOIN `cpa_qq_cw_app` ON `cpa_channel_app_source`.`id` = `cpa_qq_cw_app`.`channel_app_source_id` LEFT JOIN `cpa_qq_tp_app_penguin` ON `cpa_channel_app_source`.`id` = `cpa_qq_tp_app_penguin`.`channel_app_source_id` LEFT JOIN `cpa_wx_app` ON `cpa_channel_app_source`.`id` = `cpa_wx_app`.`channel_app_source_id` LEFT JOIN `cpa_weibo_weibo_connect_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_customize_app` ON `cpa_channel_app_source`.`id` = `cpa_customize_app`.`channel_app_source_id` LEFT JOIN `cpa_netease_tp_app_user` ON `cpa_channel_app_source`.`id` = `cpa_netease_tp_app_user`.`channel_app_source_id` LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND (`cpa_channel_app_task`.`status`=6) WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`group_id` IN ('015ce30b116ce86058fa6ab4fea4ac63', '4fd58ceba1fbc537b5402302702131eb')) GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 20

5、现在由于需要支持表 cpa_channel_app_task 的记录的创建时间的筛选，因此，需要实现如下的 SQL 查询

LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND ((`cpa_channel_app_task`.`status`=6) AND ((`cpa_channel_app_task`.`created_at` >= '1600334142') AND (`cpa_channel_app_task`.`created_at` <= '1600334142')))

6、编辑获取帐号列表的方法文件，新增加一个过滤器，配置其属性 ‘filterAttributeName’ => ‘channel_app_task_filter’, 。代码如下

<?php
/**
 * @link http://www.yiiframework.com/
 * @copyright Copyright (c) 2008 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */
namespace api\rests\channel_app_source;

use Yii;
use api\models\Channel;
use api\models\ChannelType;
use api\models\ChannelAppSource;
use api\models\ChannelAppTask;
use api\services\CmcApiGroupService;
use yii\base\InvalidConfigException;
use yii\data\DataFilter;
use yii\data\ActiveDataProvider;
use yii\helpers\ArrayHelper;
use yii\web\UnprocessableEntityHttpException;
use yii\web\HttpException;
use yii\web\ServerErrorHttpException;

/**
 * 获取渠道的应用的来源列表：/channel-app-sources(channel-app-source/index)
 *
 * For more details and usage information on IndexAction, see the [guide channel_app_source 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' => 'api\models\ChannelAppSourceSearch',
        'attributeMap' => [
            'group_id' => '{{%channel_app_source}}.[[group_id]]',
            'uuid' => '{{%channel_app_source}}.[[uuid]]',
            'channel_code' => '{{%channel_app_source}}.[[channel_code]]',
            'channel_type_code' => '{{%channel_app_source}}.[[channel_type_code]]',
            'source' => '{{%channel_app_source}}.[[source]]',
            'source_uuid' => '{{%channel_app_source}}.[[source_uuid]]',
            'permission' => '{{%channel_app_source}}.[[permission]]',
            'status' => '{{%channel_app_source}}.[[status]]',
            'created_at' => '{{%channel_app_source}}.[[created_at]]',
        ],
    ];

    public $channelAppTaskDataFilter = [
        'class' => 'yii\data\ActiveDataFilter',
        'filterAttributeName' => 'channel_app_task_filter',
        'searchModel' => 'api\models\ChannelAppSourceSearch',
        'attributeMap' => [
            'created_at' => '{{%channel_app_task}}.[[created_at]]',
        ],
    ];

    /**
     * Prepares the data provider that should return the requested collection of the models.
     * @return mixed|object|ActiveDataProvider|DataFilter|null
     * @throws HttpException
     * @throws InvalidConfigException if the configuration is invalid.
     * @throws ServerErrorHttpException
     * @throws UnprocessableEntityHttpException
     */    protected function prepareDataProvider()
    {
        $requestParams = Yii::$app->getRequest()->getQueryParams();

        $filter = null;
        if ($this->dataFilter !== null) {
            $this->dataFilter = Yii::createObject($this->dataFilter);
            if ($this->dataFilter->load($requestParams)) {
                $filter = $this->dataFilter->build();
                if ($filter === false) {
                    $firstError = '';
                    foreach ($this->dataFilter->getFirstErrors() as $message) {
                        $firstError = $message;
                        break;
                    }
                    throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013);
                }
            }
        }

        $channelAppTaskFilter = null;
        if ($this->channelAppTaskDataFilter !== null) {
            $this->channelAppTaskDataFilter = Yii::createObject($this->channelAppTaskDataFilter);
            if ($this->channelAppTaskDataFilter->load($requestParams)) {
                $channelAppTaskFilter = $this->channelAppTaskDataFilter->build();
                if ($channelAppTaskFilter === false) {
                    $firstError = '';
                    foreach ($this->channelAppTaskDataFilter->getFirstErrors() as $message) {
                        $firstError = $message;
                        break;
                    }
                    throw new UnprocessableEntityHttpException(Yii::t('error', Yii::t('error', Yii::t('error', '226013'), ['first_error' => $firstError])), 226013);
                }
            }
        }

        if ($this->prepareDataProvider !== null) {
            return call_user_func($this->prepareDataProvider, $this, $filter);
        }

        // 获取租户ID列表(二维数组，场景：筛选用户时，添加默认条件：租户ID仅限于当前租户ID|其所管辖的下级租户ID)
        $groupListTwo = CmcApiGroupService::getGroupListTwo(Yii::$app->params['groupId'], 'group_sub_tree', '1');
        $groupIds = ArrayHelper::getColumn($groupListTwo, 'group_id');

        /* @var $modelClass ChannelAppSource */        $modelClass = $this->modelClass;

        Yii::$app->params['channelAppTaskFilter'] = $channelAppTaskFilter;

        $query = $modelClass::find()
            ->select([
                $modelClass::tableName() . '.*',
                'COUNT(' . ChannelAppTask::tableName() . '.id) AS channel_app_task_count'
            ])
            ->joinWith('channel')
            ->joinWith('channelType')
            ->joinWith('baijiaApp')
            ->joinWith('douyinWeiboAppUser')
            ->joinWith('qqCwApp')
            ->joinWith('qqTpAppPenguin')
            ->joinWith('wxApp')
            ->joinWith('weiboWeiboConnectWebAppUser')
            ->joinWith('customizeApp')
            ->joinWith('neteaseTpAppUser')
            ->joinWith([
                'channelAppTasks' => function ($query) {
                    $query->onCondition([ChannelAppTask::tableName() . '.status' => ChannelAppTask::STATUS_PLATFORM_PUBLISHED])->andOnCondition(Yii::$app->params['channelAppTaskFilter']);
                },
            ], false)
            ->where([
                Channel::tableName() . '.is_deleted' => Channel::IS_DELETED_NO,
                ChannelType::tableName() . '.is_deleted' => ChannelType::IS_DELETED_NO,
                $modelClass::tableName() . '.is_deleted' => $modelClass::IS_DELETED_NO,
            ])
            ->andWhere(['in', $modelClass::tableName() . '.group_id', $groupIds])
            ->asArray()
            ->groupBy([$modelClass::tableName() . '.id']);
        if (!empty($filter)) {
            $query->andFilterWhere($filter);
        }

        return Yii::createObject([
            'class' => ActiveDataProvider::className(),
            'query' => $query,
            'pagination' => [
                'params' => $requestParams,
            ],
            'sort' => [
                'defaultOrder' => [
                    'id' => SORT_DESC,
                ],
                'attributes' => [
                    'id' => [
                        'asc' => ['id' => SORT_ASC],
                        'desc' => ['id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                    'fans_count' => [
                        'asc' => ['fans_count' => SORT_ASC, 'id' => SORT_ASC],
                        'desc' => ['fans_count' => SORT_DESC, 'id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                    'channel_app_task_count' => [
                        'asc' => ['channel_app_task_count' => SORT_ASC, 'id' => SORT_ASC],
                        'desc' => ['channel_app_task_count' => SORT_DESC, 'id' => SORT_DESC],
                        'default' => SORT_ASC,
                    ],
                ],
                'params' => $requestParams,
            ],
        ]);
    }
}

7、GET http://api.channel-pub-api.localhost/v1/channel-app-sources?filter[status]=1&per-page=5&channel_app_task_filter[created_at][gte]=1595991435&channel_app_task_filter[created_at][lte]=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count 。响应如下，符合预期，如图2

图2

{
    "code": 10000,
    "message": "获取渠道的应用的来源列表成功",
    "data": {
        "items": [
            {
                "id": 28,
                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                "group_name": "深圳市",
                "uuid": "4851b0eef81911ea8c9954ee75d2ebc1",
                "channel_id": 3,
                "channel_code": "weibo",
                "channel_type_id": 4,
                "channel_type_code": "weibo_weibo_connect_web",
                "name": "永夜烟",
                "avatar": "https://tva3.sinaimg.cn/crop.0.0.180.180.1024/9bb8f5bdjw1e8qgp5bmzyj2050050aa8.jpg?KID=imgbed,tva&Expires=1600270597&ssig=r9d4HXo5f7",
                "fans_count": 0,
                "source": "spider",
                "source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
                "permission": 2,
                "status": 1,
                "is_deleted": 0,
                "created_at": 1600259805,
                "updated_at": 1600259805,
                "deleted_at": 0,
                "channel_app_task_count": 2,
                "channel_name": "微博",
                "channel_type_name": "微博的微连接的网页应用"
            },
            {
                "id": 20,
                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                "group_name": "深圳市",
                "uuid": "362d8170d14711eabb5954ee75d2ebc1",
                "channel_id": 4,
                "channel_code": "baijia",
                "channel_type_id": 5,
                "channel_type_code": "baijia",
                "name": "栖云天下通",
                "avatar": "",
                "fans_count": 0,
                "source": "spider",
                "source_uuid": "dc1ec2188c4d8cd91111aa5055439b1w",
                "permission": 2,
                "status": 1,
                "is_deleted": 0,
                "created_at": 1595991435,
                "updated_at": 1595991435,
                "deleted_at": 0,
                "channel_app_task_count": 2,
                "channel_name": "百家号",
                "channel_type_name": "百家号"
            },
            {
                "id": 32,
                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                "group_name": "深圳市",
                "uuid": "aaa98ad6fed211ea9bc954ee75d2ebc1",
                "channel_id": 1,
                "channel_code": "qq",
                "channel_type_id": 2,
                "channel_type_code": "qq_tp",
                "name": "华栖云秀",
                "avatar": "http://inews.gtimg.com/newsapp_ls/0/4867371999_200200/0",
                "fans_count": 0,
                "source": "spider",
                "source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
                "permission": 3,
                "status": 1,
                "is_deleted": 0,
                "created_at": 1600999133,
                "updated_at": 1600999133,
                "deleted_at": 0,
                "channel_app_task_count": 0,
                "channel_name": "企鹅号",
                "channel_type_name": "企鹅号的第三方服务平台应用"
            },
            {
                "id": 31,
                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                "group_name": "深圳市",
                "uuid": "03111c38f8c111eaa0f154ee75d2ebc1",
                "channel_id": 3,
                "channel_code": "weibo",
                "channel_type_id": 4,
                "channel_type_code": "weibo_weibo_connect_web",
                "name": "terryhong123",
                "avatar": "https://tvax4.sinaimg.cn/crop.0.0.888.888.1024/e04a6511ly8ga5ssss65zj20oo0oomzu.jpg?KID=imgbed,tva&Expires=1600342637&ssig=weSAYT8b83",
                "fans_count": 0,
                "source": "spider",
                "source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
                "permission": 2,
                "status": 1,
                "is_deleted": 0,
                "created_at": 1600331844,
                "updated_at": 1600331844,
                "deleted_at": 0,
                "channel_app_task_count": 0,
                "channel_name": "微博",
                "channel_type_name": "微博的微连接的网页应用"
            },
            {
                "id": 30,
                "group_id": "015ce30b116ce86058fa6ab4fea4ac63",
                "group_name": "深圳市",
                "uuid": "66cc5808f8b911eaa86f54ee75d2ebc1",
                "channel_id": 3,
                "channel_code": "weibo",
                "channel_type_id": 4,
                "channel_type_code": "weibo_weibo_connect_web",
                "name": "右可在可蜚百里好87fr在章旭飞55",
                "avatar": "https://tvax4.sinaimg.cn/crop.210.0.540.540.1024/62d9250aly1fy2syxmiooj20qo0f0wei.jpg?KID=imgbed,tva&Expires=1600339369&ssig=pAQqCgWTnU",
                "fans_count": 0,
                "source": "spider",
                "source_uuid": "825e6d5e36468cc4bf536799ce3565cf",
                "permission": 2,
                "status": 1,
                "is_deleted": 0,
                "created_at": 1600328576,
                "updated_at": 1600328576,
                "deleted_at": 0,
                "channel_app_task_count": 0,
                "channel_name": "微博",
                "channel_type_name": "微博的微连接的网页应用"
            }
        ],
        "_links": {
            "self": {
                "href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=1"
            },
            "next": {
                "href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=2"
            },
            "last": {
                "href": "http://api.channel-pub-api.localhost/v1/channel-app-sources?filter%5Bstatus%5D=1&per-page=5&channel_app_task_filter%5Bcreated_at%5D%5Bgte%5D=1595991435&channel_app_task_filter%5Bcreated_at%5D%5Blte%5D=1600334194&group_id=c10e87f39873512a16727e17f57456a5&sort=-channel_app_task_count&page=2"
            }
        },
        "_meta": {
            "totalCount": 9,
            "pageCount": 2,
            "currentPage": 1,
            "perPage": 5
        }
    }
}

8、最终生成的 SQL 如下，如图3

图3

SELECT `cpa_channel_app_source`.*, COUNT(`cpa_channel_app_task`.id) AS `channel_app_task_count` FROM `cpa_channel_app_source` LEFT JOIN `cpa_channel` ON `cpa_channel_app_source`.`channel_id` = `cpa_channel`.`id` LEFT JOIN `cpa_channel_type` ON `cpa_channel_app_source`.`channel_type_id` = `cpa_channel_type`.`id` LEFT JOIN `cpa_baijia_app` ON `cpa_channel_app_source`.`id` = `cpa_baijia_app`.`channel_app_source_id` LEFT JOIN `cpa_douyin_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_douyin_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_qq_cw_app` ON `cpa_channel_app_source`.`id` = `cpa_qq_cw_app`.`channel_app_source_id` LEFT JOIN `cpa_qq_tp_app_penguin` ON `cpa_channel_app_source`.`id` = `cpa_qq_tp_app_penguin`.`channel_app_source_id` LEFT JOIN `cpa_wx_app` ON `cpa_channel_app_source`.`id` = `cpa_wx_app`.`channel_app_source_id` LEFT JOIN `cpa_weibo_weibo_connect_web_app_user` ON `cpa_channel_app_source`.`id` = `cpa_weibo_weibo_connect_web_app_user`.`channel_app_source_id` LEFT JOIN `cpa_customize_app` ON `cpa_channel_app_source`.`id` = `cpa_customize_app`.`channel_app_source_id` LEFT JOIN `cpa_netease_tp_app_user` ON `cpa_channel_app_source`.`id` = `cpa_netease_tp_app_user`.`channel_app_source_id` LEFT JOIN `cpa_channel_app_task` ON (`cpa_channel_app_source`.`id` = `cpa_channel_app_task`.`channel_app_source_id`) AND ((`cpa_channel_app_task`.`status`=6) AND ((`cpa_channel_app_task`.`created_at` >= '1595991435') AND (`cpa_channel_app_task`.`created_at` <= '1600334194'))) WHERE ((`cpa_channel`.`is_deleted`=0) AND (`cpa_channel_type`.`is_deleted`=0) AND (`cpa_channel_app_source`.`is_deleted`=0)) AND (`cpa_channel_app_source`.`group_id` IN ('c10e87f39873512a16727e17f57456a5', '015ce30b116ce86058fa6ab4fea4ac63', '4fd58ceba1fbc537b5402302702131eb', 'e774bfcf8fc4cfe2ce57ac875a266e94', '9852bb7c32a44709cd748180784fcb81')) AND (`cpa_channel_app_source`.`status`='1') GROUP BY `cpa_channel_app_source`.`id` ORDER BY `channel_app_task_count` DESC, `id` DESC LIMIT 5
永夜

Leave a Comment
Share
Published by
永夜
Tags: ActiveDataFilterandOnConditionattributeMapchannel_app_task_filterdataFilterfilterAttributeNamefunction ($query)JOINJOIN 查询joinWithononConditionQueryYii 2.0Yii::$app->params条件查询过滤器连接额外
4 年 ago

Recent Posts

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

1、在 Google Chro…

2 天 ago

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

1、在 https://tin…

4 天 ago

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

1、参考：在 Laravel …

6 天 ago

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

1、现在导出的 Excel 文…

7 天 ago

在 OneKey 钱包中，发送 Optimism 网络中的 USDT 的流程

1、在 OneKey 钱包中，…

2 周 ago

在 Laravel 9 中，复杂的条件验证的实现，基于嵌套数组中的一系列值，确认嵌套数组外层的值是否验证通过

1、现在请求参数如下，只有当 …

3 周 ago