yii2 basemodel query

php · yii2 · 2018年3月8日 · 11871 次浏览

定义此basemodel，其它model继承basemodel

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
<?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的查询条件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
{
  "this": {
    "page": 1,
    "offset": 0,
    "limit": 30,
    "orderby": "cron.id desc"
  },
  "cron": {
    "path": "",
    "param": {
      "like": ""
    },
    "status": "",
    "crontime": {
      "gt": -1
    }
  }
}

 

PHP 查询：

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

 

打赏
暂无评论

请到【后台 - 用户 - 我的个人资料】中填写个人说明。

发表评论