1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
namespace Asymptix\db; |
4
|
|
|
|
5
|
|
|
use Asymptix\core\Tools; |
6
|
|
|
|
7
|
|
|
/** |
8
|
|
|
* DB SQL query condition class. |
9
|
|
|
* |
10
|
|
|
* @category Asymptix PHP Framework |
11
|
|
|
* @author Dmytro Zarezenko <[email protected]> |
12
|
|
|
* @copyright (c) 2009 - 2016, Dmytro Zarezenko |
13
|
|
|
* |
14
|
|
|
* @git https://github.com/Asymptix/Framework |
15
|
|
|
* @license http://opensource.org/licenses/MIT |
16
|
|
|
*/ |
17
|
|
|
class DBQueryCondition { |
18
|
|
|
/** |
19
|
|
|
* Database condition field. |
20
|
|
|
* |
21
|
|
|
* @var DBField |
22
|
|
|
*/ |
23
|
|
|
public $field; |
24
|
|
|
|
25
|
|
|
/** |
26
|
|
|
* Type of condition. |
27
|
|
|
* |
28
|
|
|
* @var string |
29
|
|
|
*/ |
30
|
|
|
public $type; |
31
|
|
|
|
32
|
|
|
/** |
33
|
|
|
* Condition value. |
34
|
|
|
* |
35
|
|
|
* @var mixed |
36
|
|
|
*/ |
37
|
|
|
public $value; |
38
|
|
|
|
39
|
|
|
/** |
40
|
|
|
* Result SQL condition string. |
41
|
|
|
* |
42
|
|
|
* @var string |
43
|
|
|
*/ |
44
|
|
|
private $sqlCondition; |
45
|
|
|
|
46
|
|
|
/** |
47
|
|
|
* Result prepare statement SQL condition string. |
48
|
|
|
* |
49
|
|
|
* @var string |
50
|
|
|
*/ |
51
|
|
|
private $preparedCondition = ""; |
52
|
|
|
|
53
|
|
|
/** |
54
|
|
|
* Types string for prepared statement. |
55
|
|
|
* |
56
|
|
|
* @var string |
57
|
|
|
*/ |
58
|
|
|
private $preparedTypes = ""; |
59
|
|
|
|
60
|
|
|
/** |
61
|
|
|
* List with data for prepared statement. |
62
|
|
|
* |
63
|
|
|
* @var array<mixed> |
64
|
|
|
*/ |
65
|
|
|
private $preparedData = []; |
66
|
|
|
|
67
|
|
|
/** |
68
|
|
|
* Inits DBQueryCondition object. |
69
|
|
|
* |
70
|
|
|
* @param DBField $field Field to compare. |
71
|
|
|
* @param string $conditionType Type of the comparison operator or condition. |
72
|
|
|
* @param mixed $value May be other DBField object or value to compare with. |
73
|
|
|
* |
74
|
|
|
* @throws DBQueryConditionException If some parameters invalid. |
75
|
|
|
*/ |
76
|
|
|
public function __construct(DBField $field, $conditionType, $value) { |
77
|
|
|
$this->type = self::sqlConditionType($conditionType); |
78
|
|
|
if (!Tools::isInstanceOf($value, "DBField")) { |
79
|
|
|
$this->field = $field; |
80
|
|
|
|
81
|
|
|
switch ($this->type) { |
82
|
|
|
case ("="): |
83
|
|
|
case ("<"): |
84
|
|
|
case (">"): |
85
|
|
|
case ("!="): |
86
|
|
|
$this->value = DBField::castValue($this->field->type, $value); |
87
|
|
|
|
88
|
|
|
$this->sqlCondition = "`" . $field->name . "` " |
89
|
|
|
. $this->type . " " |
90
|
|
|
. DBField::sqlValue($this->field->type, $value); |
91
|
|
|
|
92
|
|
|
$this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?"; |
93
|
|
|
$this->preparedTypes = $this->field->type; |
94
|
|
|
$this->preparedData = [DBField::sqlValue($this->field->type, $value)]; |
95
|
|
|
break; |
96
|
|
|
case ("LIKE"): |
97
|
|
|
case ("NOT LIKE"): |
98
|
|
|
$this->value = DBField::castValue($this->field->type, $value); |
99
|
|
|
|
100
|
|
|
if ($this->field->type != "s") { |
101
|
|
|
throw new DBQueryConditionException("Field type is not a string"); |
102
|
|
|
} |
103
|
|
|
$this->sqlCondition = "`" . $field->name . "` " |
104
|
|
|
. $this->type . " " |
105
|
|
|
. DBField::sqlValue($this->field->type, $value); |
106
|
|
|
|
107
|
|
|
$this->preparedCondition = "`" . $field->name . "` " . $this->type . " ?"; |
108
|
|
|
$this->preparedTypes = $this->field->type; |
109
|
|
|
$this->preparedData = [DBField::sqlValue($this->field->type, $value)]; |
110
|
|
|
break; |
111
|
|
|
case ("IN"): |
112
|
|
|
case ("NOT IN"): |
113
|
|
|
if (is_array($value) && !empty($value)) { |
114
|
|
|
$dataList = []; |
115
|
|
|
foreach ($value as $dataItem) { |
116
|
|
|
$dataList[] = DBField::sqlValue($this->field->type, $dataItem); |
117
|
|
|
} |
118
|
|
|
$dataList = array_unique($dataList); |
119
|
|
|
$count = count($dataList); |
120
|
|
|
if ($count > 0) { |
121
|
|
|
$qmStr = "?"; |
122
|
|
|
$tStr = $this->field->type; |
123
|
|
|
for ($i = 1; $i < $count; $i ++) { |
124
|
|
|
$qmStr .= ", ?"; |
125
|
|
|
$tStr .= $this->field->type; |
126
|
|
|
} |
127
|
|
|
} else { |
128
|
|
|
$this->sqlCondition = "1"; |
129
|
|
|
|
130
|
|
|
return; |
131
|
|
|
} |
132
|
|
|
|
133
|
|
|
$this->sqlCondition = "`" . $field->name . "` " |
134
|
|
|
. $this->type |
135
|
|
|
. " (" . implode(", ", $dataList) . ")"; |
136
|
|
|
|
137
|
|
|
$this->preparedCondition = "`" . $field->name . "` " . $this->type . " (" . $qmStr . ")"; |
138
|
|
|
$this->preparedTypes = $tStr; |
139
|
|
|
$this->preparedData = $dataList; |
140
|
|
|
} else { |
141
|
|
|
throw new DBQueryConditionException("Invalid data for 'IN'/'NOT IN' condition"); |
142
|
|
|
} |
143
|
|
|
break; |
144
|
|
|
case ("BETWEEN"): |
145
|
|
|
if (is_array($value) && count($value) == 2 && isset($value[0]) && isset($value[1])) { |
146
|
|
|
$from = DBField::sqlValue($this->field->type, $value[0]); |
147
|
|
|
$to = DBField::sqlValue($this->field->type, $value[1]); |
148
|
|
|
$this->sqlCondition = "`" . $field->name . "` BETWEEN " . $from . " AND " . $to; |
149
|
|
|
|
150
|
|
|
$this->preparedCondition = "`" . $field->name . "` BETWEEN ? AND ?"; |
151
|
|
|
$this->preparedTypes = $this->field->type . $this->field->type; |
152
|
|
|
$this->preparedData = [$from, $to]; |
153
|
|
|
} else { |
154
|
|
|
throw new DBQueryConditionException("Invalid data for 'BETWEEN' condition"); |
155
|
|
|
} |
156
|
|
|
break; |
157
|
|
|
} |
158
|
|
|
} else { |
159
|
|
|
$field1 = $field; |
160
|
|
|
$field2 = $value; |
161
|
|
|
|
162
|
|
|
switch ($this->type) { |
163
|
|
|
case ("="): |
164
|
|
|
case ("<"): |
165
|
|
|
case (">"): |
166
|
|
|
case ("!="): |
167
|
|
|
case ("LIKE"): |
168
|
|
|
case ("NOT LIKE"): |
169
|
|
|
$this->sqlCondition = "`" . $field1->name . "` " . $this->type . " `" . $field2->name . "`"; |
170
|
|
|
break; |
171
|
|
|
case ("IN"): |
172
|
|
|
case ("NOT IN"): |
173
|
|
|
// impossible, use array instead of DBField |
174
|
|
|
break; |
175
|
|
|
case ("BETWEEN"): |
176
|
|
|
// impossible, use array instead of DBField |
177
|
|
|
break; |
178
|
|
|
} |
179
|
|
|
} |
180
|
|
|
} |
181
|
|
|
|
182
|
|
|
/** |
183
|
|
|
* Generates SQL formatted condition string. |
184
|
|
|
* |
185
|
|
|
* @param mixed $queryCondition List of DBQueryCondition objects or object |
186
|
|
|
* itself. |
187
|
|
|
* @param string $operator Initial logical OR or AND operator. |
188
|
|
|
* |
189
|
|
|
* @return string SQL query condition string. |
190
|
|
|
*/ |
191
|
|
|
public static function getSQLCondition($queryCondition, $operator = "") { |
192
|
|
|
$operator = strtoupper(trim($operator)); |
193
|
|
|
if ($operator === "OR" || $operator === "AND") { |
194
|
|
|
if (is_array($queryCondition)) { |
195
|
|
|
if ($operator === "AND") { |
196
|
|
|
$cond = " (1"; |
197
|
|
|
} else { |
198
|
|
|
$cond = " (0"; |
199
|
|
|
} |
200
|
|
|
|
201
|
|
|
foreach ($queryCondition as $operation => $conditions) { |
202
|
|
|
$cond .= " " . $operator . self::getSQLCondition($conditions, $operation); |
203
|
|
|
} |
204
|
|
|
|
205
|
|
|
$cond .= ")"; |
206
|
|
|
|
207
|
|
|
return $cond; |
208
|
|
|
} |
209
|
|
|
} else { |
210
|
|
|
if (is_array($queryCondition)) { |
211
|
|
|
foreach ($queryCondition as $operation => $conditions) { |
212
|
|
|
return trim( |
213
|
|
|
str_replace(["(1 AND ", "(0 OR "], "(", |
214
|
|
|
self::getSQLCondition($conditions, $operation) |
215
|
|
|
) |
216
|
|
|
); |
217
|
|
|
} |
218
|
|
|
} elseif (Tools::isInstanceOf($queryCondition, "\Asymptix\db\DBQueryCondition")) { |
219
|
|
|
return (" " . $queryCondition->sqlCondition); |
220
|
|
|
} |
221
|
|
|
|
222
|
|
|
return ""; |
223
|
|
|
} |
224
|
|
|
} |
225
|
|
|
|
226
|
|
|
/** |
227
|
|
|
* Normalize query condition operator (type). |
228
|
|
|
* |
229
|
|
|
* @param string $conditionType Condition type. |
230
|
|
|
* |
231
|
|
|
* @return string Normalized condition type. |
232
|
|
|
* @throws DBQueryConditionException If invalid type provided. |
233
|
|
|
*/ |
234
|
|
|
private static function sqlConditionType($conditionType) { |
235
|
|
|
$conditionType = preg_replace("#[[:blank:]]{2,}#", " ", strtolower(trim($conditionType))); |
236
|
|
|
|
237
|
|
|
$conditionTypes = [ |
238
|
|
|
// Equal operator |
239
|
|
|
'=' => "=", |
240
|
|
|
'eq' => "=", |
241
|
|
|
'equal' => "=", |
242
|
|
|
|
243
|
|
|
// Not equal operator |
244
|
|
|
'!=' => "!=", |
245
|
|
|
'<>' => "!=", |
246
|
|
|
'neq' => "!=", |
247
|
|
|
'not equal' => "!=", |
248
|
|
|
|
249
|
|
|
// Less than operator |
250
|
|
|
'<' => "<", |
251
|
|
|
'lt' => "<", |
252
|
|
|
'less than' => "<", |
253
|
|
|
|
254
|
|
|
// Greater than operator |
255
|
|
|
'>' => ">", |
256
|
|
|
'gt' => ">", |
257
|
|
|
'greater than' => ">", |
258
|
|
|
|
259
|
|
|
// Check whether a value is within a set of values |
260
|
|
|
'in' => "IN", |
261
|
|
|
|
262
|
|
|
// Check whether a value is not within a set of values |
263
|
|
|
'not in' => "NOT IN", |
264
|
|
|
|
265
|
|
|
// Simple pattern matching |
266
|
|
|
'like' => "LIKE", |
267
|
|
|
'match' => "LIKE", |
268
|
|
|
|
269
|
|
|
// Negation of simple pattern matching |
270
|
|
|
'not like' => "NOT LIKE", |
271
|
|
|
'not match' => "NOT LIKE", |
272
|
|
|
|
273
|
|
|
// Check whether a value is within a range of values |
274
|
|
|
'between' => "BETWEEN" |
275
|
|
|
|
276
|
|
|
//TODO: add all conditions from http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html |
277
|
|
|
]; |
278
|
|
|
|
279
|
|
|
if (isset($conditionTypes[$conditionType])) { |
280
|
|
|
return $conditionTypes[$conditionType]; |
281
|
|
|
} |
282
|
|
|
throw new DBQueryConditionException("Invalid SQL condition type '" . $conditionType . "'"); |
283
|
|
|
} |
284
|
|
|
} |
285
|
|
|
|
286
|
|
|
/** |
287
|
|
|
* Service exception class. |
288
|
|
|
*/ |
289
|
|
|
class DBQueryConditionException extends \Exception {} |
|
|
|
|
290
|
|
|
|
Having each class in a dedicated file usually plays nice with PSR autoloaders and is therefore a well established practice. If you use other autoloaders, you might not want to follow this rule.