1
|
|
|
<?php |
2
|
|
|
/** |
3
|
|
|
* Spiral Framework. |
4
|
|
|
* |
5
|
|
|
* @license MIT |
6
|
|
|
* @author Anton Titov (Wolfy-J) |
7
|
|
|
*/ |
8
|
|
|
|
9
|
|
|
namespace Spiral\Database\Builders; |
10
|
|
|
|
11
|
|
|
use Spiral\Database\Builders\Prototypes\AbstractSelect; |
12
|
|
|
use Spiral\Database\Entities\Driver; |
13
|
|
|
use Spiral\Database\Entities\QueryCompiler; |
14
|
|
|
use Spiral\Database\Entities\QueryStatement; |
15
|
|
|
use Spiral\Database\Exceptions\BuilderException; |
16
|
|
|
use Spiral\Database\Exceptions\QueryException; |
17
|
|
|
use Spiral\Database\Injections\FragmentInterface; |
18
|
|
|
use Spiral\Debug\Traits\LoggerTrait; |
19
|
|
|
|
20
|
|
|
/** |
21
|
|
|
* SelectQuery extends AbstractSelect with ability to specify selection tables and perform UNION |
22
|
|
|
* of multiple select queries. |
23
|
|
|
*/ |
24
|
|
|
class SelectQuery extends AbstractSelect implements \JsonSerializable, \Countable |
25
|
|
|
{ |
26
|
|
|
//See SQL generation below |
27
|
|
|
use LoggerTrait; |
28
|
|
|
|
29
|
|
|
/** |
30
|
|
|
* Table names to select data from. |
31
|
|
|
* |
32
|
|
|
* @var array |
33
|
|
|
*/ |
34
|
|
|
protected $tables = []; |
35
|
|
|
|
36
|
|
|
/** |
37
|
|
|
* Select queries represented by sql fragments or query builders to be united. Stored as |
38
|
|
|
* [UNION TYPE, SELECT QUERY]. |
39
|
|
|
* |
40
|
|
|
* @var array |
41
|
|
|
*/ |
42
|
|
|
protected $unionTokens = []; |
43
|
|
|
|
44
|
|
|
/** |
45
|
|
|
* {@inheritdoc} |
46
|
|
|
* |
47
|
|
|
* @param array $from Initial set of table names. |
48
|
|
|
* @param array $columns Initial set of columns to fetch. |
49
|
|
|
*/ |
50
|
|
|
public function __construct( |
51
|
|
|
Driver $driver, |
52
|
|
|
QueryCompiler $compiler, |
53
|
|
|
array $from = [], |
54
|
|
|
array $columns = [] |
55
|
|
|
) { |
56
|
|
|
parent::__construct($driver, $compiler); |
57
|
|
|
|
58
|
|
|
$this->tables = $from; |
59
|
|
|
if (!empty($columns)) { |
60
|
|
|
$this->columns = $this->fetchIdentifiers($columns); |
61
|
|
|
} |
62
|
|
|
} |
63
|
|
|
|
64
|
|
|
/** |
65
|
|
|
* Set table names SELECT query should be performed for. Table names can be provided with |
66
|
|
|
* specified alias (AS construction). |
67
|
|
|
* |
68
|
|
|
* @param array|string|mixed $tables Array of names, comma separated string or set of |
69
|
|
|
* parameters. |
70
|
|
|
* |
71
|
|
|
* @return self|$this |
72
|
|
|
*/ |
73
|
|
|
public function from($tables): SelectQuery |
74
|
|
|
{ |
75
|
|
|
$this->tables = $this->fetchIdentifiers(func_get_args()); |
76
|
|
|
|
77
|
|
|
return $this; |
78
|
|
|
} |
79
|
|
|
|
80
|
|
|
/** |
81
|
|
|
* Tables to be loaded. |
82
|
|
|
* |
83
|
|
|
* @return array |
84
|
|
|
*/ |
85
|
|
|
public function getTables(): array |
86
|
|
|
{ |
87
|
|
|
return $this->tables; |
88
|
|
|
} |
89
|
|
|
|
90
|
|
|
/** |
91
|
|
|
* Set columns should be fetched as result of SELECT query. Columns can be provided with |
92
|
|
|
* specified alias (AS construction). |
93
|
|
|
* |
94
|
|
|
* @param array|string|mixed $columns Array of names, comma separated string or set of |
95
|
|
|
* parameters. |
96
|
|
|
* |
97
|
|
|
* @return self|$this |
98
|
|
|
*/ |
99
|
|
|
public function columns($columns): SelectQuery |
100
|
|
|
{ |
101
|
|
|
$this->columns = $this->fetchIdentifiers(func_get_args()); |
102
|
|
|
|
103
|
|
|
return $this; |
104
|
|
|
} |
105
|
|
|
|
106
|
|
|
/** |
107
|
|
|
* Set of columns to be selected. |
108
|
|
|
* |
109
|
|
|
* @return array |
110
|
|
|
*/ |
111
|
|
|
public function getColumns(): array |
112
|
|
|
{ |
113
|
|
|
return $this->columns; |
114
|
|
|
} |
115
|
|
|
|
116
|
|
|
/** |
117
|
|
|
* Add select query to be united with. |
118
|
|
|
* |
119
|
|
|
* @param FragmentInterface $query |
120
|
|
|
* |
121
|
|
|
* @return self|$this |
122
|
|
|
*/ |
123
|
|
|
public function union(FragmentInterface $query): SelectQuery |
124
|
|
|
{ |
125
|
|
|
$this->unionTokens[] = ['', $query]; |
126
|
|
|
|
127
|
|
|
return $this; |
128
|
|
|
} |
129
|
|
|
|
130
|
|
|
/** |
131
|
|
|
* Add select query to be united with. Duplicate values will be included in result. |
132
|
|
|
* |
133
|
|
|
* @param FragmentInterface $query |
134
|
|
|
* |
135
|
|
|
* @return self|$this |
136
|
|
|
*/ |
137
|
|
|
public function unionAll(FragmentInterface $query): SelectQuery |
138
|
|
|
{ |
139
|
|
|
$this->unionTokens[] = ['ALL', $query]; |
140
|
|
|
|
141
|
|
|
return $this; |
142
|
|
|
} |
143
|
|
|
|
144
|
|
|
/** |
145
|
|
|
* {@inheritdoc} |
146
|
|
|
*/ |
147
|
|
|
public function getParameters(): array |
148
|
|
|
{ |
149
|
|
|
$parameters = parent::getParameters(); |
150
|
|
|
|
151
|
|
|
//Unions always located at the end of query. |
152
|
|
|
foreach ($this->joinTokens as $join) { |
153
|
|
|
if ($join['outer'] instanceof QueryBuilder) { |
154
|
|
|
$parameters = array_merge($parameters, $join['outer']->getParameters()); |
155
|
|
|
} |
156
|
|
|
} |
157
|
|
|
|
158
|
|
|
//Unions always located at the end of query. |
159
|
|
|
foreach ($this->unionTokens as $union) { |
160
|
|
|
if ($union[1] instanceof QueryBuilder) { |
161
|
|
|
$parameters = array_merge($parameters, $union[1]->getParameters()); |
162
|
|
|
} |
163
|
|
|
} |
164
|
|
|
|
165
|
|
|
return $parameters; |
166
|
|
|
} |
167
|
|
|
|
168
|
|
|
/** |
169
|
|
|
* {@inheritdoc} |
170
|
|
|
* |
171
|
|
|
* @param bool $paginate Apply pagination to result, can be disabled in honor of count method. |
172
|
|
|
* |
173
|
|
|
* @return QueryStatement |
174
|
|
|
*/ |
175
|
|
|
public function run(bool $paginate = true) |
176
|
|
|
{ |
177
|
|
|
if ($paginate && $this->hasPaginator()) { |
178
|
|
|
/** |
179
|
|
|
* To prevent original select builder altering |
180
|
|
|
* |
181
|
|
|
* @var SelectQuery $select |
182
|
|
|
*/ |
183
|
|
|
$select = clone $this; |
184
|
|
|
|
185
|
|
|
//Getting selection specific paginator |
186
|
|
|
$paginator = $this->configurePaginator($this->count()); |
187
|
|
|
|
188
|
|
|
if (!empty($this->getLimit()) && $this->getLimit() > $paginator->getLimit()) { |
189
|
|
|
//We have to ensure that selection works inside given pagination window |
190
|
|
|
$select = $select->limit($this->getLimit()); |
191
|
|
|
} else { |
192
|
|
|
$select->limit($paginator->getLimit()); |
193
|
|
|
} |
194
|
|
|
|
195
|
|
|
//Making sure that window is shifted |
196
|
|
|
$select = $select->offset($this->getOffset() + $paginator->getOffset()); |
197
|
|
|
|
198
|
|
|
//No inner pagination |
199
|
|
|
return $select->run(false); |
200
|
|
|
} |
201
|
|
|
|
202
|
|
|
return $this->driver->query($this->sqlStatement(), $this->getParameters()); |
203
|
|
|
} |
204
|
|
|
|
205
|
|
|
/** |
206
|
|
|
* Iterate thought result using smaller data chinks with defined size and walk function. |
207
|
|
|
* |
208
|
|
|
* Example: |
209
|
|
|
* $select->chunked(100, function(PDOResult $result, $offset, $count) { |
210
|
|
|
* dump($result); |
211
|
|
|
* }); |
212
|
|
|
* |
213
|
|
|
* You must return FALSE from walk function to stop chunking. |
214
|
|
|
* |
215
|
|
|
* @param int $limit |
216
|
|
|
* @param callable $callback |
217
|
|
|
*/ |
218
|
|
|
public function runChunks(int $limit, callable $callback) |
219
|
|
|
{ |
220
|
|
|
$count = $this->count(); |
221
|
|
|
|
222
|
|
|
//To keep original query untouched |
223
|
|
|
$select = clone $this; |
224
|
|
|
$select->limit($limit); |
225
|
|
|
|
226
|
|
|
$offset = 0; |
227
|
|
|
while ($offset + $limit <= $count) { |
228
|
|
|
$result = call_user_func_array( |
229
|
|
|
$callback, |
230
|
|
|
[$select->offset($offset)->getIterator(), $offset, $count] |
231
|
|
|
); |
232
|
|
|
|
233
|
|
|
if ($result === false) { |
234
|
|
|
//Stop iteration |
235
|
|
|
return; |
236
|
|
|
} |
237
|
|
|
|
238
|
|
|
$offset += $limit; |
239
|
|
|
} |
240
|
|
|
} |
241
|
|
|
|
242
|
|
|
/** |
243
|
|
|
* {@inheritdoc} |
244
|
|
|
* |
245
|
|
|
* Count number of rows in query. Limit, offset, order by, group by values will be ignored. Do |
246
|
|
|
* not count united queries, or queries in complex joins. |
247
|
|
|
* |
248
|
|
|
* @param string $column Column to count by (every column by default). |
249
|
|
|
* |
250
|
|
|
* @return int |
251
|
|
|
*/ |
252
|
|
|
public function count(string $column = '*'): int |
253
|
|
|
{ |
254
|
|
|
/** |
255
|
|
|
* @var AbstractSelect $select |
256
|
|
|
*/ |
257
|
|
|
$select = clone $this; |
258
|
|
|
|
259
|
|
|
//To be escaped in compiler |
260
|
|
|
$select->columns = ["COUNT({$column})"]; |
261
|
|
|
$select->ordering = []; |
262
|
|
|
$select->grouping = []; |
263
|
|
|
|
264
|
|
|
return (int)$select->run(false)->fetchColumn(); |
265
|
|
|
} |
266
|
|
|
|
267
|
|
|
/** |
268
|
|
|
* {@inheritdoc} |
269
|
|
|
* |
270
|
|
|
* Shortcut to execute one of aggregation methods (AVG, MAX, MIN, SUM) using method name as |
271
|
|
|
* reference. |
272
|
|
|
* |
273
|
|
|
* Example: |
274
|
|
|
* echo $select->sum('user.balance'); |
275
|
|
|
* |
276
|
|
|
* @param string $method |
277
|
|
|
* @param array $arguments |
278
|
|
|
* |
279
|
|
|
* @return int|float |
280
|
|
|
* |
281
|
|
|
* @throws BuilderException |
282
|
|
|
* @throws QueryException |
283
|
|
|
*/ |
284
|
|
|
public function __call($method, array $arguments) |
285
|
|
|
{ |
286
|
|
|
if (!in_array($method = strtoupper($method), ['AVG', 'MIN', 'MAX', 'SUM'])) { |
287
|
|
|
throw new BuilderException("Unknown method '{$method}' in '" . get_class($this) . "'"); |
288
|
|
|
} |
289
|
|
|
|
290
|
|
|
if (!isset($arguments[0]) || count($arguments) > 1) { |
291
|
|
|
throw new BuilderException('Aggregation methods can support exactly one column'); |
292
|
|
|
} |
293
|
|
|
|
294
|
|
|
/** |
295
|
|
|
* @var AbstractSelect $select |
296
|
|
|
*/ |
297
|
|
|
$select = clone $this; |
298
|
|
|
|
299
|
|
|
//To be escaped in compiler |
300
|
|
|
$select->columns = ["{$method}({$arguments[0]})"]; |
301
|
|
|
|
302
|
|
|
$result = $select->run(false)->fetchColumn(); |
303
|
|
|
|
304
|
|
|
//Selecting type between int and float |
305
|
|
|
if ((float)$result == $result && (int)$result != $result) { |
306
|
|
|
//Find more elegant check |
307
|
|
|
return (float)$result; |
308
|
|
|
} |
309
|
|
|
|
310
|
|
|
return (int)$result; |
311
|
|
|
} |
312
|
|
|
|
313
|
|
|
/** |
314
|
|
|
* {@inheritdoc} |
315
|
|
|
* |
316
|
|
|
* @return \PDOStatement|QueryStatement |
317
|
|
|
*/ |
318
|
|
|
public function getIterator() |
319
|
|
|
{ |
320
|
|
|
return $this->run(); |
321
|
|
|
} |
322
|
|
|
|
323
|
|
|
/** |
324
|
|
|
* {@inheritdoc} |
325
|
|
|
*/ |
326
|
|
|
public function sqlStatement(QueryCompiler $compiler = null): string |
327
|
|
|
{ |
328
|
|
|
if (empty($compiler)) { |
329
|
|
|
$compiler = $this->compiler->resetQuoter(); |
330
|
|
|
} |
331
|
|
|
|
332
|
|
|
if ((!empty($this->getLimit()) || !empty($this->getOffset())) && empty($this->ordering)) { |
333
|
|
|
$this->logger()->warning( |
334
|
|
|
"Usage of LIMIT/OFFSET without proper ORDER BY statement is ambiguous" |
335
|
|
|
); |
336
|
|
|
} |
337
|
|
|
|
338
|
|
|
//11 parameters! |
339
|
|
|
return $compiler->compileSelect( |
340
|
|
|
$this->tables, |
341
|
|
|
$this->distinct, |
342
|
|
|
$this->columns, |
343
|
|
|
$this->joinTokens, |
344
|
|
|
$this->whereTokens, |
345
|
|
|
$this->havingTokens, |
346
|
|
|
$this->grouping, |
347
|
|
|
$this->ordering, |
348
|
|
|
$this->getLimit(), |
349
|
|
|
$this->getOffset(), |
350
|
|
|
$this->unionTokens |
351
|
|
|
); |
352
|
|
|
} |
353
|
|
|
|
354
|
|
|
/** |
355
|
|
|
* {@inheritdoc} |
356
|
|
|
*/ |
357
|
|
|
public function jsonSerialize() |
358
|
|
|
{ |
359
|
|
|
return $this->fetchAll(); |
360
|
|
|
} |
361
|
|
|
|
362
|
|
|
/** |
363
|
|
|
* Request all results as array. |
364
|
|
|
* |
365
|
|
|
* @return array |
366
|
|
|
*/ |
367
|
|
|
public function fetchAll(): array |
368
|
|
|
{ |
369
|
|
|
return $this->getIterator()->fetchAll(\PDO::FETCH_ASSOC); |
370
|
|
|
} |
371
|
|
|
} |
372
|
|
|
|