Completed
Push — master ( c7c0ed...b4dd43 )
by Ivan
03:12
created

TableQuery::__call()   A

Complexity

Conditions 4
Paths 4

Size

Total Lines 12
Code Lines 7

Duplication

Lines 0
Ratio 0 %

Code Coverage

Tests 0
CRAP Score 20

Importance

Changes 0
Metric Value
c 0
b 0
f 0
dl 0
loc 12
ccs 0
cts 8
cp 0
rs 9.2
cc 4
eloc 7
nc 4
nop 2
crap 20
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
    /**
14
     * @var DatabaseInterface
15
     */
16
    protected $db;
17
    /**
18
     * @var Table
19
     */
20
    protected $definition;
21
    /**
22
     * @var TableQueryIterator|null
23
     */
24
    protected $qiterator;
25
26
    /**
27
     * @var array
28
     */
29
    protected $where = [];
30
    /**
31
     * @var array
32
     */
33
    protected $order = [];
34
    /**
35
     * @var array
36
     */
37
    protected $group = [];
38
    /**
39
     * @var array
40
     */
41
    protected $having = [];
42
    /**
43
     * @var int[]
44
     */
45
    protected $li_of = [0,0];
46
    protected $fields = [];
47
    /**
48
     * @var array
49
     */
50
    protected $withr = [];
51
    /**
52
     * @var array
53
     */
54
    protected $joins = [];
55
56
    /**
57
     * Create an instance
58
     * @param  DBInterface        $db         the database connection
59
     * @param  Table|string  $definition     the name or definition of the main table in the query
0 ignored issues
show
Bug introduced by
There is no parameter named $definition. Was it maybe removed?

This check looks for PHPDoc comments describing methods or function parameters that do not exist on the corresponding method or function.

Consider the following example. The parameter $italy is not defined by the method finale(...).

/**
 * @param array $germany
 * @param array $island
 * @param array $italy
 */
function finale($germany, $island) {
    return "2:1";
}

The most likely cause is that the parameter was removed, but the annotation was not.

Loading history...
60
     */
61
    public function __construct(DBInterface $db, $table)
62
    {
63
        $this->db = $db;
0 ignored issues
show
Documentation Bug introduced by
It seems like $db of type object<vakata\database\DBInterface> is incompatible with the declared type object<vakata\database\schema\DatabaseInterface> of property $db.

Our type inference engine has found an assignment to a property that is incompatible with the declared type of that property.

Either this assignment is in error or the assigned type should be added to the documentation/type hint for that property..

Loading history...
64
        $this->definition = $table instanceof Table ? $table : $this->db->definition((string)$table);
65
        $this->columns($this->definition->getColumns());
66
    }
67
    public function __clone()
68
    {
69
        $this->reset();
70
    }
71
    /**
72
     * Get the table definition of the queried table
73
     * @return Table        the definition
74
     */
75
    public function getDefinition() : Table
76
    {
77
        return $this->definition;
78
    }
79
80
    protected function getColumn($column)
81
    {
82
        $column = explode('.', $column, 2);
83
        if (count($column) === 1) {
84
            $column = [ $this->definition->getName(), $column[0] ];
85
        }
86
        if ($column[0] === $this->definition->getName()) {
87
            $col = $this->definition->getColumn($column[1]);
88
            if (!$col) {
89
                throw new DBException('Invalid column name in own table');
90
            }
91
        } else {
92
            if ($this->definition->hasRelation($column[0])) {
93
                $col = $this->definition->getRelation($column[0])->table->getColumn($column[1]);
94
                if (!$col) {
95
                    throw new DBException('Invalid column name in related table');
96
                }
97
            } else if (isset($this->joins[$column[0]])) {
98
                $col = $this->joins[$column[0]]->table->getColumn($column[1]);
99
                if (!$col) {
100
                    throw new DBException('Invalid column name in related table');
101
                }
102
            } else {
103
                throw new DBException('Invalid foreign table name: ' . implode(',', $column));
104
            }
105
        }
106
        return [ 'name' => implode('.', $column), 'data' => $col ];
107
    }
108
    protected function normalizeValue(TableColumn $col, $value)
109
    {
110
        if ($value === null && $col->isNullable()) {
111
            return null;
112
        }
113
        switch ($col->getBasicType()) {
114
            case 'date':
115
                if (is_string($value)) {
116
                    return date('Y-m-d', strtotime($value));
117
                }
118
                if (is_int($value)) {
119
                    return date('Y-m-d', $value);
120
                }
121
                if ($value instanceof \DateTime) {
122
                    return $value->format('Y-m-d');
123
                }
124
                return $value;
125
            case 'datetime':
126
                if (is_string($value)) {
127
                    return date('Y-m-d H:i:s', strtotime($value));
128
                }
129
                if (is_int($value)) {
130
                    return date('Y-m-d H:i:s', $value);
131
                }
132
                if ($value instanceof \DateTime) {
133
                    return $value->format('Y-m-d H:i:s');
134
                }
135
                return $value;
136
            case 'enum':
137
                if (is_int($value)) {
138
                    return $value;
139
                }
140
                if (!in_array($value, $col->getValues())) {
141
                    return 0;
142
                }
143
                return $value;
144
            case 'int':
145
                return (int)$value;
146
            default:
147
                return $value;
148
        }
149
    }
150
    /**
151
     * Filter the results by a column and a value
152
     * @param  string $column the column name to filter by (related columns can be used - for example: author.name)
153
     * @param  mixed  $value  a required value, array of values or range of values (range example: ['beg'=>1,'end'=>3])
154
     * @return $this
155
     */
156
    public function filter(string $column, $value) : TableQuery
157
    {
158
        list($name, $column) = array_values($this->getColumn($column));
159
        if (is_null($value)) {
160
            return $this->where($name . ' IS NULL');
161
        }
162
        if (!is_array($value)) {
163
            return $this->where(
164
                $name . ' = ?',
165
                [ $this->normalizeValue($column, $value) ]
166
            );
167
        }
168
        if (isset($value['beg']) && isset($value['end'])) {
169
            return $this->where(
170
                $name.' BETWEEN ? AND ?',
171
                [
172
                    $this->normalizeValue($column, $value['beg']),
173
                    $this->normalizeValue($column, $value['end'])
174
                ]
175
            );
176
        }
177
        return $this->where(
178
            $name . ' IN (??)',
179
            [ array_map(function ($v) use ($column) { return $this->normalizeValue($column, $v); }, $value) ]
180
        );
181
    }
182
    /**
183
     * Sort by a column
184
     * @param  string       $column the column name to sort by (related columns can be used - for example: author.name)
185
     * @param  bool|boolean $desc   should the sorting be in descending order, defaults to `false`
186
     * @return $this
187
     */
188
    public function sort(string $column, bool $desc = false) : TableQuery
189
    {
190
        return $this->order($this->getColumn($column)['name'] . ' ' . ($desc ? 'DESC' : 'ASC'));
191
    }
192
    /**
193
     * Group by a column (or columns)
194
     * @param  string|array        $column the column name (or names) to group by
195
     * @return $this
196
     */
197
    public function group($column) : TableQuery
198
    {
199
        if (!is_array($column)) {
200
            $column = [ $column ];
201
        }
202
        foreach ($column as $k => $v) {
203
            $column[$k] = $this->getColumn($v)['name'];
204
        }
205
        return $this->groupBy(implode(', ', $column), []);
206
    }
207
    /**
208
     * Get a part of the data
209
     * @param  int|integer $page    the page number to get (1-based), defaults to 1
210
     * @param  int|integer $perPage the number of records per page - defaults to 25
211
     * @return $this
212
     */
213
    public function paginate(int $page = 1, int $perPage = 25) : TableQuery
214
    {
215
        return $this->limit($perPage, ($page - 1) * $perPage);
216
    }
217
    public function __call($name, $data)
218
    {
219
        if (strpos($name, 'filterBy') === 0) {
220
            return $this->filter(strtolower(substr($name, 8)), $data[0]);
221
        }
222
        if (strpos($name, 'sortBy') === 0) {
223
            return $this->sort(strtolower(substr($name, 6)), $data[0]);
224
        }
225
        if (strpos($name, 'groupBy') === 0) {
226
            return $this->group(strtolower(substr($name, 7)));
227
        }
228
    }
229
    /**
230
     * Remove all filters, sorting, etc
231
     * @return $this
232
     */
233
    public function reset() : TableQuery
234
    {
235
        $this->where = [];
236
        $this->joins = [];
237
        $this->group = [];
238
        $this->withr = [];
239
        $this->order = [];
240
        $this->having = [];
241
        $this->li_of = [0,0];
242
        $this->qiterator = null;
243
        return $this;
244
    }
245
    /**
246
     * Apply advanced grouping
247
     * @param  string $sql    SQL statement to use in the GROUP BY clause
248
     * @param  array  $params optional params for the statement (defaults to an empty array)
249
     * @return $this
250
     */
251
    public function groupBy(string $sql, array $params = []) : TableQuery
252
    {
253
        $this->qiterator = null;
254
        $this->group = [ $sql, $params ];
255
        return $this;
256
    }
257
    /**
258
     * Join a table to the query (no need to do this for relations defined with foreign keys)
259
     * @param  Table|string $table     the table to join
260
     * @param  array        $fields    what to join on (joined_table_field => other_field) 
261
     * @param  string|null  $name      alias for the join, defaults to the table name 
262
     * @param  bool         $multiple  are multiple rows joined (results in a LEFT JOIN), default to true 
263
     * @return $this
264
     */
265
    public function join($table, array $fields, string $name = null, bool $multiple = true)
266
    {
267
        $table = $table instanceof Table ? $table : $this->db->definition((string)$table);
268
        $name = $name ?? $table->getName();
269
        if (isset($this->joins[$name]) || $this->definition->hasRelation($name)) {
270
            throw new DBException('Alias / table name already in use');
271
        }
272
        $this->joins[$name] = new TableRelation($name, $table, [], $multiple);
273
        foreach ($fields as $k => $v) {
274
            $k = explode('.', $k, 2);
275
            $k = count($k) == 2 ? $k[1] : $k[0];
276
            $this->joins[$name]->keymap[$this->getColumn($name . '.' . $k)['name']] = $this->getColumn($v)['name'];
277
        }
278
        return $this;
279
    }
280
    /**
281
     * Apply an advanced filter (can be called multiple times)
282
     * @param  string $sql    SQL statement to be used in the where clause
283
     * @param  array  $params parameters for the SQL statement (defaults to an empty array)
284
     * @return $this
285
     */
286
    public function where(string $sql, array $params = []) : TableQuery
287
    {
288
        $this->qiterator = null;
289
        $this->where[] = [ $sql, $params ];
290
        return $this;
291
    }
292
    /**
293
     * Apply an advanced HAVING filter (can be called multiple times)
294
     * @param  string $sql    SQL statement to be used in the HAING clause
295
     * @param  array  $params parameters for the SQL statement (defaults to an empty array)
296
     * @return $this
297
     */
298
    public function having(string $sql, array $params = []) : TableQuery
299
    {
300
        $this->qiterator = null;
301
        $this->having[] = [ $sql, $params ];
302
        return $this;
303
    }
304
    /**
305
     * Apply advanced sorting
306
     * @param  string $sql    SQL statement to use in the ORDER clause
307
     * @param  array  $params optional params for the statement (defaults to an empty array)
308
     * @return $this
309
     */
310
    public function order(string $sql, array $params = []) : TableQuery
311
    {
312
        $this->qiterator = null;
313
        $this->order = [ $sql, $params ];
314
        return $this;
315
    }
316
    /**
317
     * Apply an advanced limit
318
     * @param  int         $limit  number of rows to return
319
     * @param  int         $offset number of rows to skip from the beginning (defaults to 0)
320
     * @return $this
321
     */
322
    public function limit(int $limit, int $offset = 0) : TableQuery
323
    {
324
        $this->qiterator = null;
325
        $this->li_of = [ $limit, $offset ];
326
        return $this;
327
    }
328
    /**
329
     * Get the number of records
330
     * @return int the total number of records (does not respect pagination)
331
     */
332
    public function count() : int
333
    {
334
        $table = $this->definition->getName();
335
        $primary = $this->definition->getPrimaryKey();
336
        $sql = 'SELECT COUNT(DISTINCT '.$table.'.'.implode(', '.$table.'.', $primary).') FROM '.$table.' ';
337
        $par = [];
338
        
339
        $relations = $this->withr;
340
        foreach ($this->definition->getRelations() as $k => $v) {
341
            foreach ($this->where as $vv) {
342
                if (strpos($vv[0], $k . '.') !== false) {
343
                    $relations[] = $k;
344
                }
345
            }
346
            if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) {
347
                $relations[] = $k;
348
            }
349
        }
350
351
        foreach ($this->joins as $k => $v) {
352
            $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON ';
353
            $tmp = [];
354
            foreach ($v->keymap as $kk => $vv) {
355
                $tmp[] = $kk.' = '.$vv;
356
            }
357
            $sql .= implode(' AND ', $tmp) . ' ';
358
        }
359
        foreach (array_unique($relations) as $k) {
360
            $v = $this->definition->getRelation($k);
361
            if ($v->pivot) {
362
                $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$k.'_pivot ON ';
363
                $tmp = [];
364
                foreach ($v->keymap as $kk => $vv) {
365
                    $tmp[] = $table.'.'.$kk.' = '.$k.'_pivot.'.$vv.' ';
366
                }
367
                $sql .= implode(' AND ', $tmp) . ' ';
368
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON ';
369
                $tmp = [];
370
                foreach ($v->pivot_keymap as $kk => $vv) {
371
                    $tmp[] = $k.'.'.$vv.' = '.$k.'_pivot.'.$kk.' ';
372
                }
373
                $sql .= implode(' AND ', $tmp) . ' ';
374
            } else {
375
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$k.' ON ';
376
                $tmp = [];
377
                foreach ($v->keymap as $kk => $vv) {
378
                    $tmp[] = $table.'.'.$kk.' = '.$k.'.'.$vv.' ';
379
                }
380
                if ($v->sql) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $v->sql of type string|null is loosely compared to true; this is ambiguous if the string can be empty. You might want to explicitly use !== null instead.

In PHP, under loose comparison (like ==, or !=, or switch conditions), values of different types might be equal.

For string values, the empty string '' is a special case, in particular the following results might be unexpected:

''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
Loading history...
381
                    $tmp[] = $v->sql . ' ';
382
                    $par = array_merge($par, $v->par ?? []);
383
                }
384
                $sql .= implode(' AND ', $tmp) . ' ';
385
            }
386
        }
387
        if (count($this->where)) {
388
            $sql .= 'WHERE ';
389
            $tmp = [];
390
            foreach ($this->where as $v) {
391
                $tmp[] = '(' . $v[0] . ')';
392
                $par = array_merge($par, $v[1]);
393
            }
394
            $sql .= implode(' AND ', $tmp).' ';
395
        }
396
        if (count($this->group)) {
397
            $sql .= 'GROUP BY ' . $this->group[0] . ' ';
398
            $par = array_merge($par, $this->group[1]);
399
        }
400
        if (count($this->having)) {
401
            $sql .= 'HAVING ';
402
            $tmp = [];
403
            foreach ($this->having as $v) {
404
                $tmp[] = '(' . $v[0] . ')';
405
                $par = array_merge($par, $v[1]);
406
            }
407
            $sql .= implode(' AND ', $tmp).' ';
408
        }
409
        return $this->db->one($sql, $par);
410
    }
411
    /**
412
     * Specify which columns to fetch (be default all table columns are fetched)
413
     * @param  array $fields optional array of columns to select (related columns can be used too)
414
     * @return $this
415
     */
416
    public function columns(array $fields) : TableQuery
417
    {
418
        foreach ($fields as $k => $v) {
419
            if (strpos($v, '*') !== false) {
420
                $temp = explode('.', $v);
421
                if (count($temp) == 1) {
422
                    $table = $this->definition->getName();
423
                } else {
424
                    $table = $temp[0];
425
                }
426
                $cols = [];
0 ignored issues
show
Unused Code introduced by
$cols 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...
427
                if ($this->definition->hasRelation($table)) {
428
                    $cols = $this->definition->getRelation($table)->table->getColumns();
429
                } else if (isset($this->joins[$table])) {
430
                    $cols = $this->joins[$table]->table->getColumns();
431
                } else {
432
                    throw new DBException('Invalid foreign table name');
433
                }
434
                foreach ($cols as $col) {
435
                    $fields[] = $table . '.' . $col;
436
                }
437
                unset($fields[$k]);
438
            }
439
        }
440
        $primary = $this->definition->getPrimaryKey();
441
        foreach ($fields as $k => $v) {
442
            try {
443
                $fields[$k] = $this->getColumn($v)['name'];
444
            } catch (DBException $e) {
445
                $fields[$k] = $v;
446
            }
447
        }
448
        foreach ($primary as $field) {
449
            $field = $this->getColumn($field)['name'];
450
            if (!in_array($field, $fields)) {
451
                $fields[] = $field;
452
            }
453
        }
454
        $this->fields = $fields;
455
        return $this;
456
    }
457
    /**
458
     * Perform the actual fetch
459
     * @param  array|null $fields optional array of columns to select (related columns can be used too)
460
     * @return TableQueryIterator               the query result as an iterator
461
     */
462
    public function iterator(array $fields = null) : TableQueryIterator
463
    {
464
        if ($this->qiterator) {
465
            return $this->qiterator;
466
        }
467
        $table = $this->definition->getName();
468
        $primary = $this->definition->getPrimaryKey();
469
        if ($fields !== null) {
470
            $this->columns($fields);
471
        }
472
        $relations = $this->withr;
473
        foreach ($this->definition->getRelations() as $k => $v) {
474
            foreach ($this->fields as $field) {
475
                if (strpos($field, $k . '.') === 0) {
476
                    $relations[] = $k;
477
                }
478
            }
479
            foreach ($this->where as $v) {
480
                if (strpos($v[0], $k . '.') !== false) {
481
                    $relations[] = $k;
482
                }
483
            }
484
            if (isset($this->order[0]) && strpos($this->order[0], $k . '.') !== false) {
485
                $relations[] = $k;
486
            }
487
        }
488
        $select = [];
489
        foreach ($this->fields as $k => $field) {
490
            $select[] = $field . (!is_numeric($k) ? ' ' . $k : '');
491
        }
492
        foreach ($this->withr as $relation) {
493
            foreach ($this->definition->getRelation($relation)->table->getColumns() as $column) {
494
                $select[] = $relation . '.' . $column . ' ' . $relation . '___' . $column;
495
            }
496
        }
497
        $sql = 'SELECT '.implode(', ', $select).' FROM '.$table.' ';
498
        $par = [];
499
        foreach ($this->joins as $k => $v) {
500
            $sql .= ($v->many ? 'LEFT ' : '' ) . 'JOIN '.$v->table->getName().' '.$k.' ON ';
501
            $tmp = [];
502
            foreach ($v->keymap as $kk => $vv) {
503
                $tmp[] = $kk.' = '.$vv;
504
            }
505
            $sql .= implode(' AND ', $tmp) . ' ';
506
        }
507
        foreach (array_unique($relations) as $relation) {
508
            $v = $this->definition->getRelation($relation);
509
            if ($v->pivot) {
510
                $sql .= 'LEFT JOIN '.$v->pivot->getName().' '.$relation.'_pivot ON ';
511
                $tmp = [];
512
                foreach ($v->keymap as $kk => $vv) {
513
                    $tmp[] = $table.'.'.$kk.' = '.$relation.'_pivot.'.$vv.' ';
514
                }
515
                $sql .= implode(' AND ', $tmp) . ' ';
516
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON ';
517
                $tmp = [];
518
                foreach ($v->pivot_keymap as $kk => $vv) {
519
                    $tmp[] = $relation.'.'.$vv.' = '.$relation.'_pivot.'.$kk.' ';
520
                }
521
                $sql .= implode(' AND ', $tmp) . ' ';
522
            } else {
523
                $sql .= 'LEFT JOIN '.$v->table->getName().' '.$relation.' ON ';
524
                $tmp = [];
525
                foreach ($v->keymap as $kk => $vv) {
526
                    $tmp[] = $table.'.'.$kk.' = '.$relation.'.'.$vv.' ';
527
                }
528
                if ($v->sql) {
0 ignored issues
show
Bug Best Practice introduced by
The expression $v->sql of type string|null is loosely compared to true; this is ambiguous if the string can be empty. You might want to explicitly use !== null instead.

In PHP, under loose comparison (like ==, or !=, or switch conditions), values of different types might be equal.

For string values, the empty string '' is a special case, in particular the following results might be unexpected:

''   == false // true
''   == null  // true
'ab' == false // false
'ab' == null  // false

// It is often better to use strict comparison
'' === false // false
'' === null  // false
Loading history...
529
                    $tmp[] = $v->sql . ' ';
530
                    $par = array_merge($par, $v->par ?? []);
531
                }
532
                $sql .= implode(' AND ', $tmp) . ' ';
533
            }
534
        }
535
        if (count($this->where)) {
536
            $sql .= 'WHERE ';
537
            $tmp = [];
538
            foreach ($this->where as $v) {
539
                $tmp[] = '(' . $v[0] . ')';
540
                $par = array_merge($par, $v[1]);
541
            }
542
            $sql .= implode(' AND ', $tmp).' ';
543
        }
544
        if (count($this->group)) {
545
            $sql .= 'GROUP BY ' . $this->group[0] . ' ';
546
            $par = array_merge($par, $this->group[1]);
547
        }
548
        if (count($this->having)) {
549
            $sql .= 'HAVING ';
550
            $tmp = [];
551
            foreach ($this->having as $v) {
552
                $tmp[] = '(' . $v[0] . ')';
553
                $par = array_merge($par, $v[1]);
554
            }
555
            $sql .= implode(' AND ', $tmp).' ';
556
        }
557
        //if ($this->definition->hasRelations()) {
0 ignored issues
show
Unused Code Comprehensibility introduced by
70% 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...
558
        //    $sql .= 'GROUP BY '.$table.'.'.implode(', '.$table.'.', $primary).' ';
0 ignored issues
show
Unused Code Comprehensibility introduced by
50% 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...
559
        //}
560
        if (count($this->order)) {
561
            $sql .= 'ORDER BY ' . $this->order[0] . ' ';
562
            $par = array_merge($par, $this->order[1]);
563
        }
564
        $porder = [];
565
        foreach ($primary as $field) {
566
            $porder[] = $this->getColumn($field)['name'];
567
        }
568
        $sql .= (count($this->order) ? ', ' : 'ORDER BY ') . implode(', ', $porder) . ' ';
569
570
        if ($this->li_of[0]) {
571
            if ($this->db->driver() === 'oracle') {
572
                if ((int)($this->db->settings()->options['version'] ?? 0) >= 12) {
573
                    $sql .= 'OFFSET ' . $this->li_of[1] . ' ROWS FETCH NEXT ' . $this->li_of[0] . ' ROWS ONLY';
574
                } else {
575
                    $f = array_map(function ($v) {
576
                        $v = explode(' ', trim($v), 2);
577
                        if (count($v) === 2) { return $v[1]; }
578
                        $v = explode('.', $v[0], 2);
579
                        return count($v) === 2 ? $v[1] : $v[0];
580
                    }, $select);
581
                    $sql = "SELECT " . implode(', ', $f) . " 
582
                            FROM (
583
                                SELECT tbl__.*, rownum rnum__ FROM (
584
                                    " . $sql . "
585
                                ) tbl__ 
586
                                WHERE rownum <= " . ($this->li_of[0] + $this->li_of[1]) . "
587
                            ) WHERE rnum__ > " . $this->li_of[1];
588
                }
589
            } else {
590
                $sql .= 'LIMIT ' . $this->li_of[0] . ' OFFSET ' . $this->li_of[1];
591
            }
592
        }
593
        return $this->qiterator = new TableQueryIterator(
594
            $this->db->get($sql, $par), 
595
            $this->definition->getPrimaryKey(),
596
            array_combine(
597
                $this->withr,
598
                array_map(function ($relation) {
599
                    return $this->definition->getRelation($relation);
600
                }, $this->withr)
601
            )
602
        );
603
    }
604
    /**
605
     * Perform the actual fetch
606
     * @param  array|null $fields optional array of columns to select (related columns can be used too)
607
     * @return array               the query result as an array
608
     */
609
    public function select(array $fields = null) : array
610
    {
611
        return iterator_to_array($this->iterator($fields));
612
    }
613
    /**
614
     * Insert a new row in the table
615
     * @param  array   $data   key value pairs, where each key is the column name and the value is the value to insert
616
     * @return array           the inserted ID where keys are column names and values are column values
617
     */
618
    public function insert(array $data) : array
619
    {
620
        $table = $this->definition->getName();
621
        $columns = $this->definition->getFullColumns();
622
        $insert = [];
623
        foreach ($data as $column => $value) {
624
            if (isset($columns[$column])) {
625
                $insert[$column] = $this->normalizeValue($columns[$column], $value);
626
            }
627
        }
628
        if (!count($insert)) {
629
            throw new DBException('No valid columns to insert');
630
        }
631
        $sql = 'INSERT INTO '.$table.' ('.implode(', ', array_keys($insert)).') VALUES (??)';
632
        $par = [$insert];
633
        //if ($update) {
0 ignored issues
show
Unused Code Comprehensibility introduced by
72% 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...
634
        //    $sql .= 'ON DUPLICATE KEY UPDATE ';
0 ignored issues
show
Unused Code Comprehensibility introduced by
38% 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...
635
        //    $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($insert))) . ' ';
0 ignored issues
show
Unused Code Comprehensibility introduced by
53% 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...
636
        //    $par  = array_merge($par, $insert);
0 ignored issues
show
Unused Code Comprehensibility introduced by
54% 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...
637
        //}
638
        if ($this->db->driver() === 'oracle') {
639
            $primary = $this->definition->getPrimaryKey();
640
            $ret = [];
641
            foreach ($primary as $k) {
642
                $ret[$k] = str_repeat(' ', 255);
643
                $par[] = &$ret[$k];
644
            }
645
            $sql .= ' RETURNING ' . implode(',', $primary) . ' INTO ' . implode(',', array_fill(0, count($primary), '?'));
646
            $this->db->query($sql, $par);
647
            return $ret;
648
        } else {
649
            $ret = [];
650
            $ins = $this->db->query($sql, $par)->insertID();
651
            foreach ($this->definition->getPrimaryKey() as $k) {
652
                $ret[$k] = $data[$k] ?? $ins;
653
            }
654
            return $ret;
655
        }
656
    }
657
    /**
658
     * Update the filtered rows with new data
659
     * @param  array  $data key value pairs, where each key is the column name and the value is the value to insert
660
     * @return int          the number of affected rows
661
     */
662
    public function update(array $data) : int
663
    {
664
        $table = $this->definition->getName();
665
        $columns = $this->definition->getFullColumns();
666
        $update = [];
667
        foreach ($data as $column => $value) {
668
            if (isset($columns[$column])) {
669
                $update[$column] = $this->normalizeValue($columns[$column], $value);
670
            }
671
        }
672
        if (!count($update)) {
673
            throw new DBException('No valid columns to update');
674
        }
675
        $sql = 'UPDATE '.$table.' SET ';
676
        $par = [];
677
        $sql .= implode(', ', array_map(function ($v) { return $v . ' = ?'; }, array_keys($update))) . ' ';
678
        $par = array_merge($par, array_values($update));
679
        if (count($this->where)) {
680
            $sql .= 'WHERE ';
681
            $tmp = [];
682
            foreach ($this->where as $v) {
683
                $tmp[] = $v[0];
684
                $par = array_merge($par, $v[1]);
685
            }
686
            $sql .= implode(' AND ', $tmp) . ' ';
687
        }
688
        if (count($this->order)) {
689
            $sql .= $this->order[0];
690
            $par = array_merge($par, $this->order[1]);
691
        }
692
        return $this->db->query($sql, $par)->affected();
693
    }
694
    /**
695
     * Delete the filtered rows from the DB
696
     * @return int the number of deleted rows
697
     */
698
    public function delete() : int
699
    {
700
        $table = $this->definition->getName();
701
        $sql = 'DELETE FROM '.$table.' ';
702
        $par = [];
703
        if (count($this->where)) {
704
            $sql .= 'WHERE ';
705
            $tmp = [];
706
            foreach ($this->where as $v) {
707
                $tmp[] = $v[0];
708
                $par = array_merge($par, $v[1]);
709
            }
710
            $sql .= implode(' AND ', $tmp) . ' ';
711
        }
712
        if (count($this->order)) {
713
            $sql .= $this->order[0];
714
            $par = array_merge($par, $this->order[1]);
715
        }
716
        return $this->db->query($sql, $par)->affected();
717
    }
718
    /**
719
     * Solve the n+1 queries problem by prefetching a relation by name
720
     * @param  string $relation the relation name to fetch along with the data
721
     * @return $this
722
     */
723
    public function with(string $relation) : TableQuery
724
    {
725
        if (!$this->definition->hasRelation($relation)) {
726
            throw new DBException('Invalid relation name');
727
        }
728
        $this->qiterator = null;
729
        $this->withr[$relation] = $relation;
730
        return $this;
731
    }
732
733
    public function getIterator()
734
    {
735
        return $this->iterator();
736
    }
737
738
    public function offsetGet($offset)
739
    {
740
        return $this->iterator()->offsetGet($offset);
741
    }
742
    public function offsetExists($offset)
743
    {
744
        return $this->iterator()->offsetExists($offset);
745
    }
746
    public function offsetUnset($offset)
747
    {
748
        return $this->iterator()->offsetUnset($offset);
749
    }
750
    public function offsetSet($offset, $value)
751
    {
752
        return $this->iterator()->offsetSet($offset, $value);
753
    }
754
755
    public function collection(array $fields = null) : Collection
756
    {
757
        return new Collection($this->iterator($fields));
758
    }
759
}
760