ngds/common/components/ActiveQuery.php

<?php
namespace common\components;
/**
 * Created by PhpStorm.
 * User: leo
 * Date: 2018/3/31
 * Time: 下午5:39
 */
class ActiveQuery extends \yii\db\ActiveQuery {

    /**
     * 原始是否启用从库的配置
     * @var
     */
    public $oriEnableSlaves;

    /**
     * 从哪个数据库查询
     * @var string
     */
    public $fromDb = '';

    /**
     * 查找一条数据
     * @param null $db
     * @return array|null|\yii\db\ActiveRecord
     */
    public function one($db = null)
    {
        $result = parent::one($db);
        $this->_switchSlaves($db);
        return $result;
    }

    /**
     * 查询全部数据
     * @param null $db
     * @return array|\yii\db\ActiveRecord[]
     */
    public function all($db = null)
    {
        $result = parent::all($db);
        $this->_switchSlaves($db);
        return $result;
    }

    /**
     * 批量查询逐条返回
     * @param int $batchSize
     * @param null $db
     * @return \yii\db\BatchQueryResult
     */
    public function each($batchSize = 100, $db = null)
    {
        $result = parent::each($batchSize, $db);
        $this->_switchSlaves($db);
        return $result;
    }

    /**
     * 批量查询，批量返回
     * @param int $batchSize
     * @param null $db
     * @return \yii\db\BatchQueryResult
     */
    public function batch($batchSize = 100, $db = null)
    {
        $result = parent::batch($batchSize, $db);
        $this->_switchSlaves($db);
        return $result;
    }

    /**
     * 增加可以从指定库查表的功能
     * @param null $db
     * @return \yii\db\Command
     * @throws \yii\base\InvalidConfigException
     */
    public function createCommand($db = null)
    {
        /* @var $modelClass ActiveRecord */
        $modelClass = $this->modelClass;
        if ($db === null) {
            if($this->fromDb === ''){
                $db = $modelClass::getDb();
            } else {
                $db = \Yii::$app->get($this->fromDb);
            }
        }
        return parent::createCommand($db);
    }

    /**
     * 获取全部表分区
     * @param null $db
     * @return array
     */
    public function allPart($db = null){
        if ($this->emulateExecution) {
            return [];
        }
        if($db === null){
            $db = $this->modelClass::getDb();
        }
        // 从主库去获取表分区
        $db->enableSlaves = false;
        $tableName = $this->modelClass::tableName();
        $sql = "SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='$tableName'";
        $rows = $db->createCommand($sql)->queryAll();
        // 切换回原始设置
        $db->enableSlaves = $this->oriEnableSlaves;
        return $this->populate($rows);
    }

    /**
     * 从分区表中查出年月所在分区的相关数据(如果有from()放在from()后面使用)
     * @param $yearMonth
     * @param string $format
     * @return $this
     */
    public function yearMonth($yearMonth, $format='YYYYMM'){
//        if(!$this->from){
//            $tableName = $this->modelClass::tableName();
//        } else {
//            $tableName = $this->from[0];
//        }
//        $alias = '';
//        $pregResult = null;
//        if(preg_match('/^\s*(\S+)\s+(AS\s+|as\s+)?(\S+)\s*$/', $tableName, $pregResult)){
//            $tableName = $pregResult[1];
//            $alias = ' '.$pregResult[3];
//        }
//        if(!$yearMonth){
//            $yearMonth = '201701';
//        }
//        $this->from = [$tableName." PARTITION FOR(TO_DATE('$yearMonth'".','."'$format'))$alias"];
        return $this;
    }

    /**
     * 从指定数据库查询
     * @param string $fromDb
     * @return $this
     */
    public function fromDb(string $fromDb){
        $this->fromDb = $fromDb;
        return $this;
    }

    /**
     * 切换回原始的主从设置
     * @param null $db
     */
    private function _switchSlaves($db = null){
        if($db === null){
            $db = $this->modelClass::getDb();
        }
        $db->enableSlaves = $this->oriEnableSlaves;
    }

    /**
     * 要筛选的字段，不用*
     * @param array $append
     * @return $this
     */
    public function selectNoText($append = []){
        $modeClass = $this->modelClass;
        $columns = $modeClass::getTableSchema()->columns;
        $result = array_filter($columns, function($col){
            return in_array($col->dbType, ['text', 'longtext', 'tinytext', 'CLOB']);
        });
        if($result){
            foreach($result as $k=>$v){
                unset($columns[$k]);
            }
        }
        $selects = array_map(function ($value) use($modeClass){
            return  $modeClass::tableName().'.'.$value;
        }, array_keys($columns));
        if($append){
            $selects = array_merge($selects, $append);
        }
        $this->select($selects);
        return $this;
    }

    /**
     * 以ID筛选
     * @param $id
     * @param string $key
     * @return $this
     */
    public function is($id, $key = 'ID'){
        $this->where($key .'=:' . $key, [$key=>$id]);
        return $this;
    }
}