Completed
Push — master ( 667eb8...379582 )
by Anton
04:04
created

SelectQuery::getParameters()   B

Complexity

Conditions 5
Paths 9

Size

Total Lines 20
Code Lines 9

Duplication

Lines 0
Ratio 0 %

Importance

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