1
|
|
|
<?php |
2
|
|
|
|
3
|
|
|
/** |
4
|
|
|
* @link https://www.yiiframework.com/ |
5
|
|
|
* @copyright Copyright (c) 2008 Yii Software LLC |
6
|
|
|
* @license https://www.yiiframework.com/license/ |
7
|
|
|
*/ |
8
|
|
|
|
9
|
|
|
namespace yii\db\conditions; |
10
|
|
|
|
11
|
|
|
use yii\db\Expression; |
12
|
|
|
use yii\db\ExpressionBuilderInterface; |
13
|
|
|
use yii\db\ExpressionBuilderTrait; |
14
|
|
|
use yii\db\ExpressionInterface; |
15
|
|
|
use yii\db\Query; |
16
|
|
|
|
17
|
|
|
/** |
18
|
|
|
* Class InConditionBuilder builds objects of [[InCondition]] |
19
|
|
|
* |
20
|
|
|
* @author Dmytro Naumenko <[email protected]> |
21
|
|
|
* @since 2.0.14 |
22
|
|
|
*/ |
23
|
|
|
class InConditionBuilder implements ExpressionBuilderInterface |
24
|
|
|
{ |
25
|
|
|
use ExpressionBuilderTrait; |
26
|
|
|
|
27
|
|
|
|
28
|
|
|
/** |
29
|
|
|
* Method builds the raw SQL from the $expression that will not be additionally |
30
|
|
|
* escaped or quoted. |
31
|
|
|
* |
32
|
|
|
* @param ExpressionInterface|InCondition $expression the expression to be built. |
33
|
|
|
* @param array $params the binding parameters. |
34
|
|
|
* @return string the raw SQL that will not be additionally escaped or quoted. |
35
|
|
|
*/ |
36
|
380 |
|
public function build(ExpressionInterface $expression, array &$params = []) |
37
|
|
|
{ |
38
|
380 |
|
$operator = strtoupper($expression->getOperator()); |
|
|
|
|
39
|
380 |
|
$column = $expression->getColumn(); |
|
|
|
|
40
|
380 |
|
$values = $expression->getValues(); |
|
|
|
|
41
|
|
|
|
42
|
380 |
|
if ($column === []) { |
43
|
|
|
// no columns to test against |
44
|
|
|
return $operator === 'IN' ? '0=1' : ''; |
45
|
|
|
} |
46
|
|
|
|
47
|
380 |
|
if ($values instanceof Query) { |
48
|
14 |
|
return $this->buildSubqueryInCondition($operator, $column, $values, $params); |
49
|
|
|
} |
50
|
|
|
|
51
|
366 |
|
if (!is_array($values) && !$values instanceof \Traversable) { |
52
|
|
|
// ensure values is an array |
53
|
12 |
|
$values = (array) $values; |
54
|
|
|
} |
55
|
|
|
|
56
|
366 |
|
if (is_array($column)) { |
57
|
241 |
|
if (count($column) > 1) { |
58
|
21 |
|
return $this->buildCompositeInCondition($operator, $column, $values, $params); |
|
|
|
|
59
|
|
|
} |
60
|
223 |
|
$column = reset($column); |
61
|
|
|
} |
62
|
|
|
|
63
|
348 |
|
if ($column instanceof \Traversable) { |
64
|
6 |
|
if (iterator_count($column) > 1) { |
65
|
3 |
|
return $this->buildCompositeInCondition($operator, $column, $values, $params); |
66
|
|
|
} |
67
|
3 |
|
$column->rewind(); |
|
|
|
|
68
|
3 |
|
$column = $column->current(); |
|
|
|
|
69
|
|
|
} |
70
|
|
|
|
71
|
345 |
|
if ($column instanceof Expression) { |
72
|
6 |
|
$column = $column->expression; |
73
|
|
|
} |
74
|
|
|
|
75
|
345 |
|
if (is_array($values)) { |
76
|
312 |
|
$rawValues = $values; |
77
|
33 |
|
} elseif ($values instanceof \Traversable) { |
|
|
|
|
78
|
33 |
|
$rawValues = $this->getRawValuesFromTraversableObject($values); |
79
|
|
|
} |
80
|
|
|
|
81
|
345 |
|
$nullCondition = null; |
82
|
345 |
|
$nullConditionOperator = null; |
83
|
345 |
|
if (isset($rawValues) && in_array(null, $rawValues, true)) { |
84
|
21 |
|
$nullCondition = $this->getNullCondition($operator, $column); |
85
|
21 |
|
$nullConditionOperator = $operator === 'IN' ? 'OR' : 'AND'; |
86
|
|
|
} |
87
|
|
|
|
88
|
345 |
|
$sqlValues = $this->buildValues($expression, $values, $params); |
|
|
|
|
89
|
345 |
|
if (empty($sqlValues)) { |
90
|
42 |
|
if ($nullCondition === null) { |
91
|
33 |
|
return $operator === 'IN' ? '0=1' : ''; |
92
|
|
|
} |
93
|
9 |
|
return $nullCondition; |
94
|
|
|
} |
95
|
|
|
|
96
|
333 |
|
if (strpos($column, '(') === false) { |
97
|
333 |
|
$column = $this->queryBuilder->db->quoteColumnName($column); |
98
|
|
|
} |
99
|
333 |
|
if (count($sqlValues) > 1) { |
100
|
209 |
|
$sql = "$column $operator (" . implode(', ', $sqlValues) . ')'; |
101
|
|
|
} else { |
102
|
224 |
|
$operator = $operator === 'IN' ? '=' : '<>'; |
103
|
224 |
|
$sql = $column . $operator . reset($sqlValues); |
104
|
|
|
} |
105
|
|
|
|
106
|
333 |
|
return $nullCondition !== null && $nullConditionOperator !== null |
107
|
12 |
|
? sprintf('%s %s %s', $sql, $nullConditionOperator, $nullCondition) |
108
|
333 |
|
: $sql; |
109
|
|
|
} |
110
|
|
|
|
111
|
|
|
/** |
112
|
|
|
* Builds $values to be used in [[InCondition]] |
113
|
|
|
* |
114
|
|
|
* @param ConditionInterface|InCondition $condition |
115
|
|
|
* @param array $values |
116
|
|
|
* @param array $params the binding parameters |
117
|
|
|
* @return array of prepared for SQL placeholders |
118
|
|
|
*/ |
119
|
345 |
|
protected function buildValues(ConditionInterface $condition, $values, &$params) |
120
|
|
|
{ |
121
|
345 |
|
$sqlValues = []; |
122
|
345 |
|
$column = $condition->getColumn(); |
|
|
|
|
123
|
|
|
|
124
|
345 |
|
if (is_array($column)) { |
125
|
223 |
|
$column = reset($column); |
126
|
|
|
} |
127
|
|
|
|
128
|
345 |
|
if ($column instanceof \Traversable) { |
129
|
3 |
|
$column->rewind(); |
130
|
3 |
|
$column = $column->current(); |
131
|
|
|
} |
132
|
|
|
|
133
|
345 |
|
if ($column instanceof Expression) { |
134
|
6 |
|
$column = $column->expression; |
135
|
|
|
} |
136
|
|
|
|
137
|
345 |
|
foreach ($values as $i => $value) { |
138
|
342 |
|
if (is_array($value) || $value instanceof \ArrayAccess) { |
139
|
6 |
|
$value = isset($value[$column]) ? $value[$column] : null; |
140
|
|
|
} |
141
|
342 |
|
if ($value === null) { |
142
|
21 |
|
continue; |
143
|
333 |
|
} elseif ($value instanceof ExpressionInterface) { |
144
|
3 |
|
$sqlValues[$i] = $this->queryBuilder->buildExpression($value, $params); |
145
|
|
|
} else { |
146
|
333 |
|
$sqlValues[$i] = $this->queryBuilder->bindParam($value, $params); |
147
|
|
|
} |
148
|
|
|
} |
149
|
|
|
|
150
|
345 |
|
return $sqlValues; |
151
|
|
|
} |
152
|
|
|
|
153
|
|
|
/** |
154
|
|
|
* Builds SQL for IN condition. |
155
|
|
|
* |
156
|
|
|
* @param string $operator |
157
|
|
|
* @param array|string $columns |
158
|
|
|
* @param Query $values |
159
|
|
|
* @param array $params |
160
|
|
|
* @return string SQL |
161
|
|
|
*/ |
162
|
14 |
|
protected function buildSubqueryInCondition($operator, $columns, $values, &$params) |
163
|
|
|
{ |
164
|
14 |
|
$sql = $this->queryBuilder->buildExpression($values, $params); |
165
|
|
|
|
166
|
14 |
|
if (is_array($columns)) { |
167
|
4 |
|
foreach ($columns as $i => $col) { |
168
|
4 |
|
if ($col instanceof Expression) { |
169
|
|
|
$col = $col->expression; |
170
|
|
|
} |
171
|
4 |
|
if (strpos($col, '(') === false) { |
172
|
4 |
|
$columns[$i] = $this->queryBuilder->db->quoteColumnName($col); |
173
|
|
|
} |
174
|
|
|
} |
175
|
|
|
|
176
|
4 |
|
return '(' . implode(', ', $columns) . ") $operator $sql"; |
177
|
|
|
} |
178
|
|
|
|
179
|
10 |
|
if ($columns instanceof Expression) { |
|
|
|
|
180
|
|
|
$columns = $columns->expression; |
181
|
|
|
} |
182
|
10 |
|
if (strpos($columns, '(') === false) { |
183
|
10 |
|
$columns = $this->queryBuilder->db->quoteColumnName($columns); |
184
|
|
|
} |
185
|
|
|
|
186
|
10 |
|
return "$columns $operator $sql"; |
187
|
|
|
} |
188
|
|
|
|
189
|
|
|
/** |
190
|
|
|
* Builds SQL for IN condition. |
191
|
|
|
* |
192
|
|
|
* @param string $operator |
193
|
|
|
* @param array|\Traversable $columns |
194
|
|
|
* @param array $values |
195
|
|
|
* @param array $params |
196
|
|
|
* @return string SQL |
197
|
|
|
*/ |
198
|
16 |
|
protected function buildCompositeInCondition($operator, $columns, $values, &$params) |
199
|
|
|
{ |
200
|
16 |
|
$vss = []; |
201
|
16 |
|
foreach ($values as $value) { |
202
|
16 |
|
$vs = []; |
203
|
16 |
|
foreach ($columns as $column) { |
204
|
16 |
|
if ($column instanceof Expression) { |
205
|
2 |
|
$column = $column->expression; |
206
|
|
|
} |
207
|
16 |
|
if (isset($value[$column])) { |
208
|
16 |
|
$vs[] = $this->queryBuilder->bindParam($value[$column], $params); |
209
|
|
|
} else { |
210
|
2 |
|
$vs[] = 'NULL'; |
211
|
|
|
} |
212
|
|
|
} |
213
|
16 |
|
$vss[] = '(' . implode(', ', $vs) . ')'; |
214
|
|
|
} |
215
|
|
|
|
216
|
16 |
|
if (empty($vss)) { |
217
|
|
|
return $operator === 'IN' ? '0=1' : ''; |
218
|
|
|
} |
219
|
|
|
|
220
|
16 |
|
$sqlColumns = []; |
221
|
16 |
|
foreach ($columns as $i => $column) { |
222
|
16 |
|
if ($column instanceof Expression) { |
223
|
2 |
|
$column = $column->expression; |
224
|
|
|
} |
225
|
16 |
|
$sqlColumns[] = strpos($column, '(') === false ? $this->queryBuilder->db->quoteColumnName($column) : $column; |
226
|
|
|
} |
227
|
|
|
|
228
|
16 |
|
return '(' . implode(', ', $sqlColumns) . ") $operator (" . implode(', ', $vss) . ')'; |
229
|
|
|
} |
230
|
|
|
|
231
|
|
|
/** |
232
|
|
|
* Builds is null/is not null condition for column based on operator |
233
|
|
|
* |
234
|
|
|
* @param string $operator |
235
|
|
|
* @param string $column |
236
|
|
|
* @return string is null or is not null condition |
237
|
|
|
* @since 2.0.31 |
238
|
|
|
*/ |
239
|
21 |
|
protected function getNullCondition($operator, $column) |
240
|
|
|
{ |
241
|
21 |
|
$column = $this->queryBuilder->db->quoteColumnName($column); |
242
|
21 |
|
if ($operator === 'IN') { |
243
|
9 |
|
return sprintf('%s IS NULL', $column); |
244
|
|
|
} |
245
|
12 |
|
return sprintf('%s IS NOT NULL', $column); |
246
|
|
|
} |
247
|
|
|
|
248
|
|
|
/** |
249
|
|
|
* @param \Traversable $traversableObject |
250
|
|
|
* @return array raw values |
251
|
|
|
* @since 2.0.31 |
252
|
|
|
*/ |
253
|
33 |
|
protected function getRawValuesFromTraversableObject(\Traversable $traversableObject) |
254
|
|
|
{ |
255
|
33 |
|
$rawValues = []; |
256
|
33 |
|
foreach ($traversableObject as $value) { |
257
|
33 |
|
if (is_array($value)) { |
258
|
3 |
|
$values = array_values($value); |
259
|
3 |
|
$rawValues = array_merge($rawValues, $values); |
260
|
|
|
} else { |
261
|
30 |
|
$rawValues[] = $value; |
262
|
|
|
} |
263
|
|
|
} |
264
|
33 |
|
return $rawValues; |
265
|
|
|
} |
266
|
|
|
} |
267
|
|
|
|