|
1
|
|
|
<?php |
|
2
|
|
|
namespace vakata\database\schema; |
|
3
|
|
|
|
|
4
|
|
|
use vakata\collection\Collection; |
|
5
|
|
|
use vakata\database\DBInterface; |
|
6
|
|
|
use vakata\database\DBException; |
|
7
|
|
|
use vakata\database\ResultInterface; |
|
8
|
|
|
|
|
9
|
|
|
/** |
|
10
|
|
|
* A database query class |
|
11
|
|
|
*/ |
|
12
|
|
|
class TableQuery implements \IteratorAggregate, \ArrayAccess, \Countable |
|
13
|
|
|
{ |
|
14
|
|
|
const SEP = '___'; |
|
15
|
|
|
/** |
|
16
|
|
|
* @var DBInterface |
|
17
|
|
|
*/ |
|
18
|
|
|
protected $db; |
|
19
|
|
|
/** |
|
20
|
|
|
* @var Table |
|
21
|
|
|
*/ |
|
22
|
|
|
protected $definition; |
|
23
|
|
|
/** |
|
24
|
|
|
* @var TableQueryIterator|null |
|
25
|
|
|
*/ |
|
26
|
|
|
protected $qiterator; |
|
27
|
|
|
|
|
28
|
|
|
/** |
|
29
|
|
|
* @var array |
|
30
|
|
|
*/ |
|
31
|
|
|
protected $where = []; |
|
32
|
|
|
/** |
|
33
|
|
|
* @var array |
|
34
|
|
|
*/ |
|
35
|
|
|
protected $order = []; |
|
36
|
|
|
/** |
|
37
|
|
|
* @var array |
|
38
|
|
|
*/ |
|
39
|
|
|
protected $group = []; |
|
40
|
|
|
/** |
|
41
|
|
|
* @var array |
|
42
|
|
|
*/ |
|
43
|
|
|
protected $having = []; |
|
44
|
|
|
/** |
|
45
|
|
|
* @var int[] |
|
46
|
|
|
*/ |
|
47
|
|
|
protected $li_of = [0,0,0]; |
|
48
|
|
|
/** |
|
49
|
|
|
* @var array |
|
50
|
|
|
*/ |
|
51
|
|
|
protected $fields = []; |
|
52
|
|
|
/** |
|
53
|
|
|
* @var array |
|
54
|
|
|
*/ |
|
55
|
|
|
protected $withr = []; |
|
56
|
|
|
/** |
|
57
|
|
|
* @var array |
|
58
|
|
|
*/ |
|
59
|
|
|
protected $joins = []; |
|
60
|
|
|
/** |
|
61
|
|
|
* @var array |
|
62
|
|
|
*/ |
|
63
|
|
|
protected $pkey = []; |
|
64
|
|
|
/** |
|
65
|
|
|
* @var array |
|
66
|
|
|
*/ |
|
67
|
|
|
protected $aliases = []; |
|
68
|
|
|
|
|
69
|
|
|
/** |
|
70
|
|
|
* Create an instance |
|
71
|
|
|
* @param DBInterface $db the database connection |
|
72
|
|
|
* @param Table|string $table the name or definition of the main table in the query |
|
73
|
|
|
*/ |
|
74
|
15 |
|
public function __construct(DBInterface $db, $table) |
|
75
|
|
|
{ |
|
76
|
15 |
|
$this->db = $db; |
|
77
|
15 |
|
$this->definition = $table instanceof Table ? $table : $this->db->definition((string)$table); |
|
78
|
15 |
|
$primary = $this->definition->getPrimaryKey(); |
|
79
|
15 |
|
$columns = $this->definition->getColumns(); |
|
80
|
15 |
|
$this->pkey = count($primary) ? $primary : $columns; |
|
81
|
15 |
|
$this->columns($columns); |
|
82
|
15 |
|
} |
|
83
|
|
|
public function __clone() |
|
84
|
|
|
{ |
|
85
|
|
|
$this->reset(); |
|
86
|
|
|
} |
|
87
|
|
|
/** |
|
88
|
|
|
* Get the table definition of the queried table |
|
89
|
|
|
* @return Table the definition |
|
90
|
|
|
*/ |
|
91
|
|
|
public function getDefinition() : Table |
|
92
|
|
|
{ |
|
93
|
|
|
return $this->definition; |
|
94
|
|
|
} |
|
95
|
|
|
|
|
96
|
15 |
|
protected function getColumn($column) |
|
97
|
|
|
{ |
|
98
|
15 |
|
$column = explode('.', $column); |
|
99
|
15 |
|
if (count($column) === 1) { |
|
100
|
15 |
|
$column = [ $this->definition->getName(), $column[0] ]; |
|
101
|
15 |
|
$col = $this->definition->getColumn($column[1]); |
|
102
|
15 |
|
if (!$col) { |
|
103
|
15 |
|
throw new DBException('Invalid column name in own table'); |
|
104
|
|
|
} |
|
105
|
2 |
|
} elseif (count($column) === 2) { |
|
106
|
2 |
|
if ($column[0] === $this->definition->getName()) { |
|
107
|
|
|
$col = $this->definition->getColumn($column[1]); |
|
108
|
|
|
if (!$col) { |
|
109
|
|
|
throw new DBException('Invalid column name in own table'); |
|
110
|
|
|
} |
|
111
|
|
|
} else { |
|
112
|
2 |
|
if ($this->definition->hasRelation($column[0])) { |
|
113
|
1 |
|
$col = $this->definition->getRelation($column[0])->table->getColumn($column[1]); |
|
114
|
1 |
|
if (!$col) { |
|
115
|
1 |
|
throw new DBException('Invalid column name in related table'); |
|
116
|
|
|
} |
|
117
|
1 |
|
} else if (isset($this->joins[$column[0]])) { |
|
118
|
1 |
|
$col = $this->joins[$column[0]]->table->getColumn($column[1]); |
|
119
|
1 |
|
if (!$col) { |
|
120
|
1 |
|
throw new DBException('Invalid column name in related table'); |
|
121
|
|
|
} |
|
122
|
|
|
} else { |
|
123
|
2 |
|
throw new DBException('Invalid foreign table name: ' . implode(',', $column)); |
|
124
|
|
|
} |
|
125
|
|
|
} |
|
126
|
|
|
} else { |
|
127
|
|
|
$name = array_pop($column); |
|
128
|
|
|
$this->with(implode('.', $column)); |
|
129
|
|
|
$table = $this->definition; |
|
130
|
|
|
$table = array_reduce( |
|
131
|
|
|
$column, |
|
132
|
|
|
function ($carry, $item) use (&$table) { |
|
133
|
|
|
$table = $table->getRelation($item)->table; |
|
134
|
|
|
return $table; |
|
135
|
|
|
} |
|
136
|
|
|
); |
|
137
|
|
|
$col = $table->getColumn($name); |
|
138
|
|
|
$column = [ implode(static::SEP, $column), $name ]; |
|
139
|
|
|
} |
|
140
|
15 |
|
return [ 'name' => implode('.', $column), 'data' => $col ]; |
|
141
|
|
|
} |
|
142
|
6 |
|
protected function normalizeValue(TableColumn $col, $value) |
|
143
|
|
|
{ |
|
144
|
6 |
|
if ($value === null && $col->isNullable()) { |
|
145
|
|
|
return null; |
|
146
|
|
|
} |
|
147
|
6 |
|
switch ($col->getBasicType()) { |
|
148
|
6 |
|
case 'date': |
|
149
|
|
|
if (is_string($value)) { |
|
150
|
|
|
$temp = strtotime($value); |
|
151
|
|
|
if (!$temp) { |
|
152
|
|
|
return null; |
|
153
|
|
|
} |
|
154
|
|
|
return date('Y-m-d', strtotime($value)); |
|
155
|
|
|
} |
|
156
|
|
|
if (is_int($value)) { |
|
157
|
|
|
return date('Y-m-d', $value); |
|
158
|
|
|
} |
|
159
|
|
|
if ($value instanceof \DateTime) { |
|
160
|
|
|
return $value->format('Y-m-d'); |
|
161
|
|
|
} |
|
162
|
|
|
return $value; |
|
163
|
6 |
|
case 'datetime': |
|
164
|
|
|
if (is_string($value)) { |
|
165
|
|
|
$temp = strtotime($value); |
|
166
|
|
|
if (!$temp) { |
|
167
|
|
|
return null; |
|
168
|
|
|
} |
|
169
|
|
|
return date('Y-m-d H:i:s', strtotime($value)); |
|
170
|
|
|
} |
|
171
|
|
|
if (is_int($value)) { |
|
172
|
|
|
return date('Y-m-d H:i:s', $value); |
|
173
|
|
|
} |
|
174
|
|
|
if ($value instanceof \DateTime) { |
|
175
|
|
|
return $value->format('Y-m-d H:i:s'); |
|
176
|
|
|
} |
|
177
|
|
|
return $value; |
|
178
|
6 |
|
case 'enum': |
|
179
|
|
|
if (is_int($value)) { |
|
180
|
|
|
return $value; |
|
181
|
|
|
} |
|
182
|
|
|
if (!in_array($value, $col->getValues())) { |
|
183
|
|
|
return 0; |
|
184
|
|
|
} |
|
185
|
|
|
return $value; |
|
186
|
6 |
|
case 'int': |
|
187
|
4 |
|
return (int)preg_replace('([^+\-0-9]+)', '', $value); |
|
188
|
3 |
|
case 'float': |
|
189
|
|
|
return (int)preg_replace('([^+\-0-9.]+)', '', str_replace(',', '.', $value)); |
|
190
|
|
|
default: |
|
191
|
3 |
|
return $value; |
|
192
|
|
|
} |
|
193
|
|
|
} |
|
194
|
|
|
|
|
195
|
4 |
|
protected function filterSQL(string $column, $value, bool $negate = false) : array |
|
196
|
|
|
{ |
|
197
|
4 |
|
list($name, $column) = array_values($this->getColumn($column)); |
|
198
|
4 |
|
if (is_array($value) && count($value) === 1 && isset($value['not'])) { |
|
199
|
1 |
|
$negate = true; |
|
200
|
1 |
|
$value = $value['not']; |
|
201
|
|
|
} |
|
202
|
4 |
|
if (is_null($value)) { |
|
203
|
|
|
return $negate ? |
|
204
|
|
|
[ $name . ' IS NOT NULL', [] ]: |
|
205
|
|
|
[ $name . ' IS NULL', [] ]; |
|
206
|
|
|
} |
|
207
|
4 |
|
if (!is_array($value)) { |
|
208
|
4 |
|
return $negate ? |
|
209
|
|
|
[ |
|
210
|
1 |
|
$name . ' <> ?', |
|
211
|
1 |
|
[ $this->normalizeValue($column, $value) ] |
|
212
|
|
|
] : |
|
213
|
|
|
[ |
|
214
|
4 |
|
$name . ' = ?', |
|
215
|
4 |
|
[ $this->normalizeValue($column, $value) ] |
|
216
|
|
|
]; |
|
217
|
|
|
} |
|
218
|
3 |
|
if (isset($value['beg']) && strlen($value['beg']) && (!isset($value['end']) || !strlen($value['end']))) { |
|
219
|
|
|
$value = [ 'gte' => $value['beg'] ]; |
|
220
|
|
|
} |
|
221
|
3 |
|
if (isset($value['end']) && strlen($value['end']) && (!isset($value['beg']) || !strlen($value['beg']))) { |
|
222
|
|
|
$value = [ 'lte' => $value['end'] ]; |
|
223
|
|
|
} |
|
224
|
3 |
|
if (isset($value['beg']) && isset($value['end'])) { |
|
225
|
|
|
return $negate ? |
|
226
|
|
|
[ |
|
227
|
|
|
$name.' NOT BETWEEN ? AND ?', |
|
228
|
|
|
[ |
|
229
|
|
|
$this->normalizeValue($column, $value['beg']), |
|
230
|
|
|
$this->normalizeValue($column, $value['end']) |
|
231
|
|
|
] |
|
232
|
|
|
] : |
|
233
|
|
|
[ |
|
234
|
|
|
$name.' BETWEEN ? AND ?', |
|
235
|
|
|
[ |
|
236
|
|
|
$this->normalizeValue($column, $value['beg']), |
|
237
|
|
|
$this->normalizeValue($column, $value['end']) |
|
238
|
|
|
] |
|
239
|
|
|
]; |
|
240
|
|
|
} |
|
241
|
3 |
|
if (isset($value['gt']) || isset($value['lt']) || isset($value['gte']) || isset($value['lte'])) { |
|
242
|
2 |
|
$sql = []; |
|
243
|
2 |
|
$par = []; |
|
244
|
2 |
|
if (isset($value['gt'])) { |
|
245
|
1 |
|
$sql[] = $name. ' ' . ($negate ? '<=' : '>') . ' ?'; |
|
246
|
1 |
|
$par[] = $this->normalizeValue($column, $value['gt']); |
|
247
|
|
|
} |
|
248
|
2 |
|
if (isset($value['gte'])) { |
|
249
|
1 |
|
$sql[] = $name. ' ' . ($negate ? '<' : '>=') . ' ?'; |
|
250
|
1 |
|
$par[] = $this->normalizeValue($column, $value['gte']); |
|
251
|
|
|
} |
|
252
|
2 |
|
if (isset($value['lt'])) { |
|
253
|
2 |
|
$sql[] = $name. ' ' . ($negate ? '>=' : '<') . ' ?'; |
|
254
|
2 |
|
$par[] = $this->normalizeValue($column, $value['lt']); |
|
255
|
|
|
} |
|
256
|
2 |
|
if (isset($value['lte'])) { |
|
257
|
1 |
|
$sql[] = $name. ' ' . ($negate ? '>' : '<=') . ' ?'; |
|
258
|
1 |
|
$par[] = $this->normalizeValue($column, $value['lte']); |
|
259
|
|
|
} |
|
260
|
|
|
return [ |
|
261
|
2 |
|
'(' . implode(' AND ', $sql) . ')', |
|
262
|
2 |
|
$par |
|
263
|
|
|
]; |
|
264
|
|
|
} |
|
265
|
2 |
|
return $negate ? |
|
266
|
|
|
[ |
|
267
|
|
|
$name . ' NOT IN (??)', |
|
268
|
|
|
[ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ] |
|
269
|
|
|
] : |
|
270
|
|
|
[ |
|
271
|
2 |
|
$name . ' IN (??)', |
|
272
|
|
|
[ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ] |
|
273
|
|
|
]; |
|
274
|
|
|
} |
|
275
|
|
|
/** |
|
276
|
|
|
* Filter the results by a column and a value |
|
277
|
|
|
* @param string $column the column name to filter by (related columns can be used - for example: author.name) |
|
278
|
|
|
* @param mixed $value a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3]) |
|
279
|
|
|
* @param bool $negate optional boolean indicating that the filter should be negated |
|
280
|
|
|
* @return $this |
|
281
|
|
|
*/ |
|
282
|
2 |
|
public function filter(string $column, $value, bool $negate = false) : TableQuery |
|
283
|
|
|
{ |
|
284
|
2 |
|
$sql = $this->filterSQL($column, $value, $negate); |
|
285
|
2 |
|
return strlen($sql[0]) ? $this->where($sql[0], $sql[1]) : $this; |
|
286
|
|
|
} |
|
287
|
|
|
/** |
|
288
|
|
|
* Filter the results matching any of the criteria |
|
289
|
|
|
* @param array $criteria each row is a column, value and optional negate flag (same as filter method) |
|
290
|
|
|
* @return $this |
|
291
|
|
|
*/ |
|
292
|
1 |
|
public function any(array $criteria) : TableQuery |
|
293
|
|
|
{ |
|
294
|
1 |
|
$sql = []; |
|
295
|
1 |
|
$par = []; |
|
296
|
1 |
|
foreach ($criteria as $row) { |
|
297
|
1 |
|
if (isset($row[1])) { |
|
298
|
1 |
|
$temp = $this->filterSQL($row[0], $row[1] ?? null, $row[2] ?? false); |
|
299
|
1 |
|
$sql[] = $temp[0]; |
|
300
|
1 |
|
$par = array_merge($par, $temp[1]); |
|
301
|
|
|
} |
|
302
|
|
|
} |
|
303
|
1 |
|
return $this->where('(' . implode(' OR ', $sql) . ')', $par); |
|
304
|
|
|
} |
|
305
|
|
|
/** |
|
306
|
|
|
* Filter the results matching all of the criteria |
|
307
|
|
|
* @param array $criteria each row is a column, value and optional negate flag (same as filter method) |
|
308
|
|
|
* @return $this |
|
309
|
|
|
*/ |
|
310
|
1 |
|
public function all(array $criteria) : TableQuery |
|
311
|
|
|
{ |
|
312
|
1 |
|
$sql = []; |
|
313
|
1 |
|
$par = []; |
|
314
|
1 |
|
foreach ($criteria as $row) { |
|
315
|
1 |
|
if (isset($row[1])) { |
|
316
|
1 |
|
$temp = $this->filterSQL($row[0], $row[1] ?? null, $row[2] ?? false); |
|
317
|
1 |
|
$sql[] = $temp[0]; |
|
318
|
1 |
|
$par = array_merge($par, $temp[1]); |
|
319
|
|
|
} |
|
320
|
|
|
} |
|
321
|
1 |
|
return $this->where('(' . implode(' AND ', $sql) . ')', $par); |
|
322
|
|
|
} |
|
323
|
|
|
/** |
|
324
|
|
|
* Sort by a column |
|
325
|
|
|
* @param string $column the column name to sort by (related columns can be used - for example: author.name) |
|
326
|
|
|
* @param bool|boolean $desc should the sorting be in descending order, defaults to `false` |
|
327
|
|
|
* @return $this |
|
328
|
|
|
*/ |
|
329
|
|
|
public function sort(string $column, bool $desc = false) : TableQuery |
|
330
|
|
|
{ |
|
331
|
|
|
return $this->order($this->getColumn($column)['name'] . ' ' . ($desc ? 'DESC' : 'ASC')); |
|
332
|
|
|
} |
|
333
|
|
|
/** |
|
334
|
|
|
* Group by a column (or columns) |
|
335
|
|
|
* @param string|array $column the column name (or names) to group by |
|
336
|
|
|
* @return $this |
|
337
|
|
|
*/ |
|
338
|
1 |
|
public function group($column) : TableQuery |
|
339
|
|
|
{ |
|
340
|
1 |
|
if (!is_array($column)) { |
|
341
|
1 |
|
$column = [ $column ]; |
|
342
|
|
|
} |
|
343
|
1 |
|
foreach ($column as $k => $v) { |
|
344
|
1 |
|
$column[$k] = $this->getColumn($v)['name']; |
|
345
|
|
|
} |
|
346
|
1 |
|
return $this->groupBy(implode(', ', $column), []); |
|
347
|
|
|
} |
|
348
|
|
|
/** |
|
349
|
|
|
* Get a part of the data |
|
350
|
|
|
* @param int|integer $page the page number to get (1-based), defaults to 1 |
|
351
|
|
|
* @param int|integer $perPage the number of records per page - defaults to 25 |
|
352
|
|
|
* @return $this |
|
353
|
|
|
*/ |
|
354
|
|
|
public function paginate(int $page = 1, int $perPage = 25) : TableQuery |
|
355
|
|
|
{ |
|
356
|
|
|
return $this->limit($perPage, ($page - 1) * $perPage); |
|
357
|
|
|
} |
|
358
|
1 |
|
public function __call($name, $data) |
|
359
|
|
|
{ |
|
360
|
1 |
|
if (strpos($name, 'filterBy') === 0) { |
|
361
|
|
|
return $this->filter(strtolower(substr($name, 8)), $data[0]); |
|
362
|
|
|
} |
|
363
|
1 |
|
if (strpos($name, 'sortBy') === 0) { |
|
364
|
|
|
return $this->sort(strtolower(substr($name, 6)), $data[0]); |
|
365
|
|
|
} |
|
366
|
1 |
|
if (strpos($name, 'groupBy') === 0) { |
|
367
|
1 |
|
return $this->group(strtolower(substr($name, 7))); |
|
368
|
|
|
} |
|
369
|
|
|
} |
|
370
|
|
|
/** |
|
371
|
|
|
* Remove all filters, sorting, etc |
|
372
|
|
|
* @return $this |
|
373
|
|
|
*/ |
|
374
|
2 |
|
public function reset() : TableQuery |
|
375
|
|
|
{ |
|
376
|
2 |
|
$this->where = []; |
|
377
|
2 |
|
$this->joins = []; |
|
378
|
2 |
|
$this->group = []; |
|
379
|
2 |
|
$this->withr = []; |
|
380
|
2 |
|
$this->order = []; |
|
381
|
2 |
|
$this->having = []; |
|
382
|
2 |
|
$this->aliases = []; |
|
383
|
2 |
|
$this->li_of = [0,0,0]; |
|
384
|
2 |
|
$this->qiterator = null; |
|
385
|
2 |
|
return $this; |
|
386
|
|
|
} |
|
387
|
|
|
/** |
|
388
|
|
|
* Apply advanced grouping |
|
389
|
|
|
* @param string $sql SQL statement to use in the GROUP BY clause |
|
390
|
|
|
* @param array $params optional params for the statement (defaults to an empty array) |
|
391
|
|
|
* @return $this |
|
392
|
|
|
*/ |
|
393
|
1 |
|
public function groupBy(string $sql, array $params = []) : TableQuery |
|
394
|
|
|
{ |
|
395
|
1 |
|
$this->qiterator = null; |
|
396
|
1 |
|
$this->group = [ $sql, $params ]; |
|
397
|
1 |
|
return $this; |
|
398
|
|
|
} |
|
399
|
|
|
/** |
|
400
|
|
|
* Join a table to the query (no need to do this for relations defined with foreign keys) |
|
401
|
|
|
* @param Table|string $table the table to join |
|
402
|
|
|
* @param array $fields what to join on (joined_table_field => other_field) |
|
403
|
|
|
* @param string|null $name alias for the join, defaults to the table name |
|
404
|
|
|
* @param bool $multiple are multiple rows joined (results in a LEFT JOIN), default to true |
|
405
|
|
|
* @return $this |
|
406
|
|
|
*/ |
|
407
|
1 |
|
public function join($table, array $fields, string $name = null, bool $multiple = true) |
|
408
|
|
|
{ |
|
409
|
1 |
|
$table = $table instanceof Table ? $table : $this->db->definition((string)$table); |
|
410
|
1 |
|
$name = $name ?? $table->getName(); |
|
411
|
1 |
|
if (isset($this->joins[$name]) || $this->definition->hasRelation($name)) { |
|
412
|
|
|
throw new DBException('Alias / table name already in use'); |
|
413
|
|
|
} |
|
414
|
1 |
|
$this->joins[$name] = new TableRelation($name, $table, [], $multiple); |
|
415
|
1 |
|
foreach ($fields as $k => $v) { |
|
416
|
1 |
|
$k = explode('.', $k, 2); |
|
417
|
1 |
|
$k = count($k) == 2 ? $k[1] : $k[0]; |
|
418
|
1 |
|
$this->joins[$name]->keymap[$this->getColumn($name . '.' . $k)['name']] = $this->getColumn($v)['name']; |
|
419
|
|
|
} |
|
420
|
1 |
|
return $this; |
|
421
|
|
|
} |
|
422
|
|
|
/** |
|
423
|
|
|
* Apply an advanced filter (can be called multiple times) |
|
424
|
|
|
* @param string $sql SQL statement to be used in the where clause |
|
425
|
|
|
* @param array $params parameters for the SQL statement (defaults to an empty array) |
|
426
|
|
|
* @return $this |
|
427
|
|
|
*/ |
|
428
|
5 |
|
public function where(string $sql, array $params = []) : TableQuery |
|
429
|
|
|
{ |
|
430
|
5 |
|
$this->qiterator = null; |
|
431
|
5 |
|
$this->where[] = [ $sql, $params ]; |
|
432
|
5 |
|
return $this; |
|
433
|
|
|
} |
|
434
|
|
|
/** |
|
435
|
|
|
* Apply an advanced HAVING filter (can be called multiple times) |
|
436
|
|
|
* @param string $sql SQL statement to be used in the HAING clause |
|
437
|
|
|
* @param array $params parameters for the SQL statement (defaults to an empty array) |
|
438
|
|
|
* @return $this |
|
439
|
|
|
*/ |
|
440
|
1 |
|
public function having(string $sql, array $params = []) : TableQuery |
|
441
|
|
|
{ |
|
442
|
1 |
|
$this->qiterator = null; |
|
443
|
1 |
|
$this->having[] = [ $sql, $params ]; |
|
444
|
1 |
|
return $this; |
|
445
|
|
|
} |
|
446
|
|
|
/** |
|
447
|
|
|
* Apply advanced sorting |
|
448
|
|
|
* @param string $sql SQL statement to use in the ORDER clause |
|
449
|
|
|
* @param array $params optional params for the statement (defaults to an empty array) |
|
450
|
|
|
* @return $this |
|
451
|
|
|
*/ |
|
452
|
1 |
|
public function order(string $sql, array $params = []) : TableQuery |
|
453
|
|
|
{ |
|
454
|
1 |
|
$this->qiterator = null; |
|
455
|
1 |
|
$this->order = [ $sql, $params ]; |
|
456
|
1 |
|
return $this; |
|
457
|
|
|
} |
|
458
|
|
|
/** |
|
459
|
|
|
* Apply an advanced limit |
|
460
|
|
|
* @param int $limit number of rows to return |
|
461
|
|
|
* @param int $offset number of rows to skip from the beginning (defaults to 0) |
|
462
|
|
|
* @return $this |
|
463
|
|
|
*/ |
|
464
|
1 |
|
public function limit(int $limit, int $offset = 0, bool $limitOnMainTable = false) : TableQuery |
|
465
|
|
|
{ |
|
466
|
1 |
|
$this->qiterator = null; |
|
467
|
1 |
|
$this->li_of = [ $limit, $offset, $limitOnMainTable ? 1 : 0 ]; |
|
468
|
1 |
|
return $this; |
|
469
|
|
|
} |
|
470
|
|
|
/** |
|
471
|
|
|
* Get the number of records |
|
472
|
|
|
* @return int the total number of records (does not respect pagination) |
|
473
|
|
|
*/ |
|
474
|
5 |
|
public function count() : int |
|
475
|
|
|
{ |
|
476
|
5 |
|
$aliases = []; |
|
477
|
5 |
|
$getAlias = function ($name) use (&$aliases) { |
|
478
|
|
|
// to bypass use: return $name; |
|
|
|
|
|
|
479
|
1 |
|
return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases); |
|
480
|
5 |
|
}; |
|
481
|
5 |
|
$table = $this->definition->getName(); |
|
482
|
5 |
|
$sql = 'SELECT COUNT(DISTINCT '.$table.'.'.implode(', '.$table.'.', $this->pkey).') FROM '.$table.' '; |
|
483
|
5 |
|
$par = []; |
|
484
|
|
|
|
|
485
|
5 |
|
$relations = $this->withr; |
|
486
|
5 |
|
foreach ($relations as $k => $v) { |
|
487
|
|
|
$getAlias($k); |
|
488
|
|
|
} |
|
489
|
5 |
|
$f = $this->fields; |
|
|
|
|
|
|
490
|
5 |
|
$w = $this->where; |
|
491
|
5 |
|
$h = $this->having; |
|
492
|
5 |
|
$o = $this->order; |
|
493
|
5 |
|
$g = $this->group; |
|
494
|
|
|
$j = array_map(function ($v) { return clone $v; }, $this->joins); |
|
495
|
5 |
|
foreach ($this->definition->getRelations() as $k => $v) { |
|
496
|
5 |
|
foreach ($w as $kk => $vv) { |
|
497
|
3 |
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv[0])) { |
|
498
|
1 |
|
$relations[$k] = [ $v, $table ]; |
|
499
|
3 |
|
$w[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv[0]); |
|
500
|
|
|
} |
|
501
|
|
|
} |
|
502
|
5 |
|
if (isset($o[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $o[0])) { |
|
503
|
|
|
$relations[$k] = [ $v, $table ]; |
|
504
|
|
|
} |
|
505
|
5 |
|
foreach ($h as $kk => $vv) { |
|
506
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv[0])) { |
|
507
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
508
|
|
|
$h[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv[0]); |
|
509
|
|
|
} |
|
510
|
|
|
} |
|
511
|
5 |
|
if (isset($g[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $g[0])) { |
|
512
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
513
|
|
|
$g[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $g[0]); |
|
514
|
|
|
} |
|
515
|
5 |
|
foreach ($j as $kk => $v) { |
|
516
|
|
|
foreach ($v->keymap as $kkk => $vv) { |
|
517
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv)) { |
|
518
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
519
|
5 |
|
$j[$k]->keymap[$kkk] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv); |
|
520
|
|
|
} |
|
521
|
|
|
} |
|
522
|
|
|
} |
|
523
|
|
|
} |
|
524
|
|
|
|
|
525
|
5 |
|
foreach ($j as $k => $v) { |
|
526
|
|
|
$sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
|
527
|
|
|
$tmp = []; |
|
528
|
|
|
foreach ($v->keymap as $kk => $vv) { |
|
529
|
|
|
$tmp[] = $kk.' = '.$vv; |
|
530
|
|
|
} |
|
531
|
|
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
532
|
|
|
} |
|
533
|
5 |
|
foreach ($relations as $k => $v) { |
|
534
|
1 |
|
$table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1]; |
|
535
|
1 |
|
$v = $v[0]; |
|
536
|
1 |
|
if ($v->pivot) { |
|
537
|
1 |
|
$alias = $getAlias($k.'_pivot'); |
|
538
|
1 |
|
$sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON '; |
|
539
|
1 |
|
$tmp = []; |
|
540
|
1 |
|
foreach ($v->keymap as $kk => $vv) { |
|
541
|
1 |
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
542
|
|
|
} |
|
543
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
544
|
1 |
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($k).' ON '; |
|
545
|
1 |
|
$tmp = []; |
|
546
|
1 |
|
foreach ($v->pivot_keymap as $kk => $vv) { |
|
547
|
1 |
|
$tmp[] = $getAlias($k).'.'.$vv.' = '.$alias.'.'.$kk.' '; |
|
548
|
|
|
} |
|
549
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
550
|
|
|
} else { |
|
551
|
1 |
|
$alias = $getAlias($k); |
|
552
|
1 |
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON '; |
|
553
|
1 |
|
$tmp = []; |
|
554
|
1 |
|
foreach ($v->keymap as $kk => $vv) { |
|
555
|
1 |
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
556
|
|
|
} |
|
557
|
1 |
|
if ($v->sql) { |
|
558
|
|
|
$tmp[] = $v->sql . ' '; |
|
559
|
|
|
$par = array_merge($par, $v->par ?? []); |
|
560
|
|
|
} |
|
561
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
562
|
|
|
} |
|
563
|
|
|
} |
|
564
|
5 |
|
if (count($w)) { |
|
565
|
3 |
|
$sql .= 'WHERE '; |
|
566
|
3 |
|
$tmp = []; |
|
567
|
3 |
|
foreach ($w as $v) { |
|
568
|
3 |
|
$tmp[] = '(' . $v[0] . ')'; |
|
569
|
3 |
|
$par = array_merge($par, $v[1]); |
|
570
|
|
|
} |
|
571
|
3 |
|
$sql .= implode(' AND ', $tmp).' '; |
|
572
|
|
|
} |
|
573
|
5 |
|
if (count($g)) { |
|
574
|
|
|
$sql .= 'GROUP BY ' . $g[0] . ' '; |
|
575
|
|
|
$par = array_merge($par, $g[1]); |
|
576
|
|
|
} |
|
577
|
5 |
|
if (count($h)) { |
|
578
|
|
|
$sql .= 'HAVING '; |
|
579
|
|
|
$tmp = []; |
|
580
|
|
|
foreach ($h as $v) { |
|
581
|
|
|
$tmp[] = '(' . $v[0] . ')'; |
|
582
|
|
|
$par = array_merge($par, $v[1]); |
|
583
|
|
|
} |
|
584
|
|
|
$sql .= implode(' AND ', $tmp).' '; |
|
585
|
|
|
} |
|
586
|
5 |
|
return $this->db->one($sql, $par); |
|
587
|
|
|
} |
|
588
|
|
|
/** |
|
589
|
|
|
* Specify which columns to fetch (be default all table columns are fetched) |
|
590
|
|
|
* @param array $fields optional array of columns to select (related columns can be used too) |
|
591
|
|
|
* @return $this |
|
592
|
|
|
*/ |
|
593
|
15 |
|
public function columns(array $fields) : TableQuery |
|
594
|
|
|
{ |
|
595
|
15 |
|
foreach ($fields as $k => $v) { |
|
596
|
15 |
|
if (strpos($v, '*') !== false) { |
|
597
|
|
|
$temp = explode('.', $v); |
|
598
|
|
|
if (count($temp) === 1) { |
|
599
|
|
|
$table = $this->definition->getName(); |
|
600
|
|
|
$cols = $this->definition->getColumns(); |
|
601
|
|
|
} else if (count($temp) === 2) { |
|
602
|
|
|
$table = $temp[0]; |
|
603
|
|
|
if ($this->definition->hasRelation($table)) { |
|
604
|
|
|
$cols = $this->definition->getRelation($table)->table->getColumns(); |
|
605
|
|
|
} else if (isset($this->joins[$table])) { |
|
606
|
|
|
$cols = $this->joins[$table]->table->getColumns(); |
|
607
|
|
|
} else { |
|
608
|
|
|
throw new DBException('Invalid foreign table name'); |
|
609
|
|
|
} |
|
610
|
|
|
} else { |
|
611
|
|
|
array_pop($temp); |
|
612
|
|
|
$this->with(implode('.', $temp)); |
|
613
|
|
|
$table = array_reduce( |
|
614
|
|
|
$temp, |
|
615
|
|
|
function ($carry, $item) use (&$table) { |
|
616
|
|
|
return $table->getRelation($item)->table; |
|
617
|
|
|
} |
|
618
|
|
|
); |
|
619
|
|
|
$cols = $table->getColumns(); |
|
620
|
|
|
$table = implode(static::SEP, $temp); |
|
621
|
|
|
} |
|
622
|
|
|
unset($fields[$k]); |
|
623
|
|
|
foreach ($cols as $col) { |
|
624
|
15 |
|
$fields[] = $table . '.' . $col; |
|
625
|
|
|
} |
|
626
|
|
|
} |
|
627
|
|
|
} |
|
628
|
15 |
|
$primary = $this->definition->getPrimaryKey(); |
|
629
|
15 |
|
foreach ($fields as $k => $v) { |
|
630
|
|
|
try { |
|
631
|
15 |
|
$fields[$k] = $this->getColumn($v)['name']; |
|
632
|
1 |
|
} catch (DBException $e) { |
|
633
|
15 |
|
$fields[$k] = $v; |
|
634
|
|
|
} |
|
635
|
|
|
} |
|
636
|
15 |
|
foreach ($primary as $field) { |
|
637
|
15 |
|
$field = $this->getColumn($field)['name']; |
|
638
|
15 |
|
if (!in_array($field, $fields)) { |
|
639
|
15 |
|
$fields[] = $field; |
|
640
|
|
|
} |
|
641
|
|
|
} |
|
642
|
15 |
|
$this->fields = $fields; |
|
643
|
15 |
|
return $this; |
|
644
|
|
|
} |
|
645
|
|
|
/** |
|
646
|
|
|
* Perform the actual fetch |
|
647
|
|
|
* @param array|null $fields optional array of columns to select (related columns can be used too) |
|
648
|
|
|
* @return TableQueryIterator the query result as an iterator |
|
649
|
|
|
*/ |
|
650
|
11 |
|
public function iterator(array $fields = null) : TableQueryIterator |
|
651
|
|
|
{ |
|
652
|
11 |
|
if ($this->qiterator) { |
|
653
|
5 |
|
return $this->qiterator; |
|
654
|
|
|
} |
|
655
|
11 |
|
$aliases = []; |
|
656
|
11 |
|
$getAlias = function ($name) use (&$aliases) { |
|
657
|
|
|
// to bypass use: return $name; |
|
|
|
|
|
|
658
|
4 |
|
return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases); |
|
659
|
11 |
|
}; |
|
660
|
11 |
|
$table = $this->definition->getName(); |
|
661
|
11 |
|
if ($fields !== null) { |
|
662
|
1 |
|
$this->columns($fields); |
|
663
|
|
|
} |
|
664
|
11 |
|
$relations = $this->withr; |
|
665
|
11 |
|
foreach ($relations as $k => $v) { |
|
666
|
4 |
|
$getAlias($k); |
|
667
|
|
|
} |
|
668
|
|
|
|
|
669
|
11 |
|
$f = $this->fields; |
|
670
|
11 |
|
$w = $this->where; |
|
671
|
11 |
|
$h = $this->having; |
|
672
|
11 |
|
$o = $this->order; |
|
673
|
11 |
|
$g = $this->group; |
|
674
|
|
|
$j = array_map(function ($v) { return clone $v; }, $this->joins); |
|
675
|
|
|
|
|
676
|
11 |
|
$porder = []; |
|
677
|
11 |
|
foreach ($this->definition->getPrimaryKey() as $field) { |
|
678
|
11 |
|
$porder[] = $this->getColumn($field)['name']; |
|
679
|
|
|
} |
|
680
|
|
|
|
|
681
|
11 |
|
if (count($porder) && $this->li_of[2] === 1) { |
|
682
|
|
|
$ids = $this->ids(); |
|
683
|
|
|
if (count($ids)) { |
|
684
|
|
|
if (count($porder) > 1) { |
|
685
|
|
|
$pkw = []; |
|
686
|
|
|
foreach ($porder as $name) { |
|
687
|
|
|
$pkw[] = $name . ' = ?'; |
|
688
|
|
|
} |
|
689
|
|
|
$pkw = '(' . implode(' AND ', $pkw) . ')'; |
|
690
|
|
|
$pkp = []; |
|
691
|
|
|
foreach ($ids as $id) { |
|
692
|
|
|
foreach ($id as $p) { |
|
693
|
|
|
$pkp[] = $p; |
|
694
|
|
|
} |
|
695
|
|
|
} |
|
696
|
|
|
$w[] = [ |
|
697
|
|
|
implode(' OR ', array_fill(0, count($ids), $pkw)), |
|
698
|
|
|
$pkp |
|
699
|
|
|
]; |
|
700
|
|
|
} else { |
|
701
|
|
|
$w[] = [ $porder[0] . ' IN ('.implode(',', array_fill(0, count($ids), '?')).')', $ids ]; |
|
702
|
|
|
} |
|
703
|
|
|
} else { |
|
704
|
|
|
$w[] = [ '1=0', [] ]; |
|
705
|
|
|
} |
|
706
|
|
|
} |
|
707
|
|
|
|
|
708
|
11 |
|
foreach ($this->definition->getRelations() as $k => $relation) { |
|
709
|
11 |
|
foreach ($f as $kk => $field) { |
|
710
|
11 |
|
if (strpos($field, $k . '.') === 0) { |
|
711
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
712
|
11 |
|
$f[$kk] = str_replace($k . '.', $getAlias($k) . '.', $field); |
|
713
|
|
|
} |
|
714
|
|
|
} |
|
715
|
11 |
|
foreach ($w as $kk => $v) { |
|
716
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $v[0])) { |
|
717
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
718
|
|
|
$w[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $v[0]); |
|
719
|
|
|
} |
|
720
|
|
|
} |
|
721
|
11 |
|
foreach ($h as $kk => $v) { |
|
722
|
1 |
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $v[0])) { |
|
723
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
724
|
1 |
|
$h[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $v[0]); |
|
725
|
|
|
} |
|
726
|
|
|
} |
|
727
|
11 |
|
if (isset($o[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $o[0])) { |
|
728
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
729
|
|
|
$o[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $o[0]); |
|
730
|
|
|
} |
|
731
|
11 |
|
if (isset($g[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $g[0])) { |
|
732
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
733
|
|
|
$g[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $g[0]); |
|
734
|
|
|
} |
|
735
|
11 |
|
foreach ($j as $kk => $v) { |
|
736
|
1 |
|
foreach ($v->keymap as $kkk => $vv) { |
|
737
|
1 |
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv)) { |
|
738
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
739
|
11 |
|
$j[$k]->keymap[$kkk] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv); |
|
740
|
|
|
} |
|
741
|
|
|
} |
|
742
|
|
|
} |
|
743
|
|
|
} |
|
744
|
11 |
|
$select = []; |
|
745
|
11 |
|
foreach ($f as $k => $field) { |
|
746
|
11 |
|
$select[] = $field . (!is_numeric($k) ? ' ' . $k : ''); |
|
747
|
|
|
} |
|
748
|
11 |
|
foreach ($this->withr as $name => $relation) { |
|
749
|
4 |
|
foreach ($relation[0]->table->getColumns() as $column) { |
|
750
|
4 |
|
$select[] = $getAlias($name) . '.' . $column . ' ' . $getAlias($name . static::SEP . $column); |
|
751
|
|
|
} |
|
752
|
|
|
} |
|
753
|
11 |
|
$sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' '; |
|
754
|
11 |
|
$par = []; |
|
755
|
11 |
|
foreach ($j as $k => $v) { |
|
756
|
1 |
|
$sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
|
757
|
1 |
|
$tmp = []; |
|
758
|
1 |
|
foreach ($v->keymap as $kk => $vv) { |
|
759
|
1 |
|
$tmp[] = $kk.' = '.$vv; |
|
760
|
|
|
} |
|
761
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
762
|
|
|
} |
|
763
|
11 |
|
foreach ($relations as $relation => $v) { |
|
764
|
4 |
|
$table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1]; |
|
765
|
4 |
|
$v = $v[0]; |
|
766
|
4 |
|
if ($v->pivot) { |
|
767
|
3 |
|
$alias = $getAlias($relation.'_pivot'); |
|
768
|
3 |
|
$sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON '; |
|
769
|
3 |
|
$tmp = []; |
|
770
|
3 |
|
foreach ($v->keymap as $kk => $vv) { |
|
771
|
3 |
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
772
|
|
|
} |
|
773
|
3 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
774
|
3 |
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($relation).' ON '; |
|
775
|
3 |
|
$tmp = []; |
|
776
|
3 |
|
foreach ($v->pivot_keymap as $kk => $vv) { |
|
777
|
3 |
|
$tmp[] = $getAlias($relation).'.'.$vv.' = '.$alias.'.'.$kk.' '; |
|
778
|
|
|
} |
|
779
|
3 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
780
|
|
|
} else { |
|
781
|
4 |
|
$alias = $getAlias($relation); |
|
782
|
|
|
|
|
783
|
4 |
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON '; |
|
784
|
4 |
|
$tmp = []; |
|
785
|
4 |
|
foreach ($v->keymap as $kk => $vv) { |
|
786
|
4 |
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
787
|
|
|
} |
|
788
|
4 |
|
if ($v->sql) { |
|
789
|
|
|
$tmp[] = $v->sql . ' '; |
|
790
|
|
|
$par = array_merge($par, $v->par ?? []); |
|
791
|
|
|
} |
|
792
|
4 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
793
|
|
|
} |
|
794
|
|
|
} |
|
795
|
11 |
|
if (count($w)) { |
|
796
|
|
|
$sql .= 'WHERE '; |
|
797
|
|
|
$tmp = []; |
|
798
|
|
|
foreach ($w as $v) { |
|
799
|
|
|
$tmp[] = '(' . $v[0] . ')'; |
|
800
|
|
|
$par = array_merge($par, $v[1]); |
|
801
|
|
|
} |
|
802
|
|
|
$sql .= implode(' AND ', $tmp).' '; |
|
803
|
|
|
} |
|
804
|
11 |
|
if (count($g)) { |
|
805
|
1 |
|
$sql .= 'GROUP BY ' . $g[0] . ' '; |
|
806
|
1 |
|
$par = array_merge($par, $g[1]); |
|
807
|
|
|
} |
|
808
|
11 |
|
if (count($h)) { |
|
809
|
1 |
|
$sql .= 'HAVING '; |
|
810
|
1 |
|
$tmp = []; |
|
811
|
1 |
|
foreach ($h as $v) { |
|
812
|
1 |
|
$tmp[] = '(' . $v[0] . ')'; |
|
813
|
1 |
|
$par = array_merge($par, $v[1]); |
|
814
|
|
|
} |
|
815
|
1 |
|
$sql .= implode(' AND ', $tmp).' '; |
|
816
|
|
|
} |
|
817
|
11 |
|
if (count($o)) { |
|
818
|
1 |
|
$sql .= 'ORDER BY ' . $o[0] . ' '; |
|
819
|
1 |
|
$par = array_merge($par, $o[1]); |
|
820
|
|
|
} |
|
821
|
11 |
|
if (count($porder)) { |
|
822
|
11 |
|
$sql .= (count($o) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' '; |
|
823
|
|
|
} |
|
824
|
11 |
|
if (($this->li_of[2] === 0 || !count($porder)) && $this->li_of[0]) { |
|
825
|
1 |
|
if ($this->db->driverName() === 'oracle') { |
|
826
|
|
|
if ((int)$this->db->driverOption('version', 0) >= 12) { |
|
827
|
|
|
$sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY'; |
|
828
|
|
|
} else { |
|
829
|
|
|
$f = array_map(function ($v) { |
|
830
|
|
|
$v = explode(' ', trim($v), 2); |
|
831
|
|
|
if (count($v) === 2) { return $v[1]; } |
|
832
|
|
|
$v = explode('.', $v[0], 2); |
|
833
|
|
|
return count($v) === 2 ? $v[1] : $v[0]; |
|
834
|
|
|
}, $select); |
|
835
|
|
|
$sql = "SELECT " . implode(', ', $f) . " |
|
836
|
|
|
FROM ( |
|
837
|
|
|
SELECT tbl__.*, rownum rnum__ FROM ( |
|
838
|
|
|
" . $sql . " |
|
839
|
|
|
) tbl__ |
|
840
|
|
|
WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . " |
|
841
|
|
|
) WHERE rnum__ > " . $this->li_of[1]; |
|
842
|
|
|
} |
|
843
|
|
|
} else { |
|
844
|
1 |
|
$sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1]; |
|
845
|
|
|
} |
|
846
|
|
|
} |
|
847
|
11 |
|
return $this->qiterator = new TableQueryIterator( |
|
848
|
11 |
|
$this->db->get($sql, $par), |
|
849
|
11 |
|
$this->pkey, |
|
850
|
11 |
|
$this->withr, |
|
851
|
11 |
|
$aliases |
|
852
|
|
|
); |
|
853
|
|
|
} |
|
854
|
|
|
/** |
|
855
|
|
|
* Perform the actual fetch |
|
856
|
|
|
* @param array|null $fields optional array of columns to select (related columns can be used too) |
|
857
|
|
|
* @return array the query result as an array |
|
858
|
|
|
*/ |
|
859
|
1 |
|
public function select(array $fields = null) : array |
|
860
|
|
|
{ |
|
861
|
1 |
|
return iterator_to_array($this->iterator($fields)); |
|
862
|
|
|
} |
|
863
|
|
|
/** |
|
864
|
|
|
* Insert a new row in the table |
|
865
|
|
|
* @param array $data key value pairs, where each key is the column name and the value is the value to insert |
|
866
|
|
|
* @return array the inserted ID where keys are column names and values are column values |
|
867
|
|
|
*/ |
|
868
|
1 |
|
public function insert(array $data) : array |
|
869
|
|
|
{ |
|
870
|
1 |
|
$table = $this->definition->getName(); |
|
871
|
1 |
|
$columns = $this->definition->getFullColumns(); |
|
872
|
1 |
|
$insert = []; |
|
873
|
1 |
|
foreach ($data as $column => $value) { |
|
874
|
1 |
|
if (isset($columns[$column])) { |
|
875
|
1 |
|
$insert[$column] = $this->normalizeValue($columns[$column], $value); |
|
876
|
|
|
} |
|
877
|
|
|
} |
|
878
|
1 |
|
if (!count($insert)) { |
|
879
|
|
|
throw new DBException('No valid columns to insert'); |
|
880
|
|
|
} |
|
881
|
1 |
|
$sql = 'INSERT INTO '.$table.' ('.implode(', ', array_keys($insert)).') VALUES (??)'; |
|
882
|
1 |
|
$par = [$insert]; |
|
883
|
1 |
|
$primary = $this->definition->getPrimaryKey(); |
|
884
|
1 |
|
if (!count($primary)) { |
|
885
|
|
|
$this->db->query($sql, $par); |
|
886
|
|
|
return []; |
|
887
|
|
|
} |
|
888
|
1 |
|
if ($this->db->driverName() === 'oracle') { |
|
889
|
|
|
$ret = []; |
|
890
|
|
|
foreach ($primary as $k) { |
|
891
|
|
|
$ret[$k] = str_repeat(' ', 255); |
|
892
|
|
|
$par[] = &$ret[$k]; |
|
893
|
|
|
} |
|
894
|
|
|
$sql .= ' RETURNING ' . implode(',', $primary) . ' INTO ' . implode(',', array_fill(0, count($primary), '?')); |
|
895
|
|
|
$this->db->query($sql, $par); |
|
896
|
|
|
return $ret; |
|
897
|
|
|
} else { |
|
898
|
1 |
|
$ret = []; |
|
899
|
1 |
|
$ins = $this->db->query($sql, $par)->insertID(); |
|
900
|
1 |
|
foreach ($primary as $k) { |
|
901
|
1 |
|
$ret[$k] = $data[$k] ?? $ins; |
|
902
|
|
|
} |
|
903
|
1 |
|
return $ret; |
|
904
|
|
|
} |
|
905
|
|
|
} |
|
906
|
|
|
/** |
|
907
|
|
|
* Update the filtered rows with new data |
|
908
|
|
|
* @param array $data key value pairs, where each key is the column name and the value is the value to insert |
|
909
|
|
|
* @return int the number of affected rows |
|
910
|
|
|
*/ |
|
911
|
1 |
|
public function update(array $data) : int |
|
912
|
|
|
{ |
|
913
|
1 |
|
$table = $this->definition->getName(); |
|
914
|
1 |
|
$columns = $this->definition->getFullColumns(); |
|
915
|
1 |
|
$update = []; |
|
916
|
1 |
|
foreach ($data as $column => $value) { |
|
917
|
1 |
|
if (isset($columns[$column])) { |
|
918
|
1 |
|
$update[$column] = $this->normalizeValue($columns[$column], $value); |
|
919
|
|
|
} |
|
920
|
|
|
} |
|
921
|
1 |
|
if (!count($update)) { |
|
922
|
|
|
throw new DBException('No valid columns to update'); |
|
923
|
|
|
} |
|
924
|
1 |
|
$sql = 'UPDATE '.$table.' SET '; |
|
925
|
1 |
|
$par = []; |
|
926
|
|
|
$sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($update))) . ' '; |
|
927
|
1 |
|
$par = array_merge($par, array_values($update)); |
|
928
|
1 |
|
if (count($this->where)) { |
|
929
|
1 |
|
$sql .= 'WHERE '; |
|
930
|
1 |
|
$tmp = []; |
|
931
|
1 |
|
foreach ($this->where as $v) { |
|
932
|
1 |
|
$tmp[] = $v[0]; |
|
933
|
1 |
|
$par = array_merge($par, $v[1]); |
|
934
|
|
|
} |
|
935
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
936
|
|
|
} |
|
937
|
1 |
|
if (count($this->order)) { |
|
938
|
|
|
$sql .= $this->order[0]; |
|
939
|
|
|
$par = array_merge($par, $this->order[1]); |
|
940
|
|
|
} |
|
941
|
1 |
|
return $this->db->query($sql, $par)->affected(); |
|
942
|
|
|
} |
|
943
|
|
|
/** |
|
944
|
|
|
* Delete the filtered rows from the DB |
|
945
|
|
|
* @return int the number of deleted rows |
|
946
|
|
|
*/ |
|
947
|
1 |
|
public function delete() : int |
|
948
|
|
|
{ |
|
949
|
1 |
|
$table = $this->definition->getName(); |
|
950
|
1 |
|
$sql = 'DELETE FROM '.$table.' '; |
|
951
|
1 |
|
$par = []; |
|
952
|
1 |
|
if (count($this->where)) { |
|
953
|
1 |
|
$sql .= 'WHERE '; |
|
954
|
1 |
|
$tmp = []; |
|
955
|
1 |
|
foreach ($this->where as $v) { |
|
956
|
1 |
|
$tmp[] = $v[0]; |
|
957
|
1 |
|
$par = array_merge($par, $v[1]); |
|
958
|
|
|
} |
|
959
|
1 |
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
960
|
|
|
} |
|
961
|
1 |
|
if (count($this->order)) { |
|
962
|
|
|
$sql .= $this->order[0]; |
|
963
|
|
|
$par = array_merge($par, $this->order[1]); |
|
964
|
|
|
} |
|
965
|
1 |
|
return $this->db->query($sql, $par)->affected(); |
|
966
|
|
|
} |
|
967
|
|
|
/** |
|
968
|
|
|
* Solve the n+1 queries problem by prefetching a relation by name |
|
969
|
|
|
* @param string $relation the relation name to fetch along with the data |
|
970
|
|
|
* @return $this |
|
971
|
|
|
*/ |
|
972
|
4 |
|
public function with(string $relation) : TableQuery |
|
973
|
|
|
{ |
|
974
|
4 |
|
$this->qiterator = null; |
|
975
|
4 |
|
$parts = explode('.', $relation); |
|
976
|
4 |
|
$table = $this->definition; |
|
977
|
4 |
|
array_reduce( |
|
978
|
4 |
|
$parts, |
|
979
|
4 |
|
function ($carry, $item) use (&$table) { |
|
980
|
4 |
|
$relation = $table->getRelation($item); |
|
981
|
4 |
|
if (!$relation) { |
|
982
|
|
|
throw new DBException('Invalid relation name'); |
|
983
|
|
|
} |
|
984
|
4 |
|
$name = $carry ? $carry . static::SEP . $item : $item; |
|
985
|
4 |
|
$this->withr[$name] = [ $relation, $carry ?? $table->getName() ]; |
|
986
|
4 |
|
$table = $relation->table; |
|
987
|
4 |
|
return $name; |
|
988
|
4 |
|
} |
|
989
|
|
|
); |
|
990
|
4 |
|
return $this; |
|
991
|
|
|
} |
|
992
|
|
|
|
|
993
|
1 |
|
public function getIterator() |
|
994
|
|
|
{ |
|
995
|
1 |
|
return $this->iterator(); |
|
996
|
|
|
} |
|
997
|
|
|
|
|
998
|
9 |
|
public function offsetGet($offset) |
|
999
|
|
|
{ |
|
1000
|
9 |
|
return $this->iterator()->offsetGet($offset); |
|
1001
|
|
|
} |
|
1002
|
|
|
public function offsetExists($offset) |
|
1003
|
|
|
{ |
|
1004
|
|
|
return $this->iterator()->offsetExists($offset); |
|
1005
|
|
|
} |
|
1006
|
|
|
public function offsetUnset($offset) |
|
1007
|
|
|
{ |
|
1008
|
|
|
return $this->iterator()->offsetUnset($offset); |
|
1009
|
|
|
} |
|
1010
|
|
|
public function offsetSet($offset, $value) |
|
1011
|
|
|
{ |
|
1012
|
|
|
return $this->iterator()->offsetSet($offset, $value); |
|
1013
|
|
|
} |
|
1014
|
|
|
|
|
1015
|
|
|
public function collection(array $fields = null) : Collection |
|
1016
|
|
|
{ |
|
1017
|
|
|
return new Collection($this->iterator($fields)); |
|
1018
|
|
|
} |
|
1019
|
|
|
|
|
1020
|
|
|
public function ids() |
|
1021
|
|
|
{ |
|
1022
|
|
|
$aliases = []; |
|
1023
|
|
|
$getAlias = function ($name) use (&$aliases) { |
|
1024
|
|
|
// to bypass use: return $name; |
|
|
|
|
|
|
1025
|
|
|
return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases); |
|
1026
|
|
|
}; |
|
1027
|
|
|
$table = $this->definition->getName(); |
|
1028
|
|
|
$sql = 'SELECT DISTINCT '.$table.'.'.implode(', '.$table.'.', $this->pkey).' FROM '.$table.' '; |
|
1029
|
|
|
$par = []; |
|
1030
|
|
|
|
|
1031
|
|
|
$relations = $this->withr; |
|
1032
|
|
|
foreach ($relations as $k => $v) { |
|
1033
|
|
|
$getAlias($k); |
|
1034
|
|
|
} |
|
1035
|
|
|
$f = $this->fields; |
|
|
|
|
|
|
1036
|
|
|
$w = $this->where; |
|
1037
|
|
|
$h = $this->having; |
|
1038
|
|
|
$o = $this->order; |
|
1039
|
|
|
$g = $this->group; |
|
1040
|
|
|
$j = array_map(function ($v) { return clone $v; }, $this->joins); |
|
1041
|
|
|
foreach ($this->definition->getRelations() as $k => $v) { |
|
1042
|
|
|
foreach ($w as $kk => $vv) { |
|
1043
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv[0])) { |
|
1044
|
|
|
$relations[$k] = [ $v, $table ]; |
|
1045
|
|
|
$w[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv[0]); |
|
1046
|
|
|
} |
|
1047
|
|
|
} |
|
1048
|
|
|
if (isset($o[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $o[0])) { |
|
1049
|
|
|
$relations[$k] = [ $v, $table ]; |
|
1050
|
|
|
$o[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $o[0]); |
|
1051
|
|
|
} |
|
1052
|
|
|
foreach ($h as $kk => $vv) { |
|
1053
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv[0])) { |
|
1054
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
1055
|
|
|
$h[$kk][0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv[0]); |
|
1056
|
|
|
} |
|
1057
|
|
|
} |
|
1058
|
|
|
if (isset($g[0]) && preg_match('(\b'.preg_quote($k . '.'). ')i', $g[0])) { |
|
1059
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
1060
|
|
|
$g[0] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $g[0]); |
|
1061
|
|
|
} |
|
1062
|
|
|
foreach ($j as $kk => $v) { |
|
1063
|
|
|
foreach ($v->keymap as $kkk => $vv) { |
|
1064
|
|
|
if (preg_match('(\b'.preg_quote($k . '.'). ')i', $vv)) { |
|
1065
|
|
|
$relations[$k] = [ $relation, $table ]; |
|
|
|
|
|
|
1066
|
|
|
$j[$k]->keymap[$kkk] = preg_replace('(\b'.preg_quote($k . '.'). ')i', $getAlias($k) . '.', $vv); |
|
1067
|
|
|
} |
|
1068
|
|
|
} |
|
1069
|
|
|
} |
|
1070
|
|
|
} |
|
1071
|
|
|
|
|
1072
|
|
|
foreach ($j as $k => $v) { |
|
1073
|
|
|
$sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON '; |
|
1074
|
|
|
$tmp = []; |
|
1075
|
|
|
foreach ($v->keymap as $kk => $vv) { |
|
1076
|
|
|
$tmp[] = $kk.' = '.$vv; |
|
1077
|
|
|
} |
|
1078
|
|
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
1079
|
|
|
} |
|
1080
|
|
|
foreach ($relations as $k => $v) { |
|
1081
|
|
|
$table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1]; |
|
1082
|
|
|
$v = $v[0]; |
|
1083
|
|
|
if ($v->pivot) { |
|
1084
|
|
|
$alias = $getAlias($k.'_pivot'); |
|
1085
|
|
|
$sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON '; |
|
1086
|
|
|
$tmp = []; |
|
1087
|
|
|
foreach ($v->keymap as $kk => $vv) { |
|
1088
|
|
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
1089
|
|
|
} |
|
1090
|
|
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
1091
|
|
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($k).' ON '; |
|
1092
|
|
|
$tmp = []; |
|
1093
|
|
|
foreach ($v->pivot_keymap as $kk => $vv) { |
|
1094
|
|
|
$tmp[] = $getAlias($k).'.'.$vv.' = '.$alias.'.'.$kk.' '; |
|
1095
|
|
|
} |
|
1096
|
|
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
1097
|
|
|
} else { |
|
1098
|
|
|
$alias = $getAlias($k); |
|
1099
|
|
|
$sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON '; |
|
1100
|
|
|
$tmp = []; |
|
1101
|
|
|
foreach ($v->keymap as $kk => $vv) { |
|
1102
|
|
|
$tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' '; |
|
1103
|
|
|
} |
|
1104
|
|
|
if ($v->sql) { |
|
1105
|
|
|
$tmp[] = $v->sql . ' '; |
|
1106
|
|
|
$par = array_merge($par, $v->par ?? []); |
|
1107
|
|
|
} |
|
1108
|
|
|
$sql .= implode(' AND ', $tmp) . ' '; |
|
1109
|
|
|
} |
|
1110
|
|
|
} |
|
1111
|
|
|
if (count($w)) { |
|
1112
|
|
|
$sql .= 'WHERE '; |
|
1113
|
|
|
$tmp = []; |
|
1114
|
|
|
foreach ($w as $v) { |
|
1115
|
|
|
$tmp[] = '(' . $v[0] . ')'; |
|
1116
|
|
|
$par = array_merge($par, $v[1]); |
|
1117
|
|
|
} |
|
1118
|
|
|
$sql .= implode(' AND ', $tmp).' '; |
|
1119
|
|
|
} |
|
1120
|
|
|
if (count($g)) { |
|
1121
|
|
|
$sql .= 'GROUP BY ' . $g[0] . ' '; |
|
1122
|
|
|
$par = array_merge($par, $g[1]); |
|
1123
|
|
|
} |
|
1124
|
|
|
if (count($h)) { |
|
1125
|
|
|
$sql .= 'HAVING '; |
|
1126
|
|
|
$tmp = []; |
|
1127
|
|
|
foreach ($h as $v) { |
|
1128
|
|
|
$tmp[] = '(' . $v[0] . ')'; |
|
1129
|
|
|
$par = array_merge($par, $v[1]); |
|
1130
|
|
|
} |
|
1131
|
|
|
$sql .= implode(' AND ', $tmp).' '; |
|
1132
|
|
|
} |
|
1133
|
|
|
if (count($o)) { |
|
1134
|
|
|
$sql .= 'ORDER BY ' . $o[0] . ' '; |
|
1135
|
|
|
$par = array_merge($par, $o[1]); |
|
1136
|
|
|
} |
|
1137
|
|
|
$porder = []; |
|
1138
|
|
|
foreach ($this->definition->getPrimaryKey() as $field) { |
|
1139
|
|
|
$porder[] = $this->getColumn($field)['name']; |
|
1140
|
|
|
} |
|
1141
|
|
|
if (count($porder)) { |
|
1142
|
|
|
$sql .= (count($o) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' '; |
|
1143
|
|
|
} |
|
1144
|
|
|
|
|
1145
|
|
|
if ($this->li_of[0]) { |
|
1146
|
|
|
if ($this->db->driverName() === 'oracle') { |
|
1147
|
|
|
if ((int)$this->db->driverOption('version', 0) >= 12) { |
|
1148
|
|
|
$sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY'; |
|
1149
|
|
|
} else { |
|
1150
|
|
|
$f = array_map(function ($v) { |
|
1151
|
|
|
$v = explode(' ', trim($v), 2); |
|
1152
|
|
|
if (count($v) === 2) { return $v[1]; } |
|
1153
|
|
|
$v = explode('.', $v[0], 2); |
|
1154
|
|
|
return count($v) === 2 ? $v[1] : $v[0]; |
|
1155
|
|
|
}, $select); |
|
|
|
|
|
|
1156
|
|
|
$sql = "SELECT " . implode(', ', $f) . " |
|
1157
|
|
|
FROM ( |
|
1158
|
|
|
SELECT tbl__.*, rownum rnum__ FROM ( |
|
1159
|
|
|
" . $sql . " |
|
1160
|
|
|
) tbl__ |
|
1161
|
|
|
WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . " |
|
1162
|
|
|
) WHERE rnum__ > " . $this->li_of[1]; |
|
1163
|
|
|
} |
|
1164
|
|
|
} else { |
|
1165
|
|
|
$sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1]; |
|
1166
|
|
|
} |
|
1167
|
|
|
} |
|
1168
|
|
|
return $this->db->all($sql, $par); |
|
1169
|
|
|
} |
|
1170
|
|
|
} |
|
1171
|
|
|
|
vakata /
database
Loading history...
Sometimes obsolete code just ends up commented out instead of removed. In this case it is better to remove the code once you have checked you do not need it.
The code might also have been commented out for debugging purposes. In this case it is vital that someone uncomments it again or your project may behave in very unexpected ways in production.
This check looks for comments that seem to be mostly valid code and reports them.