|
1
|
|
|
<?php |
|
2
|
|
|
|
|
3
|
|
|
namespace leandrogehlen\querybuilder; |
|
4
|
|
|
|
|
5
|
|
|
use yii\base\Object; |
|
6
|
|
|
use yii\helpers\ArrayHelper; |
|
7
|
|
|
|
|
8
|
|
|
/** |
|
9
|
|
|
* Translator is used to build WHERE clauses from rules configuration |
|
10
|
|
|
* |
|
11
|
|
|
* The typical usage of Translator is as follows, |
|
12
|
|
|
* |
|
13
|
|
|
* ```php |
|
14
|
|
|
* public function actionIndex() |
|
15
|
|
|
* { |
|
16
|
|
|
* $query = Customer::find(); |
|
17
|
|
|
* $rules = Yii::$app->request->post('rules'); |
|
18
|
|
|
* |
|
19
|
|
|
* if ($rules) { |
|
20
|
|
|
* $translator = new Translator(Json::decode($rules),['currentParams'=>$query->params]); |
|
21
|
|
|
* $query->andWhere($translator->where()) |
|
22
|
|
|
* ->addParams($translator->params()); |
|
23
|
|
|
* } |
|
24
|
|
|
* |
|
25
|
|
|
* $dataProvider = new ActiveDataProvider([ |
|
26
|
|
|
* 'query' => $query, |
|
27
|
|
|
* ]); |
|
28
|
|
|
* |
|
29
|
|
|
* return $this->render('index', [ |
|
30
|
|
|
* 'dataProvider' => $dataProvider, |
|
31
|
|
|
* ]); |
|
32
|
|
|
* } |
|
33
|
|
|
* ``` |
|
34
|
|
|
* @author Leandro Gehlen <[email protected]> |
|
35
|
|
|
*/ |
|
36
|
|
|
class Translator extends Object |
|
37
|
|
|
{ |
|
38
|
|
|
private $_where; |
|
39
|
|
|
private $_params = []; |
|
40
|
|
|
private $_operators; |
|
41
|
|
|
/** |
|
42
|
|
|
* @var array The params from yii\db\Query object that are already set so we don't overwrite them |
|
43
|
|
|
*/ |
|
44
|
|
|
private $currentParams = []; |
|
45
|
|
|
private $paramsCount = 0; |
|
46
|
|
|
|
|
47
|
|
|
/** |
|
48
|
|
|
* Constructors. |
|
49
|
|
|
* @param array $data Rules configuraion |
|
50
|
|
|
* @param array $config the configuration array to be applied to this object. |
|
51
|
|
|
*/ |
|
52
|
1 |
|
public function __construct($data, $config = []) |
|
53
|
|
|
{ |
|
54
|
1 |
|
if(isset($config['currentParams'])){ |
|
55
|
|
|
$this->setCurrentParams($config['currentParams']); |
|
56
|
|
|
|
|
57
|
|
|
} |
|
58
|
1 |
|
parent::__construct($config); |
|
59
|
1 |
|
$this->_where = $this->buildWhere($data); |
|
60
|
1 |
|
} |
|
61
|
|
|
|
|
62
|
|
|
/** |
|
63
|
|
|
* @inheritdoc |
|
64
|
|
|
*/ |
|
65
|
1 |
|
public function init() |
|
66
|
|
|
{ |
|
67
|
1 |
|
$this->_operators = [ |
|
68
|
1 |
|
'equal' => '= ?', |
|
69
|
1 |
|
'not_equal' => '<> ?', |
|
70
|
1 |
|
'in' => ['op' => 'IN (?)', 'list' => true, 'sep' => ', ' ], |
|
71
|
1 |
|
'not_in' => ['op' => 'NOT IN (?)', 'list' => true, 'sep' => ', '], |
|
72
|
1 |
|
'less' => '< ?', |
|
73
|
1 |
|
'less_or_equal' => '<= ?', |
|
74
|
1 |
|
'greater' => '> ?', |
|
75
|
1 |
|
'greater_or_equal' => '>= ?', |
|
76
|
1 |
|
'between' => ['op' => 'BETWEEN ?', 'list' => true, 'sep' => ' AND '], |
|
77
|
1 |
|
'begins_with' => ['op' => 'LIKE ?', 'fn' => function($value){ return "$value%"; } ], |
|
78
|
1 |
|
'not_begins_with' => ['op' => 'NOT LIKE ?', 'fn' => function($value){ return "$value%"; } ], |
|
79
|
1 |
|
'contains' => ['op' => 'LIKE ?', 'fn' => function($value){ return "%$value%"; } ], |
|
80
|
1 |
|
'not_contains' => ['op' => 'NOT LIKE ?', 'fn' => function($value){ return "%$value%"; } ], |
|
81
|
1 |
|
'ends_with' => ['op' => 'LIKE ?', 'fn' => function($value){ return "%$value"; } ], |
|
82
|
1 |
|
'not_ends_with' => ['op' => 'NOT LIKE ?', 'fn' => function($value){ return "%$value"; } ], |
|
83
|
1 |
|
'is_empty' => '= ""', |
|
84
|
1 |
|
'is_not_empty' => '<> ""', |
|
85
|
1 |
|
'is_null' => 'IS NULL', |
|
86
|
|
|
'is_not_null' => 'IS NOT NULL' |
|
87
|
1 |
|
]; |
|
88
|
1 |
|
} |
|
89
|
|
|
|
|
90
|
|
|
|
|
91
|
|
|
/** |
|
92
|
|
|
* Encodes filter rule into SQL condition |
|
93
|
|
|
* @param string $field field name |
|
94
|
|
|
* @param string|array $type operator type |
|
95
|
|
|
* @param string|array $params query parameters |
|
96
|
|
|
* @return string encoded rule |
|
97
|
|
|
*/ |
|
98
|
1 |
|
protected function encodeRule($field, $type, $params) |
|
99
|
|
|
{ |
|
100
|
1 |
|
$pattern = $this->_operators[$type]; |
|
101
|
1 |
|
$keys = array_keys($params); |
|
102
|
|
|
|
|
103
|
1 |
|
if (is_string($pattern)) { |
|
104
|
1 |
|
$replacement = !empty($keys) ? $keys[0] : null; |
|
105
|
1 |
|
} else { |
|
106
|
|
|
$op = ArrayHelper::getValue($pattern, 'op'); |
|
107
|
|
|
$list = ArrayHelper::getValue($pattern, 'list'); |
|
108
|
|
|
if ($list){ |
|
109
|
|
|
$sep = ArrayHelper::getValue($pattern, 'sep'); |
|
110
|
|
|
$replacement = implode($sep, $keys); |
|
111
|
|
|
} else { |
|
112
|
|
|
$fn = ArrayHelper::getValue($pattern, 'fn'); |
|
113
|
|
|
$replacement = key($params); |
|
114
|
|
|
$params[$replacement] = call_user_func($fn, $params[$replacement]); |
|
115
|
|
|
} |
|
116
|
|
|
$pattern = $op; |
|
117
|
|
|
} |
|
118
|
|
|
|
|
119
|
1 |
|
$this->_params = array_merge($this->_params, $params); |
|
120
|
1 |
|
return $field . " " . ($replacement ? str_replace("?", $replacement, $pattern) : $pattern); |
|
121
|
|
|
} |
|
122
|
|
|
|
|
123
|
|
|
/** |
|
124
|
|
|
* @param array $data rules configuration |
|
125
|
|
|
* @return string the WHERE clause |
|
126
|
|
|
*/ |
|
127
|
1 |
|
protected function buildWhere($data) |
|
128
|
|
|
{ |
|
129
|
1 |
|
if (!isset($data['rules']) || !$data['rules']) { |
|
130
|
|
|
return ''; |
|
131
|
|
|
} |
|
132
|
|
|
|
|
133
|
1 |
|
$where = []; |
|
134
|
1 |
|
$condition = " " . $data['condition'] . " "; |
|
135
|
|
|
|
|
136
|
1 |
|
foreach ($data['rules'] as $rule) { |
|
137
|
1 |
|
if (isset($rule['condition'])) { |
|
138
|
|
|
$where[] = $this->buildWhere($rule); |
|
139
|
|
|
} else { |
|
140
|
1 |
|
$params = []; |
|
141
|
1 |
|
$operator = $rule['operator']; |
|
142
|
1 |
|
$field = $rule['field']; |
|
143
|
1 |
|
$value = ArrayHelper::getValue($rule, 'value'); |
|
144
|
|
|
|
|
145
|
1 |
|
if ($value !== null) { |
|
146
|
|
|
|
|
147
|
1 |
|
if (!is_array($value)) { |
|
148
|
1 |
|
$value = [$value]; |
|
149
|
1 |
|
} |
|
150
|
|
|
|
|
151
|
1 |
|
foreach ($value as $v) { |
|
152
|
1 |
|
$params[":".$this->getNewParamName()] = $v; |
|
153
|
1 |
|
} |
|
154
|
1 |
|
} |
|
155
|
1 |
|
$where[] = $this->encodeRule($field, $operator, $params); |
|
156
|
|
|
} |
|
157
|
1 |
|
} |
|
158
|
1 |
|
return "(" . implode($condition, $where) . ")"; |
|
159
|
|
|
} |
|
160
|
|
|
|
|
161
|
|
|
/** |
|
162
|
|
|
* Returns query WHERE condition. |
|
163
|
|
|
* @return string |
|
164
|
|
|
*/ |
|
165
|
1 |
|
public function where() |
|
166
|
|
|
{ |
|
167
|
1 |
|
return $this->_where; |
|
168
|
|
|
} |
|
169
|
|
|
|
|
170
|
|
|
/** |
|
171
|
|
|
* Returns the parameters to be bound to the query. |
|
172
|
|
|
* @return array |
|
173
|
|
|
*/ |
|
174
|
|
|
public function params() |
|
175
|
|
|
{ |
|
176
|
|
|
return array_merge($this->currentParams, $this->_params); |
|
177
|
|
|
} |
|
178
|
|
|
|
|
179
|
|
|
/** |
|
180
|
|
|
* Get a param name that should not conflict with any params already set |
|
181
|
|
|
* @return string |
|
182
|
|
|
*/ |
|
183
|
1 |
|
private function getNewParamName(){ |
|
184
|
1 |
|
$paramPrefix = 'p'; |
|
185
|
1 |
|
if(!empty($this->currentParams) && $this->paramsCount < count($this->currentParams) ){ |
|
186
|
|
|
$this->paramsCount = count($this->currentParams) +1; |
|
187
|
|
|
}else{ |
|
188
|
1 |
|
$this->paramsCount = $this->paramsCount + 1; |
|
189
|
|
|
} |
|
190
|
1 |
|
return $paramPrefix.$this->paramsCount; |
|
191
|
|
|
} |
|
192
|
|
|
|
|
193
|
|
|
/** |
|
194
|
|
|
* |
|
195
|
|
|
* @param array $currentParams |
|
196
|
|
|
* @return \leandrogehlen\querybuilder\Translator |
|
197
|
|
|
*/ |
|
198
|
|
|
public function setCurrentParams($currentParams) { |
|
199
|
|
|
$this->currentParams = $currentParams; |
|
200
|
|
|
} |
|
201
|
|
|
|
|
202
|
|
|
|
|
203
|
|
|
} |
|
204
|
|
|
|
leandrogehlen /
yii2-querybuilder
