Completed
Push — master ( 6931a9...0392f4 )
by Ivan
01:48
created

TableQuery::getAlias()   A

Complexity

Conditions 1
Paths 1

Size

Total Lines 5
Code Lines 2

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 2
CRAP Score 1

Importance

Changes 0
Metric Value
dl 0
loc 5
ccs 2
cts 2
cp 1
rs 9.4285
c 0
b 0
f 0
cc 1
eloc 2
nc 1
nop 1
crap 1
1
<?php
2
namespace vakata\database\schema;
3
4
use vakata\database\DBInterface;
5
use vakata\database\DBException;
6
use vakata\database\ResultInterface;
7
8
/**
9
 * A database query class
10
 */
11
class TableQuery implements \IteratorAggregate, \ArrayAccess, \Countable
12
{
13
    const SEP = '___';
14
    /**
15
     * @var DBInterface
16
     */
17
    protected $db;
18
    /**
19
     * @var Table
20
     */
21
    protected $definition;
22
    /**
23
     * @var TableQueryIterator|null
24
     */
25
    protected $qiterator;
26
27
    /**
28
     * @var array
29
     */
30
    protected $where = [];
31
    /**
32
     * @var array
33
     */
34
    protected $order = [];
35
    /**
36
     * @var array
37
     */
38
    protected $group = [];
39
    /**
40
     * @var array
41
     */
42
    protected $having = [];
43
    /**
44
     * @var int[]
45
     */
46
    protected $li_of = [0,0];
47
    /**
48
     * @var array
49
     */
50
    protected $fields = [];
51
    /**
52
     * @var array
53
     */
54
    protected $withr = [];
55
    /**
56
     * @var array
57
     */
58
    protected $joins = [];
59
    /**
60
     * @var array
61
     */
62
    protected $pkey = [];
63
    /**
64
     * @var array
65
     */
66
    protected $aliases = [];
67
68
    /**
69
     * Create an instance
70
     * @param  DBInterface    $db         the database connection
71
     * @param  Table|string   $table      the name or definition of the main table in the query
72
     */
73 13
    public function __construct(DBInterface $db, $table)
74
    {
75 13
        $this->db = $db;
76 13
        $this->definition = $table instanceof Table ? $table : $this->db->definition((string)$table);
77 13
        $primary = $this->definition->getPrimaryKey();
78 13
        $columns = $this->definition->getColumns();
79 13
        $this->pkey = count($primary) ? $primary : $columns;
80 13
        $this->columns($columns);
81 13
    }
82
    public function __clone()
83
    {
84
        $this->reset();
85
    }
86
    /**
87
     * Get the table definition of the queried table
88
     * @return Table        the definition
89
     */
90
    public function getDefinition() : Table
91
    {
92
        return $this->definition;
93
    }
94
95 13
    protected function getColumn($column)
96
    {
97 13
        $column = explode('.', $column);
98 13
        if (count($column) === 1) {
99 13
            $column = [ $this->definition->getName(), $column[0] ];
100 13
            $col = $this->definition->getColumn($column[1]);
101 13
            if (!$col) {
102 13
                throw new DBException('Invalid column name in own table');
103
            }
104 2
        } elseif (count($column) === 2) {
105 2
            if ($column[0] === $this->definition->getName()) {
106
                $col = $this->definition->getColumn($column[1]);
107
                if (!$col) {
108
                    throw new DBException('Invalid column name in own table');
109
                }
110
            } else {
111 2
                if ($this->definition->hasRelation($column[0])) {
112 1
                    $col = $this->definition->getRelation($column[0])->table->getColumn($column[1]);
113 1
                    if (!$col) {
114 1
                        throw new DBException('Invalid column name in related table');
115
                    }
116 1
                } else if (isset($this->joins[$column[0]])) {
117 1
                    $col = $this->joins[$column[0]]->table->getColumn($column[1]);
118 1
                    if (!$col) {
119 1
                        throw new DBException('Invalid column name in related table');
120
                    }
121
                } else {
122 2
                    throw new DBException('Invalid foreign table name: ' . implode(',', $column));
123
                }
124
            }
125
        } else {
126
            $name = array_pop($column);
127
            $this->with(implode('.', $column));
128
            $table = $this->definition;
129
            $table = array_reduce(
130
                $column,
131
                function ($carry, $item) use (&$table) {
132
                    $table = $table->getRelation($item)->table;
133
                    return $table;
134
                }
135
            );
136
            $col = $table->getColumn($name);
137
            $column = [ implode(static::SEP, $column), $name ];
138
        }
139 13
        return [ 'name' => implode('.', $column), 'data' => $col ];
140
    }
141 4
    protected function normalizeValue(TableColumn $col, $value)
142
    {
143 4
        if ($value === null && $col->isNullable()) {
144
            return null;
145
        }
146 4
        switch ($col->getBasicType()) {
147 4
            case 'date':
148
                if (is_string($value)) {
149
                    $temp = strtotime($value);
150
                    if (!$temp) {
151
                        return null;
152
                    }
153
                    return date('Y-m-d', strtotime($value));
154
                }
155
                if (is_int($value)) {
156
                    return date('Y-m-d', $value);
157
                }
158
                if ($value instanceof \DateTime) {
159
                    return $value->format('Y-m-d');
160
                }
161
                return $value;
162 4
            case 'datetime':
163
                if (is_string($value)) {
164
                    $temp = strtotime($value);
165
                    if (!$temp) {
166
                        return null;
167
                    }
168
                    return date('Y-m-d H:i:s', strtotime($value));
169
                }
170
                if (is_int($value)) {
171
                    return date('Y-m-d H:i:s', $value);
172
                }
173
                if ($value instanceof \DateTime) {
174
                    return $value->format('Y-m-d H:i:s');
175
                }
176
                return $value;
177 4
            case 'enum':
178
                if (is_int($value)) {
179
                    return $value;
180
                }
181
                if (!in_array($value, $col->getValues())) {
182
                    return 0;
183
                }
184
                return $value;
185 4
            case 'int':
186 2
                return (int)$value;
187
            default:
188 3
                return $value;
189
        }
190
    }
191
192
    /**
193
     * Filter the results by a column and a value
194
     * @param  string $column  the column name to filter by (related columns can be used - for example: author.name)
195
     * @param  mixed  $value   a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3])
196
     * @param  bool   $negate  optional boolean indicating that the filter should be negated
197
     * @return $this
198
     */
199 2
    public function filter(string $column, $value, bool $negate = false) : TableQuery
200
    {
201 2
        list($name, $column) = array_values($this->getColumn($column));
202 2
        if (is_null($value)) {
203
            return $negate ?
204
                $this->where($name . ' IS NOT NULL') :
205
                $this->where($name . ' IS NULL');
206
        }
207 2
        if (!is_array($value)) {
208 2
            return $negate ?
209
                $this->where(
210
                    $name . ' <> ?',
211
                    [ $this->normalizeValue($column, $value) ]
212
                ) :
213 2
                $this->where(
214 2
                    $name . ' = ?',
215 2
                    [ $this->normalizeValue($column, $value) ]
216
                );
217
        }
218 1
        if (isset($value['beg']) && isset($value['end'])) {
219
            return $negate ?
220
                $this->where(
221
                    $name.' NOT BETWEEN ? AND ?',
222
                    [
223
                        $this->normalizeValue($column, $value['beg']),
224
                        $this->normalizeValue($column, $value['end'])
225
                    ]
226
                ) :
227
                $this->where(
228
                    $name.' BETWEEN ? AND ?',
229
                    [
230
                        $this->normalizeValue($column, $value['beg']),
231
                        $this->normalizeValue($column, $value['end'])
232
                    ]
233
                );
234
        }
235 1
        if (isset($value['gt']) || isset($value['lt']) || isset($value['gte']) || isset($value['lte'])) {
236 1
            if (isset($value['gt'])) {
237 1
                $this->where(
238 1
                    $name. ' ' . ($negate ? '<=' : '>') . ' ?',
239 1
                    [ $this->normalizeValue($column, $value['gt']) ]
240
                );
241
            }
242 1
            if (isset($value['gte'])) {
243 1
                $this->where(
244 1
                    $name. ' ' . ($negate ? '<' : '>=') . ' ?',
245 1
                    [ $this->normalizeValue($column, $value['gte']) ]
246
                );
247
            }
248 1
            if (isset($value['lt'])) {
249 1
                $this->where(
250 1
                    $name. ' ' . ($negate ? '>=' : '<') . ' ?',
251 1
                    [ $this->normalizeValue($column, $value['lt']) ]
252
                );
253
            }
254 1
            if (isset($value['lte'])) {
255 1
                $this->where(
256 1
                    $name. ' ' . ($negate ? '>' : '<=') . ' ?',
257 1
                    [ $this->normalizeValue($column, $value['lte']) ]
258
                );
259
            }
260 1
            return $this;
261
        }
262
        return $negate ?
263
            $this->where(
264
                $name . ' NOT IN (??)',
265
                [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ]
266
            ) :
267
            $this->where(
268
                $name . ' IN (??)',
269
                [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ]
270
            );
271
    }
272
    /**
273
     * Sort by a column
274
     * @param  string       $column the column name to sort by (related columns can be used - for example: author.name)
275
     * @param  bool|boolean $desc   should the sorting be in descending order, defaults to `false`
276
     * @return $this
277
     */
278
    public function sort(string $column, bool $desc = false) : TableQuery
279
    {
280
        return $this->order($this->getColumn($column)['name'] . ' ' . ($desc ? 'DESC' : 'ASC'));
281
    }
282
    /**
283
     * Group by a column (or columns)
284
     * @param  string|array        $column the column name (or names) to group by
285
     * @return $this
286
     */
287 1
    public function group($column) : TableQuery
288
    {
289 1
        if (!is_array($column)) {
290 1
            $column = [ $column ];
291
        }
292 1
        foreach ($column as $k => $v) {
293 1
            $column[$k] = $this->getColumn($v)['name'];
294
        }
295 1
        return $this->groupBy(implode(', ', $column), []);
296
    }
297
    /**
298
     * Get a part of the data
299
     * @param  int|integer $page    the page number to get (1-based), defaults to 1
300
     * @param  int|integer $perPage the number of records per page - defaults to 25
301
     * @return $this
302
     */
303
    public function paginate(int $page = 1, int $perPage = 25) : TableQuery
304
    {
305
        return $this->limit($perPage, ($page - 1) * $perPage);
306
    }
307 1
    public function __call($name, $data)
308
    {
309 1
        if (strpos($name, 'filterBy') === 0) {
310
            return $this->filter(strtolower(substr($name, 8)), $data[0]);
311
        }
312 1
        if (strpos($name, 'sortBy') === 0) {
313
            return $this->sort(strtolower(substr($name, 6)), $data[0]);
314
        }
315 1
        if (strpos($name, 'groupBy') === 0) {
316 1
            return $this->group(strtolower(substr($name, 7)));
317
        }
318
    }
319
    /**
320
     * Remove all filters, sorting, etc
321
     * @return $this
322
     */
323
    public function reset() : TableQuery
324
    {
325
        $this->where = [];
326
        $this->joins = [];
327
        $this->group = [];
328
        $this->withr = [];
329
        $this->order = [];
330
        $this->having = [];
331
        $this->aliases = [];
332
        $this->li_of = [0,0];
333
        $this->qiterator = null;
334
        return $this;
335
    }
336
    /**
337
     * Apply advanced grouping
338
     * @param  string $sql    SQL statement to use in the GROUP BY clause
339
     * @param  array  $params optional params for the statement (defaults to an empty array)
340
     * @return $this
341
     */
342 1
    public function groupBy(string $sql, array $params = []) : TableQuery
343
    {
344 1
        $this->qiterator = null;
345 1
        $this->group = [ $sql, $params ];
346 1
        return $this;
347
    }
348
    /**
349
     * Join a table to the query (no need to do this for relations defined with foreign keys)
350
     * @param  Table|string $table     the table to join
351
     * @param  array        $fields    what to join on (joined_table_field => other_field) 
352
     * @param  string|null  $name      alias for the join, defaults to the table name 
353
     * @param  bool         $multiple  are multiple rows joined (results in a LEFT JOIN), default to true 
354
     * @return $this
355
     */
356 1
    public function join($table, array $fields, string $name = null, bool $multiple = true)
357
    {
358 1
        $table = $table instanceof Table ? $table : $this->db->definition((string)$table);
359 1
        $name = $name ?? $table->getName();
360 1
        if (isset($this->joins[$name]) || $this->definition->hasRelation($name)) {
361
            throw new DBException('Alias / table name already in use');
362
        }
363 1
        $this->joins[$name] = new TableRelation($name, $table, [], $multiple);
364 1
        foreach ($fields as $k => $v) {
365 1
            $k = explode('.', $k, 2);
366 1
            $k = count($k) == 2 ? $k[1] : $k[0];
367 1
            $this->joins[$name]->keymap[$this->getColumn($name . '.' . $k)['name']] = $this->getColumn($v)['name'];
368
        }
369 1
        return $this;
370
    }
371
    /**
372
     * Apply an advanced filter (can be called multiple times)
373
     * @param  string $sql    SQL statement to be used in the where clause
374
     * @param  array  $params parameters for the SQL statement (defaults to an empty array)
375
     * @return $this
376
     */
377 3
    public function where(string $sql, array $params = []) : TableQuery
378
    {
379 3
        $this->qiterator = null;
380 3
        $this->where[] = [ $sql, $params ];
381 3
        return $this;
382
    }
383
    /**
384
     * Apply an advanced HAVING filter (can be called multiple times)
385
     * @param  string $sql    SQL statement to be used in the HAING clause
386
     * @param  array  $params parameters for the SQL statement (defaults to an empty array)
387
     * @return $this
388
     */
389 1
    public function having(string $sql, array $params = []) : TableQuery
390
    {
391 1
        $this->qiterator = null;
392 1
        $this->having[] = [ $sql, $params ];
393 1
        return $this;
394
    }
395
    /**
396
     * Apply advanced sorting
397
     * @param  string $sql    SQL statement to use in the ORDER clause
398
     * @param  array  $params optional params for the statement (defaults to an empty array)
399
     * @return $this
400
     */
401 1
    public function order(string $sql, array $params = []) : TableQuery
402
    {
403 1
        $this->qiterator = null;
404 1
        $this->order = [ $sql, $params ];
405 1
        return $this;
406
    }
407
    /**
408
     * Apply an advanced limit
409
     * @param  int         $limit  number of rows to return
410
     * @param  int         $offset number of rows to skip from the beginning (defaults to 0)
411
     * @return $this
412
     */
413 1
    public function limit(int $limit, int $offset = 0) : TableQuery
414
    {
415 1
        $this->qiterator = null;
416 1
        $this->li_of = [ $limit, $offset ];
417 1
        return $this;
418
    }
419
    /**
420
     * Get the number of records
421
     * @return int the total number of records (does not respect pagination)
422
     */
423 3
    public function count() : int
424
    {
425 3
        $aliases = [];
426 3
        $getAlias = function ($name) use (&$aliases) {
427
            // to bypass use: return $name;
0 ignored issues
show
Unused Code Comprehensibility introduced by
42% of this comment could be valid code. Did you maybe forget this after debugging?

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.

Loading history...
428 1
            return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases);
429 3
        };
430 3
        $table = $this->definition->getName();
431 3
        $sql = 'SELECT COUNT(DISTINCT '.$table.'.'.implode(', '.$table.'.', $this->pkey).') FROM '.$table.' ';
432 3
        $par = [];
433
        
434 3
        $relations = $this->withr;
435 3
        foreach ($relations as $k => $v) {
436
            $getAlias($k);
437
        }
438 3
        $f = $this->fields;
0 ignored issues
show
Unused Code introduced by
$f is not used, you could remove the assignment.

This check looks for variable assignements that are either overwritten by other assignments or where the variable is not used subsequently.

$myVar = 'Value';
$higher = false;

if (rand(1, 6) > 3) {
    $higher = true;
} else {
    $higher = false;
}

Both the $myVar assignment in line 1 and the $higher assignment in line 2 are dead. The first because $myVar is never used and the second because $higher is always overwritten for every possible time line.

Loading history...
439 3
        $w = $this->where;
440 3
        $h = $this->having;
441 3
        $o = $this->order;
442 3
        $g = $this->group;
443
        $j = array_map(function ($v) { return clone $v; }, $this->joins);
444 3
        foreach ($this->definition->getRelations() as $k => $v) {
445 3
            foreach ($w as $kk => $vv) {
446 1
                if (strpos($vv[0], $k . '.') !== false) {
447 1
                    $relations[$k] = [ $v, $table ];
448 1
                    $w[$kk][0] = str_replace($k . '.', $getAlias($k) . '.', $vv[0]);
449
                }
450
            }
451 3
            if (isset($o[0]) && strpos($o[0], $k . '.') !== false) {
452
                $relations[$k] = [ $v, $table ];
453
            }
454 3
            foreach ($h as $kk => $vv) {
455
                if (strpos($vv[0], $k . '.') !== false) {
456
                    $relations[$k] = [ $relation, $table ];
0 ignored issues
show
Bug introduced by
The variable $relation does not exist. Did you mean $relations?

This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name.

The variable may have been renamed without also renaming all references.

Loading history...
457
                    $h[$kk][0] = str_replace($k . '.', $getAlias($k) . '.', $vv[0]);
458
                }
459
            }
460 3
            if (isset($g[0]) && strpos($g[0], $k . '.') !== false) {
461
                $relations[$k] = [ $relation, $table ];
0 ignored issues
show
Bug introduced by
The variable $relation does not exist. Did you mean $relations?

This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name.

The variable may have been renamed without also renaming all references.

Loading history...
462
                $g[0] = str_replace($k . '.', $getAlias($k) . '.', $g[0]);
463
            }
464 3
            foreach ($j as $kk => $v) {
465
                foreach ($v->keymap as $kkk => $vv) {
466
                    if (strpos($vv, $k . '.') !== false) {
467
                        $relations[$k] = [ $relation, $table ];
0 ignored issues
show
Bug introduced by
The variable $relation does not exist. Did you mean $relations?

This check looks for variables that are accessed but have not been defined. It raises an issue if it finds another variable that has a similar name.

The variable may have been renamed without also renaming all references.

Loading history...
468 3
                        $j[$k]->keymap[$kkk] = str_replace($k . '.', $getAlias($k) . '.', $vv);
469
                    }
470
                }
471
            }
472
        }
473
474 3
        foreach ($j as $k => $v) {
475
            $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON ';
476
            $tmp = [];
477
            foreach ($v->keymap as $kk => $vv) {
478
                $tmp[] = $kk.' = '.$vv;
479
            }
480
            $sql .= implode(' AND ', $tmp) . ' ';
481
        }
482 3
        foreach ($relations as $k => $v) {
483 1
            $table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1];
484 1
            $v = $v[0];
485 1
            if ($v->pivot) {
486 1
                $alias = $getAlias($k.'_pivot');
487 1
                $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON ';
488 1
                $tmp = [];
489 1
                foreach ($v->keymap as $kk => $vv) {
490 1
                    $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' ';
491
                }
492 1
                $sql .= implode(' AND ', $tmp) . ' ';
493 1
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($k).' ON ';
494 1
                $tmp = [];
495 1
                foreach ($v->pivot_keymap as $kk => $vv) {
496 1
                    $tmp[] = $getAlias($k).'.'.$vv.' = '.$alias.'.'.$kk.' ';
497
                }
498 1
                $sql .= implode(' AND ', $tmp) . ' ';
499
            } else {
500 1
                $alias = $getAlias($k);
501 1
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON ';
502 1
                $tmp = [];
503 1
                foreach ($v->keymap as $kk => $vv) {
504 1
                    $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' ';
505
                }
506 1
                if ($v->sql) {
507
                    $tmp[] = $v->sql . ' ';
508
                    $par = array_merge($par, $v->par ?? []);
509
                }
510 1
                $sql .= implode(' AND ', $tmp) . ' ';
511
            }
512
        }
513 3
        if (count($w)) {
514 1
            $sql .= 'WHERE ';
515 1
            $tmp = [];
516 1
            foreach ($w as $v) {
517 1
                $tmp[] = '(' . $v[0] . ')';
518 1
                $par = array_merge($par, $v[1]);
519
            }
520 1
            $sql .= implode(' AND ', $tmp).' ';
521
        }
522 3
        if (count($g)) {
523
            $sql .= 'GROUP BY ' . $g[0] . ' ';
524
            $par = array_merge($par, $g[1]);
525
        }
526 3
        if (count($h)) {
527
            $sql .= 'HAVING ';
528
            $tmp = [];
529
            foreach ($h as $v) {
530
                $tmp[] = '(' . $v[0] . ')';
531
                $par = array_merge($par, $v[1]);
532
            }
533
            $sql .= implode(' AND ', $tmp).' ';
534
        }
535 3
        return $this->db->one($sql, $par);
536
    }
537
    /**
538
     * Specify which columns to fetch (be default all table columns are fetched)
539
     * @param  array $fields optional array of columns to select (related columns can be used too)
540
     * @return $this
541
     */
542 13
    public function columns(array $fields) : TableQuery
543
    {
544 13
        foreach ($fields as $k => $v) {
545 13
            if (strpos($v, '*') !== false) {
546
                $temp = explode('.', $v);
547
                if (count($temp) === 1) {
548
                    $table = $this->definition->getName();
549
                    $cols = $this->definition->getColumns();
550
                } else if (count($temp) === 2) {
551
                    $table = $temp[0];
552
                    if ($this->definition->hasRelation($table)) {
553
                        $cols = $this->definition->getRelation($table)->table->getColumns();
554
                    } else if (isset($this->joins[$table])) {
555
                        $cols = $this->joins[$table]->table->getColumns();
556
                    } else {
557
                        throw new DBException('Invalid foreign table name');
558
                    }
559
                } else {
560
                    array_pop($temp);
561
                    $this->with(implode('.', $temp));
562
                    $table = array_reduce(
563
                        $temp,
564
                        function ($carry, $item) use (&$table) {
565
                            return $table->getRelation($item)->table;
566
                        }
567
                    );
568
                    $cols = $table->getColumns();
569
                    $table = implode(static::SEP, $temp);
570
                }
571
                unset($fields[$k]);
572
                foreach ($cols as $col) {
573 13
                    $fields[] = $table . '.' . $col;
574
                }
575
            }
576
        }
577 13
        $primary = $this->definition->getPrimaryKey();
578 13
        foreach ($fields as $k => $v) {
579
            try {
580 13
                $fields[$k] = $this->getColumn($v)['name'];
581 1
            } catch (DBException $e) {
582 13
                $fields[$k] = $v;
583
            }
584
        }
585 13
        foreach ($primary as $field) {
586 13
            $field = $this->getColumn($field)['name'];
587 13
            if (!in_array($field, $fields)) {
588 13
                $fields[] = $field;
589
            }
590
        }
591 13
        $this->fields = $fields;
592 13
        return $this;
593
    }
594
    /**
595
     * Perform the actual fetch
596
     * @param  array|null $fields optional array of columns to select (related columns can be used too)
597
     * @return TableQueryIterator               the query result as an iterator
598
     */
599 11
    public function iterator(array $fields = null) : TableQueryIterator
600
    {
601 11
        if ($this->qiterator) {
602 5
            return $this->qiterator;
603
        }
604 11
        $aliases = [];
605 11
        $getAlias = function ($name) use (&$aliases) {
606
            // to bypass use: return $name;
0 ignored issues
show
Unused Code Comprehensibility introduced by
42% of this comment could be valid code. Did you maybe forget this after debugging?

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.

Loading history...
607 4
            return $aliases[$name] = $aliases[$name] ?? 'alias' . static::SEP . count($aliases);
608 11
        };
609 11
        $table = $this->definition->getName();
610 11
        if ($fields !== null) {
611 1
            $this->columns($fields);
612
        }
613 11
        $relations = $this->withr;
614 11
        foreach ($relations as $k => $v) {
615 4
            $getAlias($k);
616
        }
617
618 11
        $f = $this->fields;
619 11
        $w = $this->where;
620 11
        $h = $this->having;
621 11
        $o = $this->order;
622 11
        $g = $this->group;
623
        $j = array_map(function ($v) { return clone $v; }, $this->joins);
624 11
        foreach ($this->definition->getRelations() as $k => $relation) {
625 11
            foreach ($f as $kk => $field) {
626 11
                if (strpos($field, $k . '.') === 0) {
627
                    $relations[$k] = [ $relation, $table ];
628 11
                    $f[$kk] = str_replace($k . '.', $getAlias($k) . '.', $field);
629
                }
630
            }
631 11
            foreach ($w as $kk => $v) {
632
                if (strpos($v[0], $k . '.') !== false) {
633
                    $relations[$k] = [ $relation, $table ];
634
                    $w[$kk][0] = str_replace($k . '.', $getAlias($k) . '.', $v[0]);
635
                }
636
            }
637 11
            foreach ($h as $kk => $v) {
638 1
                if (strpos($v[0], $k . '.') !== false) {
639
                    $relations[$k] = [ $relation, $table ];
640 1
                    $h[$kk][0] = str_replace($k . '.', $getAlias($k) . '.', $v[0]);
641
                }
642
            }
643 11
            if (isset($o[0]) && strpos($o[0], $k . '.') !== false) {
644
                $relations[$k] = [ $relation, $table ];
645
                $o[0] = str_replace($k . '.', $getAlias($k) . '.', $o[0]);
646
            }
647 11
            if (isset($g[0]) && strpos($g[0], $k . '.') !== false) {
648
                $relations[$k] = [ $relation, $table ];
649
                $g[0] = str_replace($k . '.', $getAlias($k) . '.', $g[0]);
650
            }
651 11
            foreach ($j as $kk => $v) {
652 1
                foreach ($v->keymap as $kkk => $vv) {
653 1
                    if (strpos($vv, $k . '.') !== false) {
654
                        $relations[$k] = [ $relation, $table ];
655 11
                        $j[$k]->keymap[$kkk] = str_replace($k . '.', $getAlias($k) . '.', $vv);
656
                    }
657
                }
658
            }
659
        }
660 11
        $select = [];
661 11
        foreach ($f as $k => $field) {
662 11
            $select[] = $field . (!is_numeric($k) ? ' ' . $k : '');
663
        }
664 11
        foreach ($this->withr as $name => $relation) {
665 4
            foreach ($relation[0]->table->getColumns() as $column) {
666 4
                $select[] = $getAlias($name) . '.' . $column . ' ' . $getAlias($name . static::SEP . $column);
667
            }
668
        }
669 11
        $sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' ';
670 11
        $par = [];
671 11
        foreach ($j as $k => $v) {
672 1
            $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON ';
673 1
            $tmp = [];
674 1
            foreach ($v->keymap as $kk => $vv) {
675 1
                $tmp[] = $kk.' = '.$vv;
676
            }
677 1
            $sql .= implode(' AND ', $tmp) . ' ';
678
        }
679 11
        foreach ($relations as $relation => $v) {
680 4
            $table = $v[1] !== $this->definition->getName() ? $getAlias($v[1]) : $v[1];
681 4
            $v = $v[0];
682 4
            if ($v->pivot) {
683 3
                $alias = $getAlias($relation.'_pivot');
684 3
                $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$alias.' ON ';
685 3
                $tmp = [];
686 3
                foreach ($v->keymap as $kk => $vv) {
687 3
                    $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' ';
688
                }
689 3
                $sql .= implode(' AND ', $tmp) . ' ';
690 3
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$getAlias($relation).' ON ';
691 3
                $tmp = [];
692 3
                foreach ($v->pivot_keymap as $kk => $vv) {
693 3
                    $tmp[] = $getAlias($relation).'.'.$vv.' = '.$alias.'.'.$kk.' ';
694
                }
695 3
                $sql .= implode(' AND ', $tmp) . ' ';
696
            } else {
697 4
                $alias = $getAlias($relation);
698
699 4
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$alias.' ON ';
700 4
                $tmp = [];
701 4
                foreach ($v->keymap as $kk => $vv) {
702 4
                    $tmp[] = $table.'.'.$kk.' = '.$alias.'.'.$vv.' ';
703
                }
704 4
                if ($v->sql) {
705
                    $tmp[] = $v->sql . ' ';
706
                    $par = array_merge($par, $v->par ?? []);
707
                }
708 4
                $sql .= implode(' AND ', $tmp) . ' ';
709
            }
710
        }
711 11
        if (count($w)) {
712
            $sql .= 'WHERE ';
713
            $tmp = [];
714
            foreach ($w as $v) {
715
                $tmp[] = '(' . $v[0] . ')';
716
                $par = array_merge($par, $v[1]);
717
            }
718
            $sql .= implode(' AND ', $tmp).' ';
719
        }
720 11
        if (count($g)) {
721 1
            $sql .= 'GROUP BY ' . $g[0] . ' ';
722 1
            $par = array_merge($par, $g[1]);
723
        }
724 11
        if (count($h)) {
725 1
            $sql .= 'HAVING ';
726 1
            $tmp = [];
727 1
            foreach ($h as $v) {
728 1
                $tmp[] = '(' . $v[0] . ')';
729 1
                $par = array_merge($par, $v[1]);
730
            }
731 1
            $sql .= implode(' AND ', $tmp).' ';
732
        }
733 11
        if (count($o)) {
734 1
            $sql .= 'ORDER BY ' . $o[0] . ' ';
735 1
            $par = array_merge($par, $o[1]);
736
        }
737 11
        $porder = [];
738 11
        foreach ($this->definition->getPrimaryKey() as $field) {
739 11
            $porder[] = $this->getColumn($field)['name'];
740
        }
741 11
        if (count($porder)) {
742 11
            $sql .= (count($o) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' ';
743
        }
744
745 11
        if ($this->li_of[0]) {
746 1
            if ($this->db->driverName() === 'oracle') {
747
                if ((int)$this->db->driverOption('version', 0) >= 12) {
748
                    $sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY';
749
                } else {
750
                    $f = array_map(function ($v) {
751
                        $v = explode(' ', trim($v), 2);
752
                        if (count($v) === 2) { return $v[1]; }
753
                        $v = explode('.', $v[0], 2);
754
                        return count($v) === 2 ? $v[1] : $v[0];
755
                    }, $select);
756
                    $sql = "SELECT " . implode(', ', $f) . " 
757
                            FROM (
758
                                SELECT tbl__.*, rownum rnum__ FROM (
759
                                    " . $sql . "
760
                                ) tbl__ 
761
                                WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . "
762
                            ) WHERE rnum__ > " . $this->li_of[1];
763
                }
764
            } else {
765 1
                $sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1];
766
            }
767
        }
768 11
        return $this->qiterator = new TableQueryIterator(
769 11
            $this->db->get($sql, $par), 
770 11
            $this->pkey,
771 11
            $this->withr,
772 11
            $aliases
773
        );
774
    }
775
    /**
776
     * Perform the actual fetch
777
     * @param  array|null $fields optional array of columns to select (related columns can be used too)
778
     * @return array               the query result as an array
779
     */
780 1
    public function select(array $fields = null) : array
781
    {
782 1
        return iterator_to_array($this->iterator($fields));
783
    }
784
    /**
785
     * Insert a new row in the table
786
     * @param  array   $data   key value pairs, where each key is the column name and the value is the value to insert
787
     * @return array           the inserted ID where keys are column names and values are column values
788
     */
789 1
    public function insert(array $data) : array
790
    {
791 1
        $table = $this->definition->getName();
792 1
        $columns = $this->definition->getFullColumns();
793 1
        $insert = [];
794 1
        foreach ($data as $column => $value) {
795 1
            if (isset($columns[$column])) {
796 1
                $insert[$column] = $this->normalizeValue($columns[$column], $value);
797
            }
798
        }
799 1
        if (!count($insert)) {
800
            throw new DBException('No valid columns to insert');
801
        }
802 1
        $sql = 'INSERT INTO '.$table.' ('.implode(', ', array_keys($insert)).') VALUES (??)';
803 1
        $par = [$insert];
804 1
        $primary = $this->definition->getPrimaryKey();
805 1
        if (!count($primary)) {
806
            $this->db->query($sql, $par);
807
            return [];
808
        }
809 1
        if ($this->db->driverName() === 'oracle') {
810
            $ret = [];
811
            foreach ($primary as $k) {
812
                $ret[$k] = str_repeat(' ', 255);
813
                $par[] = &$ret[$k];
814
            }
815
            $sql .= ' RETURNING ' . implode(',', $primary) . ' INTO ' . implode(',', array_fill(0, count($primary), '?'));
816
            $this->db->query($sql, $par);
817
            return $ret;
818
        } else {
819 1
            $ret = [];
820 1
            $ins = $this->db->query($sql, $par)->insertID();
821 1
            foreach ($primary as $k) {
822 1
                $ret[$k] = $data[$k] ?? $ins;
823
            }
824 1
            return $ret;
825
        }
826
    }
827
    /**
828
     * Update the filtered rows with new data
829
     * @param  array  $data key value pairs, where each key is the column name and the value is the value to insert
830
     * @return int          the number of affected rows
831
     */
832 1
    public function update(array $data) : int
833
    {
834 1
        $table = $this->definition->getName();
835 1
        $columns = $this->definition->getFullColumns();
836 1
        $update = [];
837 1
        foreach ($data as $column => $value) {
838 1
            if (isset($columns[$column])) {
839 1
                $update[$column] = $this->normalizeValue($columns[$column], $value);
840
            }
841
        }
842 1
        if (!count($update)) {
843
            throw new DBException('No valid columns to update');
844
        }
845 1
        $sql = 'UPDATE '.$table.' SET ';
846 1
        $par = [];
847
        $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($update))) . ' ';
848 1
        $par = array_merge($par, array_values($update));
849 1
        if (count($this->where)) {
850 1
            $sql .= 'WHERE ';
851 1
            $tmp = [];
852 1
            foreach ($this->where as $v) {
853 1
                $tmp[] = $v[0];
854 1
                $par = array_merge($par, $v[1]);
855
            }
856 1
            $sql .= implode(' AND ', $tmp) . ' ';
857
        }
858 1
        if (count($this->order)) {
859
            $sql .= $this->order[0];
860
            $par = array_merge($par, $this->order[1]);
861
        }
862 1
        return $this->db->query($sql, $par)->affected();
863
    }
864
    /**
865
     * Delete the filtered rows from the DB
866
     * @return int the number of deleted rows
867
     */
868 1
    public function delete() : int
869
    {
870 1
        $table = $this->definition->getName();
871 1
        $sql = 'DELETE FROM '.$table.' ';
872 1
        $par = [];
873 1
        if (count($this->where)) {
874 1
            $sql .= 'WHERE ';
875 1
            $tmp = [];
876 1
            foreach ($this->where as $v) {
877 1
                $tmp[] = $v[0];
878 1
                $par = array_merge($par, $v[1]);
879
            }
880 1
            $sql .= implode(' AND ', $tmp) . ' ';
881
        }
882 1
        if (count($this->order)) {
883
            $sql .= $this->order[0];
884
            $par = array_merge($par, $this->order[1]);
885
        }
886 1
        return $this->db->query($sql, $par)->affected();
887
    }
888
    /**
889
     * Solve the n+1 queries problem by prefetching a relation by name
890
     * @param  string $relation the relation name to fetch along with the data
891
     * @return $this
892
     */
893 4
    public function with(string $relation) : TableQuery
894
    {
895 4
        $this->qiterator = null;
896 4
        $parts = explode('.', $relation);
897 4
        $table = $this->definition;
898 4
        array_reduce(
899 4
            $parts,
900 4
            function ($carry, $item) use (&$table) {
901 4
                $relation = $table->getRelation($item);
902 4
                if (!$relation) {
903
                    throw new DBException('Invalid relation name');
904
                }
905 4
                $name = $carry ? $carry . static::SEP . $item : $item;
906 4
                $this->withr[$name] = [ $relation, $carry ?? $table->getName() ];
907 4
                $table = $relation->table;
908 4
                return $name;
909 4
            }
910
        );
911 4
        return $this;
912
    }
913
914 1
    public function getIterator()
915
    {
916 1
        return $this->iterator();
917
    }
918
919 9
    public function offsetGet($offset)
920
    {
921 9
        return $this->iterator()->offsetGet($offset);
922
    }
923
    public function offsetExists($offset)
924
    {
925
        return $this->iterator()->offsetExists($offset);
926
    }
927
    public function offsetUnset($offset)
928
    {
929
        return $this->iterator()->offsetUnset($offset);
930
    }
931
    public function offsetSet($offset, $value)
932
    {
933
        return $this->iterator()->offsetSet($offset, $value);
934
    }
935
936
    public function collection(array $fields = null) : Collection
937
    {
938
        return new Collection($this->iterator($fields));
939
    }
940
}
941