yii2 basemodel query

定义此basemodel，其它model继承basemodel

<?php
namespace app\models;

use Yii;
use yii\db\ActiveRecord;
use yii\behaviors\TimestampBehavior;

class BaseModel extends ActiveRecord
{
    /**
     * @inheritdoc
     */
    public function behaviors()
    {
        return [
            TimestampBehavior::className(),
        ];
    }

    /**
     * @inheritdoc
     */
    public static function primaryKey()
    {
        return ['id'];
    }

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return strtolower(basename(str_replace('\\','/',self::className())));
    }
    
    /**
     * @inheritdoc
     */
    public function rules()
    {
        $rules = [
            [$this->getTableSchema()->getColumnNames(),'safe'],
        ];
        return $rules;
    }

    /**
     * author vking
     * 处理数据库字段搜索，
     * 根据字段name自动joinwith model
     * @param $params
     */
    function query($params,$isCount=false,$getQuery=false){
        $tbname=self::tableName();
        $query = self::find()->alias(self::tableName());
        foreach($params as $table=>$ques){
            if($table=='groupby'){
                $query->groupBy($ques);
                unset($params[$table]);
                continue;
            }
            if($table=='select'){
                $query->addSelect("$tbname.*");
                $query->addSelect($ques);
                unset($params[$table]);
                continue;
            }
            if($table=='with'){
                foreach ($ques as $_wt=>$_wq){
                    $query->with([
                        $_wt=>function($q) use ($_wq){
                            $q->andWhere($_wq);
                        }
                    ]);
                }
                unset($params[$table]);
                continue;
            }
            if($table=='join'){
                if($ques['table']){
                    $ques=[$ques];
                }
                foreach ($ques as $_que){
                    $query->join($_que['type'],$_que['table'],$_que['on']);
                }
                unset($params[$table]);
                continue;
            }
            if(is_string($ques)){
                if(empty($params[$tbname])){
                    $params[$tbname]=[];
                }
                $params[$tbname][$table]=$ques;
                unset($params[$table]);
            }
        }
        foreach($params as $table=>$ques){
            if($table=='this') continue;

            if($table!=$tbname){
                $query->joinWith($table);

            }
            foreach($ques as $field=>$val){
                $field = trim($field);
                $val = is_string($val)?trim($val):$val;
                if(substr($field,0,2)=='__'){
                    continue;
                }
                if(isset($val) && $val!=""){
                    /**
                     * [between]=>[b1,b2]
                     * [in]=>[1,2,3]
                     * [like]=>str
                     * [...]=>str
                     */
                    if(!is_array($val)){
                        $query->andWhere([
                            $table.'.'.$field=>$val
                        ]);
                    }else{
                        foreach ($val as $exp=>$_val){
                            $_val = is_string($_val)?trim($_val):$_val;
                            if(!(isset($_val) && $_val!="")){
                                continue;
                            }
                            if($exp=='between'){
                                $query->andWhere([
                                    $exp,$table.'.'.$field,$_val[0],$_val[1]
                                ]);
                            }else{
                                $filterControls = [
                                    'lt' => '<',
                                    'gt' => '>',
                                    'lte' => '<=',
                                    'gte' => '>=',
                                    'eq' => '=',
                                    'neq' => '!=',
                                    'nin' => 'NOT IN',
                                ];
                                $exp= $filterControls[$exp]?$filterControls[$exp]:$exp;
                                $query->andWhere([
                                    $exp,$table.'.'.$field,$_val
                                ]);
                            }
                        }
                    }
                }
            }
        }
        //$query->groupBy($tbname.'.'.self::primaryKey()[0]);
        if($getQuery) return $query;
        $total=0;
        if($isCount){
            $total = intval($query->count());
        }
        $query->offset($params['this']['offset'])->limit($params['this']['limit']?$params['this']['limit']:'20');
        $sort=$params['this']['orderby']?$params['this']['orderby']:$params['this']['sort'];
        if($sort){
            $query->orderBy($sort);
        }
        $data=$query->asArray()->all();
        return ['list'=>$data,'total'=>$total];
    }
}

使用方式

JS POST 参数：

参数说明 this 是分页相关，其它为具体model，下面是从cron这个model的查询条件

{
  "this": {
    "page": 1,
    "offset": 0,
    "limit": 30,
    "orderby": "cron.id desc"
  },
  "cron": {
    "path": "",
    "param": {
      "like": ""
    },
    "status": "",
    "crontime": {
      "gt": -1
    }
  }
}

PHP 查询：

function getCrons(){
        $searchModel = new Cron();
        $params=\Yii::$app->request->getBodyParams();
        $data = $searchModel->query($params,true);
        return $data;
    }