1 | <?php |
||||
2 | /* |
||||
3 | * THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS |
||||
4 | * "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT |
||||
5 | * LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR |
||||
6 | * A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT |
||||
7 | * OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, |
||||
8 | * SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT |
||||
9 | * LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, |
||||
10 | * DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY |
||||
11 | * THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT |
||||
12 | * (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE |
||||
13 | * OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. |
||||
14 | * |
||||
15 | * This software consists of voluntary contributions made by many individuals |
||||
16 | * and is licensed under the MIT license. For more information, see |
||||
17 | * <http://www.doctrine-project.org>. |
||||
18 | */ |
||||
19 | |||||
20 | namespace Doctrine\DBAL\Query; |
||||
21 | |||||
22 | use Doctrine\DBAL\Query\Expression\CompositeExpression; |
||||
23 | use Doctrine\DBAL\Connection; |
||||
24 | |||||
25 | /** |
||||
26 | * QueryBuilder class is responsible to dynamically create SQL queries. |
||||
27 | * |
||||
28 | * Important: Verify that every feature you use will work with your database vendor. |
||||
29 | * SQL Query Builder does not attempt to validate the generated SQL at all. |
||||
30 | * |
||||
31 | * The query builder does no validation whatsoever if certain features even work with the |
||||
32 | * underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements |
||||
33 | * even if some vendors such as MySQL support it. |
||||
34 | * |
||||
35 | * @link www.doctrine-project.org |
||||
36 | * @since 2.1 |
||||
37 | * @author Guilherme Blanco <[email protected]> |
||||
38 | * @author Benjamin Eberlei <[email protected]> |
||||
39 | */ |
||||
40 | class QueryBuilder |
||||
41 | { |
||||
42 | /* |
||||
43 | * The query types. |
||||
44 | */ |
||||
45 | const SELECT = 0; |
||||
46 | const DELETE = 1; |
||||
47 | const UPDATE = 2; |
||||
48 | const INSERT = 3; |
||||
49 | |||||
50 | /* |
||||
51 | * The builder states. |
||||
52 | */ |
||||
53 | const STATE_DIRTY = 0; |
||||
54 | const STATE_CLEAN = 1; |
||||
55 | |||||
56 | /** |
||||
57 | * The DBAL Connection. |
||||
58 | * |
||||
59 | * @var \Doctrine\DBAL\Connection |
||||
60 | */ |
||||
61 | private $connection; |
||||
62 | |||||
63 | /** |
||||
64 | * @var array The array of SQL parts collected. |
||||
65 | */ |
||||
66 | private $sqlParts = [ |
||||
67 | 'select' => [], |
||||
68 | 'from' => [], |
||||
69 | 'join' => [], |
||||
70 | 'set' => [], |
||||
71 | 'where' => null, |
||||
72 | 'groupBy' => [], |
||||
73 | 'having' => null, |
||||
74 | 'orderBy' => [], |
||||
75 | 'values' => [], |
||||
76 | ]; |
||||
77 | |||||
78 | /** |
||||
79 | * The complete SQL string for this query. |
||||
80 | * |
||||
81 | * @var string |
||||
82 | */ |
||||
83 | private $sql; |
||||
84 | |||||
85 | /** |
||||
86 | * The query parameters. |
||||
87 | * |
||||
88 | * @var array |
||||
89 | */ |
||||
90 | private $params = []; |
||||
91 | |||||
92 | /** |
||||
93 | * The parameter type map of this query. |
||||
94 | * |
||||
95 | * @var array |
||||
96 | */ |
||||
97 | private $paramTypes = []; |
||||
98 | |||||
99 | /** |
||||
100 | * The type of query this is. Can be select, update or delete. |
||||
101 | * |
||||
102 | * @var integer |
||||
103 | */ |
||||
104 | private $type = self::SELECT; |
||||
105 | |||||
106 | /** |
||||
107 | * The state of the query object. Can be dirty or clean. |
||||
108 | * |
||||
109 | * @var integer |
||||
110 | */ |
||||
111 | private $state = self::STATE_CLEAN; |
||||
112 | |||||
113 | /** |
||||
114 | * The index of the first result to retrieve. |
||||
115 | * |
||||
116 | * @var integer |
||||
117 | */ |
||||
118 | private $firstResult = null; |
||||
119 | |||||
120 | /** |
||||
121 | * The maximum number of results to retrieve. |
||||
122 | * |
||||
123 | * @var integer |
||||
124 | */ |
||||
125 | private $maxResults = null; |
||||
126 | |||||
127 | /** |
||||
128 | * The counter of bound parameters used with {@see bindValue). |
||||
129 | * |
||||
130 | * @var integer |
||||
131 | */ |
||||
132 | private $boundCounter = 0; |
||||
133 | |||||
134 | /** |
||||
135 | * Initializes a new <tt>QueryBuilder</tt>. |
||||
136 | * |
||||
137 | * @param \Doctrine\DBAL\Connection $connection The DBAL Connection. |
||||
138 | */ |
||||
139 | 66 | public function __construct(Connection $connection) |
|||
140 | { |
||||
141 | 66 | $this->connection = $connection; |
|||
142 | 66 | } |
|||
143 | |||||
144 | /** |
||||
145 | * Gets an ExpressionBuilder used for object-oriented construction of query expressions. |
||||
146 | * This producer method is intended for convenient inline usage. Example: |
||||
147 | * |
||||
148 | * <code> |
||||
149 | * $qb = $conn->createQueryBuilder() |
||||
150 | * ->select('u') |
||||
151 | * ->from('users', 'u') |
||||
152 | * ->where($qb->expr()->eq('u.id', 1)); |
||||
153 | * </code> |
||||
154 | * |
||||
155 | * For more complex expression construction, consider storing the expression |
||||
156 | * builder object in a local variable. |
||||
157 | * |
||||
158 | * @return \Doctrine\DBAL\Query\Expression\ExpressionBuilder |
||||
159 | */ |
||||
160 | 28 | public function expr() |
|||
161 | { |
||||
162 | 28 | return $this->connection->getExpressionBuilder(); |
|||
163 | } |
||||
164 | |||||
165 | /** |
||||
166 | * Gets the type of the currently built query. |
||||
167 | * |
||||
168 | * @return integer |
||||
169 | */ |
||||
170 | 12 | public function getType() |
|||
171 | { |
||||
172 | 12 | return $this->type; |
|||
173 | } |
||||
174 | |||||
175 | /** |
||||
176 | * Gets the associated DBAL Connection for this query builder. |
||||
177 | * |
||||
178 | * @return \Doctrine\DBAL\Connection |
||||
179 | */ |
||||
180 | 1 | public function getConnection() |
|||
181 | { |
||||
182 | 1 | return $this->connection; |
|||
183 | } |
||||
184 | |||||
185 | /** |
||||
186 | * Gets the state of this query builder instance. |
||||
187 | * |
||||
188 | * @return integer Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN. |
||||
189 | */ |
||||
190 | 3 | public function getState() |
|||
191 | { |
||||
192 | 3 | return $this->state; |
|||
193 | } |
||||
194 | |||||
195 | /** |
||||
196 | * Executes this query using the bound parameters and their types. |
||||
197 | * |
||||
198 | * Uses {@see Connection::executeQuery} for select statements and {@see Connection::executeUpdate} |
||||
199 | * for insert, update and delete statements. |
||||
200 | * |
||||
201 | * @return \Doctrine\DBAL\Driver\Statement|int |
||||
202 | */ |
||||
203 | public function execute() |
||||
204 | { |
||||
205 | if ($this->type == self::SELECT) { |
||||
206 | return $this->connection->executeQuery($this->getSQL(), $this->params, $this->paramTypes); |
||||
207 | } else { |
||||
208 | return $this->connection->executeUpdate($this->getSQL(), $this->params, $this->paramTypes); |
||||
209 | } |
||||
210 | } |
||||
211 | |||||
212 | /** |
||||
213 | * Gets the complete SQL string formed by the current specifications of this QueryBuilder. |
||||
214 | * |
||||
215 | * <code> |
||||
216 | * $qb = $em->createQueryBuilder() |
||||
217 | * ->select('u') |
||||
218 | * ->from('User', 'u') |
||||
219 | * echo $qb->getSQL(); // SELECT u FROM User u |
||||
220 | * </code> |
||||
221 | * |
||||
222 | * @return string The SQL query string. |
||||
223 | */ |
||||
224 | 56 | public function getSQL() |
|||
225 | { |
||||
226 | 56 | if ($this->sql !== null && $this->state === self::STATE_CLEAN) { |
|||
227 | 1 | return $this->sql; |
|||
228 | } |
||||
229 | |||||
230 | 56 | switch ($this->type) { |
|||
231 | 56 | case self::INSERT: |
|||
232 | 4 | $sql = $this->getSQLForInsert(); |
|||
233 | 4 | break; |
|||
234 | 52 | case self::DELETE: |
|||
235 | 3 | $sql = $this->getSQLForDelete(); |
|||
236 | 3 | break; |
|||
237 | |||||
238 | 49 | case self::UPDATE: |
|||
239 | 3 | $sql = $this->getSQLForUpdate(); |
|||
240 | 3 | break; |
|||
241 | |||||
242 | 46 | case self::SELECT: |
|||
243 | default: |
||||
244 | 46 | $sql = $this->getSQLForSelect(); |
|||
245 | 44 | break; |
|||
246 | } |
||||
247 | |||||
248 | 54 | $this->state = self::STATE_CLEAN; |
|||
249 | 54 | $this->sql = $sql; |
|||
250 | |||||
251 | 54 | return $sql; |
|||
252 | } |
||||
253 | |||||
254 | /** |
||||
255 | * Sets a query parameter for the query being constructed. |
||||
256 | * |
||||
257 | * <code> |
||||
258 | * $qb = $conn->createQueryBuilder() |
||||
259 | * ->select('u') |
||||
260 | * ->from('users', 'u') |
||||
261 | * ->where('u.id = :user_id') |
||||
262 | * ->setParameter(':user_id', 1); |
||||
263 | * </code> |
||||
264 | * |
||||
265 | * @param string|integer $key The parameter position or name. |
||||
266 | * @param mixed $value The parameter value. |
||||
267 | * @param string|integer|null $type One of the PDO::PARAM_* constants. |
||||
268 | * |
||||
269 | * @return $this This QueryBuilder instance. |
||||
270 | */ |
||||
271 | 6 | public function setParameter($key, $value, $type = null) |
|||
272 | { |
||||
273 | 6 | if ($type !== null) { |
|||
274 | 5 | $this->paramTypes[$key] = $type; |
|||
275 | } |
||||
276 | |||||
277 | 6 | $this->params[$key] = $value; |
|||
278 | |||||
279 | 6 | return $this; |
|||
280 | } |
||||
281 | |||||
282 | /** |
||||
283 | * Sets a collection of query parameters for the query being constructed. |
||||
284 | * |
||||
285 | * <code> |
||||
286 | * $qb = $conn->createQueryBuilder() |
||||
287 | * ->select('u') |
||||
288 | * ->from('users', 'u') |
||||
289 | * ->where('u.id = :user_id1 OR u.id = :user_id2') |
||||
290 | * ->setParameters(array( |
||||
291 | * ':user_id1' => 1, |
||||
292 | * ':user_id2' => 2 |
||||
293 | * )); |
||||
294 | * </code> |
||||
295 | * |
||||
296 | * @param array $params The query parameters to set. |
||||
297 | * @param array $types The query parameters types to set. |
||||
298 | * |
||||
299 | * @return $this This QueryBuilder instance. |
||||
300 | */ |
||||
301 | public function setParameters(array $params, array $types = []) |
||||
302 | { |
||||
303 | $this->paramTypes = $types; |
||||
304 | $this->params = $params; |
||||
305 | |||||
306 | return $this; |
||||
307 | } |
||||
308 | |||||
309 | /** |
||||
310 | * Gets all defined query parameters for the query being constructed indexed by parameter index or name. |
||||
311 | * |
||||
312 | * @return array The currently defined query parameters indexed by parameter index or name. |
||||
313 | */ |
||||
314 | 1 | public function getParameters() |
|||
315 | { |
||||
316 | 1 | return $this->params; |
|||
317 | } |
||||
318 | |||||
319 | /** |
||||
320 | * Gets a (previously set) query parameter of the query being constructed. |
||||
321 | * |
||||
322 | * @param mixed $key The key (index or name) of the bound parameter. |
||||
323 | * |
||||
324 | * @return mixed The value of the bound parameter. |
||||
325 | */ |
||||
326 | 3 | public function getParameter($key) |
|||
327 | { |
||||
328 | 3 | return $this->params[$key] ?? null; |
|||
329 | } |
||||
330 | |||||
331 | /** |
||||
332 | * Gets all defined query parameter types for the query being constructed indexed by parameter index or name. |
||||
333 | * |
||||
334 | * @return array The currently defined query parameter types indexed by parameter index or name. |
||||
335 | */ |
||||
336 | 1 | public function getParameterTypes() |
|||
337 | { |
||||
338 | 1 | return $this->paramTypes; |
|||
339 | } |
||||
340 | |||||
341 | /** |
||||
342 | * Gets a (previously set) query parameter type of the query being constructed. |
||||
343 | * |
||||
344 | * @param mixed $key The key (index or name) of the bound parameter type. |
||||
345 | * |
||||
346 | * @return mixed The value of the bound parameter type. |
||||
347 | */ |
||||
348 | 4 | public function getParameterType($key) |
|||
349 | { |
||||
350 | 4 | return $this->paramTypes[$key] ?? null; |
|||
351 | } |
||||
352 | |||||
353 | /** |
||||
354 | * Sets the position of the first result to retrieve (the "offset"). |
||||
355 | * |
||||
356 | * @param integer $firstResult The first result to return. |
||||
357 | * |
||||
358 | * @return $this This QueryBuilder instance. |
||||
359 | */ |
||||
360 | 1 | public function setFirstResult($firstResult) |
|||
361 | { |
||||
362 | 1 | $this->state = self::STATE_DIRTY; |
|||
363 | 1 | $this->firstResult = $firstResult; |
|||
364 | |||||
365 | 1 | return $this; |
|||
366 | } |
||||
367 | |||||
368 | /** |
||||
369 | * Gets the position of the first result the query object was set to retrieve (the "offset"). |
||||
370 | * Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder. |
||||
371 | * |
||||
372 | * @return integer The position of the first result. |
||||
373 | */ |
||||
374 | 1 | public function getFirstResult() |
|||
375 | { |
||||
376 | 1 | return $this->firstResult; |
|||
377 | } |
||||
378 | |||||
379 | /** |
||||
380 | * Sets the maximum number of results to retrieve (the "limit"). |
||||
381 | * |
||||
382 | * @param integer $maxResults The maximum number of results to retrieve. |
||||
383 | * |
||||
384 | * @return $this This QueryBuilder instance. |
||||
385 | */ |
||||
386 | 1 | public function setMaxResults($maxResults) |
|||
387 | { |
||||
388 | 1 | $this->state = self::STATE_DIRTY; |
|||
389 | 1 | $this->maxResults = $maxResults; |
|||
390 | |||||
391 | 1 | return $this; |
|||
392 | } |
||||
393 | |||||
394 | /** |
||||
395 | * Gets the maximum number of results the query object was set to retrieve (the "limit"). |
||||
396 | * Returns NULL if {@link setMaxResults} was not applied to this query builder. |
||||
397 | * |
||||
398 | * @return integer The maximum number of results. |
||||
399 | */ |
||||
400 | 1 | public function getMaxResults() |
|||
401 | { |
||||
402 | 1 | return $this->maxResults; |
|||
403 | } |
||||
404 | |||||
405 | /** |
||||
406 | * Either appends to or replaces a single, generic query part. |
||||
407 | * |
||||
408 | * The available parts are: 'select', 'from', 'set', 'where', |
||||
409 | * 'groupBy', 'having' and 'orderBy'. |
||||
410 | * |
||||
411 | * @param string $sqlPartName |
||||
412 | * @param string $sqlPart |
||||
413 | * @param boolean $append |
||||
414 | * |
||||
415 | * @return $this This QueryBuilder instance. |
||||
416 | */ |
||||
417 | 58 | public function add($sqlPartName, $sqlPart, $append = false) |
|||
418 | { |
||||
419 | 58 | $isArray = is_array($sqlPart); |
|||
420 | 58 | $isMultiple = is_array($this->sqlParts[$sqlPartName]); |
|||
421 | |||||
422 | 58 | if ($isMultiple && !$isArray) { |
|||
423 | 7 | $sqlPart = [$sqlPart]; |
|||
424 | } |
||||
425 | |||||
426 | 58 | $this->state = self::STATE_DIRTY; |
|||
427 | |||||
428 | 58 | if ($append) { |
|||
429 | 50 | if ($sqlPartName == "orderBy" || $sqlPartName == "groupBy" || $sqlPartName == "select" || $sqlPartName == "set") { |
|||
430 | 9 | foreach ($sqlPart as $part) { |
|||
431 | 9 | $this->sqlParts[$sqlPartName][] = $part; |
|||
432 | } |
||||
433 | 47 | } elseif ($isArray && is_array($sqlPart[key($sqlPart)])) { |
|||
434 | 12 | $key = key($sqlPart); |
|||
435 | 12 | $this->sqlParts[$sqlPartName][$key][] = $sqlPart[$key]; |
|||
436 | 47 | } elseif ($isMultiple) { |
|||
437 | 47 | $this->sqlParts[$sqlPartName][] = $sqlPart; |
|||
438 | } else { |
||||
439 | 8 | $this->sqlParts[$sqlPartName] = $sqlPart; |
|||
440 | } |
||||
441 | |||||
442 | 50 | return $this; |
|||
443 | } |
||||
444 | |||||
445 | 58 | $this->sqlParts[$sqlPartName] = $sqlPart; |
|||
446 | |||||
447 | 58 | return $this; |
|||
448 | } |
||||
449 | |||||
450 | /** |
||||
451 | * Specifies an item that is to be returned in the query result. |
||||
452 | * Replaces any previously specified selections, if any. |
||||
453 | * |
||||
454 | * <code> |
||||
455 | * $qb = $conn->createQueryBuilder() |
||||
456 | * ->select('u.id', 'p.id') |
||||
457 | * ->from('users', 'u') |
||||
458 | * ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id'); |
||||
459 | * </code> |
||||
460 | * |
||||
461 | * @param mixed $select The selection expressions. |
||||
462 | * |
||||
463 | * @return $this This QueryBuilder instance. |
||||
464 | */ |
||||
465 | 49 | View Code Duplication | public function select($select = null) |
||
466 | { |
||||
467 | 49 | $this->type = self::SELECT; |
|||
468 | |||||
469 | 49 | if (empty($select)) { |
|||
470 | 1 | return $this; |
|||
471 | } |
||||
472 | |||||
473 | 48 | $selects = is_array($select) ? $select : func_get_args(); |
|||
474 | |||||
475 | 48 | return $this->add('select', $selects, false); |
|||
476 | } |
||||
477 | |||||
478 | /** |
||||
479 | * Adds an item that is to be returned in the query result. |
||||
480 | * |
||||
481 | * <code> |
||||
482 | * $qb = $conn->createQueryBuilder() |
||||
483 | * ->select('u.id') |
||||
484 | * ->addSelect('p.id') |
||||
485 | * ->from('users', 'u') |
||||
486 | * ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id'); |
||||
487 | * </code> |
||||
488 | * |
||||
489 | * @param mixed $select The selection expression. |
||||
490 | * |
||||
491 | * @return $this This QueryBuilder instance. |
||||
492 | */ |
||||
493 | 3 | View Code Duplication | public function addSelect($select = null) |
||
494 | { |
||||
495 | 3 | $this->type = self::SELECT; |
|||
496 | |||||
497 | 3 | if (empty($select)) { |
|||
498 | 1 | return $this; |
|||
499 | } |
||||
500 | |||||
501 | 2 | $selects = is_array($select) ? $select : func_get_args(); |
|||
502 | |||||
503 | 2 | return $this->add('select', $selects, true); |
|||
504 | } |
||||
505 | |||||
506 | /** |
||||
507 | * Turns the query being built into a bulk delete query that ranges over |
||||
508 | * a certain table. |
||||
509 | * |
||||
510 | * <code> |
||||
511 | * $qb = $conn->createQueryBuilder() |
||||
512 | * ->delete('users', 'u') |
||||
513 | * ->where('u.id = :user_id'); |
||||
514 | * ->setParameter(':user_id', 1); |
||||
515 | * </code> |
||||
516 | * |
||||
517 | * @param string $delete The table whose rows are subject to the deletion. |
||||
518 | * @param string $alias The table alias used in the constructed query. |
||||
519 | * |
||||
520 | * @return $this This QueryBuilder instance. |
||||
521 | */ |
||||
522 | 4 | View Code Duplication | public function delete($delete = null, $alias = null) |
||
523 | { |
||||
524 | 4 | $this->type = self::DELETE; |
|||
525 | |||||
526 | 4 | if ( ! $delete) { |
|||
527 | 1 | return $this; |
|||
528 | } |
||||
529 | |||||
530 | 3 | return $this->add('from', [ |
|||
0 ignored issues
–
show
Bug
introduced
by
Loading history...
|
|||||
531 | 3 | 'table' => $delete, |
|||
532 | 3 | 'alias' => $alias |
|||
533 | ]); |
||||
534 | } |
||||
535 | |||||
536 | /** |
||||
537 | * Turns the query being built into a bulk update query that ranges over |
||||
538 | * a certain table |
||||
539 | * |
||||
540 | * <code> |
||||
541 | * $qb = $conn->createQueryBuilder() |
||||
542 | * ->update('users', 'u') |
||||
543 | * ->set('u.last_login', 'NOW()') |
||||
544 | * ->where('u.id = ?'); |
||||
545 | * </code> |
||||
546 | * |
||||
547 | * @param string $update The table whose rows are subject to the update. |
||||
548 | * @param string $alias The table alias used in the constructed query. |
||||
549 | * |
||||
550 | * @return $this This QueryBuilder instance. |
||||
551 | */ |
||||
552 | 4 | View Code Duplication | public function update($update = null, $alias = null) |
||
553 | { |
||||
554 | 4 | $this->type = self::UPDATE; |
|||
555 | |||||
556 | 4 | if ( ! $update) { |
|||
557 | 1 | return $this; |
|||
558 | } |
||||
559 | |||||
560 | 3 | return $this->add('from', [ |
|||
0 ignored issues
–
show
array('table' => $update, 'alias' => $alias) of type array<string,null|string> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
561 | 3 | 'table' => $update, |
|||
562 | 3 | 'alias' => $alias |
|||
563 | ]); |
||||
564 | } |
||||
565 | |||||
566 | /** |
||||
567 | * Turns the query being built into an insert query that inserts into |
||||
568 | * a certain table |
||||
569 | * |
||||
570 | * <code> |
||||
571 | * $qb = $conn->createQueryBuilder() |
||||
572 | * ->insert('users') |
||||
573 | * ->values( |
||||
574 | * array( |
||||
575 | * 'name' => '?', |
||||
576 | * 'password' => '?' |
||||
577 | * ) |
||||
578 | * ); |
||||
579 | * </code> |
||||
580 | * |
||||
581 | * @param string $insert The table into which the rows should be inserted. |
||||
582 | * |
||||
583 | * @return $this This QueryBuilder instance. |
||||
584 | */ |
||||
585 | 5 | public function insert($insert = null) |
|||
586 | { |
||||
587 | 5 | $this->type = self::INSERT; |
|||
588 | |||||
589 | 5 | if ( ! $insert) { |
|||
590 | 1 | return $this; |
|||
591 | } |
||||
592 | |||||
593 | 4 | return $this->add('from', [ |
|||
0 ignored issues
–
show
array('table' => $insert) of type array<string,string> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
594 | 4 | 'table' => $insert |
|||
595 | ]); |
||||
596 | } |
||||
597 | |||||
598 | /** |
||||
599 | * Creates and adds a query root corresponding to the table identified by the |
||||
600 | * given alias, forming a cartesian product with any existing query roots. |
||||
601 | * |
||||
602 | * <code> |
||||
603 | * $qb = $conn->createQueryBuilder() |
||||
604 | * ->select('u.id') |
||||
605 | * ->from('users', 'u') |
||||
606 | * </code> |
||||
607 | * |
||||
608 | * @param string $from The table. |
||||
609 | * @param string|null $alias The alias of the table. |
||||
610 | * |
||||
611 | * @return $this This QueryBuilder instance. |
||||
612 | */ |
||||
613 | 47 | public function from($from, $alias = null) |
|||
614 | { |
||||
615 | 47 | return $this->add('from', [ |
|||
0 ignored issues
–
show
array('table' => $from, 'alias' => $alias) of type array<string,null|string> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
616 | 47 | 'table' => $from, |
|||
617 | 47 | 'alias' => $alias |
|||
618 | 47 | ], true); |
|||
619 | } |
||||
620 | |||||
621 | /** |
||||
622 | * Creates and adds a join to the query. |
||||
623 | * |
||||
624 | * <code> |
||||
625 | * $qb = $conn->createQueryBuilder() |
||||
626 | * ->select('u.name') |
||||
627 | * ->from('users', 'u') |
||||
628 | * ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1'); |
||||
629 | * </code> |
||||
630 | * |
||||
631 | * @param string $fromAlias The alias that points to a from clause. |
||||
632 | * @param string $join The table name to join. |
||||
633 | * @param string $alias The alias of the join table. |
||||
634 | * @param string $condition The condition for the join. |
||||
635 | * |
||||
636 | * @return $this This QueryBuilder instance. |
||||
637 | */ |
||||
638 | 4 | public function join($fromAlias, $join, $alias, $condition = null) |
|||
639 | { |
||||
640 | 4 | return $this->innerJoin($fromAlias, $join, $alias, $condition); |
|||
641 | } |
||||
642 | |||||
643 | /** |
||||
644 | * Creates and adds a join to the query. |
||||
645 | * |
||||
646 | * <code> |
||||
647 | * $qb = $conn->createQueryBuilder() |
||||
648 | * ->select('u.name') |
||||
649 | * ->from('users', 'u') |
||||
650 | * ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); |
||||
651 | * </code> |
||||
652 | * |
||||
653 | * @param string $fromAlias The alias that points to a from clause. |
||||
654 | * @param string $join The table name to join. |
||||
655 | * @param string $alias The alias of the join table. |
||||
656 | * @param string $condition The condition for the join. |
||||
657 | * |
||||
658 | * @return $this This QueryBuilder instance. |
||||
659 | */ |
||||
660 | 10 | View Code Duplication | public function innerJoin($fromAlias, $join, $alias, $condition = null) |
||
661 | { |
||||
662 | 10 | return $this->add('join', [ |
|||
0 ignored issues
–
show
array($fromAlias => arra...dition' => $condition)) of type array<string,array<string,null|string>> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
663 | $fromAlias => [ |
||||
664 | 10 | 'joinType' => 'inner', |
|||
665 | 10 | 'joinTable' => $join, |
|||
666 | 10 | 'joinAlias' => $alias, |
|||
667 | 10 | 'joinCondition' => $condition |
|||
668 | ] |
||||
669 | 10 | ], true); |
|||
670 | } |
||||
671 | |||||
672 | /** |
||||
673 | * Creates and adds a left join to the query. |
||||
674 | * |
||||
675 | * <code> |
||||
676 | * $qb = $conn->createQueryBuilder() |
||||
677 | * ->select('u.name') |
||||
678 | * ->from('users', 'u') |
||||
679 | * ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); |
||||
680 | * </code> |
||||
681 | * |
||||
682 | * @param string $fromAlias The alias that points to a from clause. |
||||
683 | * @param string $join The table name to join. |
||||
684 | * @param string $alias The alias of the join table. |
||||
685 | * @param string $condition The condition for the join. |
||||
686 | * |
||||
687 | * @return $this This QueryBuilder instance. |
||||
688 | */ |
||||
689 | 1 | View Code Duplication | public function leftJoin($fromAlias, $join, $alias, $condition = null) |
||
690 | { |
||||
691 | 1 | return $this->add('join', [ |
|||
0 ignored issues
–
show
array($fromAlias => arra...dition' => $condition)) of type array<string,array<string,null|string>> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
692 | $fromAlias => [ |
||||
693 | 1 | 'joinType' => 'left', |
|||
694 | 1 | 'joinTable' => $join, |
|||
695 | 1 | 'joinAlias' => $alias, |
|||
696 | 1 | 'joinCondition' => $condition |
|||
697 | ] |
||||
698 | 1 | ], true); |
|||
699 | } |
||||
700 | |||||
701 | /** |
||||
702 | * Creates and adds a right join to the query. |
||||
703 | * |
||||
704 | * <code> |
||||
705 | * $qb = $conn->createQueryBuilder() |
||||
706 | * ->select('u.name') |
||||
707 | * ->from('users', 'u') |
||||
708 | * ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1'); |
||||
709 | * </code> |
||||
710 | * |
||||
711 | * @param string $fromAlias The alias that points to a from clause. |
||||
712 | * @param string $join The table name to join. |
||||
713 | * @param string $alias The alias of the join table. |
||||
714 | * @param string $condition The condition for the join. |
||||
715 | * |
||||
716 | * @return $this This QueryBuilder instance. |
||||
717 | */ |
||||
718 | 1 | View Code Duplication | public function rightJoin($fromAlias, $join, $alias, $condition = null) |
||
719 | { |
||||
720 | 1 | return $this->add('join', [ |
|||
0 ignored issues
–
show
array($fromAlias => arra...dition' => $condition)) of type array<string,array<string,null|string>> is incompatible with the type string expected by parameter $sqlPart of Doctrine\DBAL\Query\QueryBuilder::add() .
(
Ignorable by Annotation
)
If this is a false-positive, you can also ignore this issue in your code via the
Loading history...
|
|||||
721 | $fromAlias => [ |
||||
722 | 1 | 'joinType' => 'right', |
|||
723 | 1 | 'joinTable' => $join, |
|||
724 | 1 | 'joinAlias' => $alias, |
|||
725 | 1 | 'joinCondition' => $condition |
|||
726 | ] |
||||
727 | 1 | ], true); |
|||
728 | } |
||||
729 | |||||
730 | /** |
||||
731 | * Sets a new value for a column in a bulk update query. |
||||
732 | * |
||||
733 | * <code> |
||||
734 | * $qb = $conn->createQueryBuilder() |
||||
735 | * ->update('users', 'u') |
||||
736 | * ->set('u.last_login', 'NOW()') |
||||
737 | * ->where('u.id = ?'); |
||||
738 | * </code> |
||||
739 | * |
||||
740 | * @param string $key The column to set. |
||||
741 | * @param string $value The value, expression, placeholder, etc. |
||||
742 | * |
||||
743 | * @return $this This QueryBuilder instance. |
||||
744 | */ |
||||
745 | 3 | public function set($key, $value) |
|||
746 | { |
||||
747 | 3 | return $this->add('set', $key .' = ' . $value, true); |
|||
748 | } |
||||
749 | |||||
750 | /** |
||||
751 | * Specifies one or more restrictions to the query result. |
||||
752 | * Replaces any previously specified restrictions, if any. |
||||
753 | * |
||||
754 | * <code> |
||||
755 | * $qb = $conn->createQueryBuilder() |
||||
756 | * ->select('u.name') |
||||
757 | * ->from('users', 'u') |
||||
758 | * ->where('u.id = ?'); |
||||
759 | * |
||||
760 | * // You can optionally programatically build and/or expressions |
||||
761 | * $qb = $conn->createQueryBuilder(); |
||||
762 | * |
||||
763 | * $or = $qb->expr()->orx(); |
||||
764 | * $or->add($qb->expr()->eq('u.id', 1)); |
||||
765 | * $or->add($qb->expr()->eq('u.id', 2)); |
||||
766 | * |
||||
767 | * $qb->update('users', 'u') |
||||
768 | * ->set('u.last_login', 'NOW()') |
||||
769 | * ->where($or); |
||||
770 | * </code> |
||||
771 | * |
||||
772 | * @param mixed $predicates The restriction predicates. |
||||
773 | * |
||||
774 | * @return $this This QueryBuilder instance. |
||||
775 | */ |
||||
776 | 19 | View Code Duplication | public function where($predicates) |
||
777 | { |
||||
778 | 19 | if ( ! (func_num_args() == 1 && $predicates instanceof CompositeExpression)) { |
|||
779 | 18 | $predicates = new CompositeExpression(CompositeExpression::TYPE_AND, func_get_args()); |
|||
780 | } |
||||
781 | |||||
782 | 19 | return $this->add('where', $predicates); |
|||
783 | } |
||||
784 | |||||
785 | /** |
||||
786 | * Adds one or more restrictions to the query results, forming a logical |
||||
787 | * conjunction with any previously specified restrictions. |
||||
788 | * |
||||
789 | * <code> |
||||
790 | * $qb = $conn->createQueryBuilder() |
||||
791 | * ->select('u') |
||||
792 | * ->from('users', 'u') |
||||
793 | * ->where('u.username LIKE ?') |
||||
794 | * ->andWhere('u.is_active = 1'); |
||||
795 | * </code> |
||||
796 | * |
||||
797 | * @param mixed $where The query restrictions. |
||||
798 | * |
||||
799 | * @return $this This QueryBuilder instance. |
||||
800 | * |
||||
801 | * @see where() |
||||
802 | */ |
||||
803 | 6 | View Code Duplication | public function andWhere($where) |
||
804 | { |
||||
805 | 6 | $args = func_get_args(); |
|||
806 | 6 | $where = $this->getQueryPart('where'); |
|||
807 | |||||
808 | 6 | if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_AND) { |
|||
809 | 6 | $where->addMultiple($args); |
|||
810 | } else { |
||||
811 | 1 | array_unshift($args, $where); |
|||
812 | 1 | $where = new CompositeExpression(CompositeExpression::TYPE_AND, $args); |
|||
813 | } |
||||
814 | |||||
815 | 6 | return $this->add('where', $where, true); |
|||
816 | } |
||||
817 | |||||
818 | /** |
||||
819 | * Adds one or more restrictions to the query results, forming a logical |
||||
820 | * disjunction with any previously specified restrictions. |
||||
821 | * |
||||
822 | * <code> |
||||
823 | * $qb = $em->createQueryBuilder() |
||||
824 | * ->select('u.name') |
||||
825 | * ->from('users', 'u') |
||||
826 | * ->where('u.id = 1') |
||||
827 | * ->orWhere('u.id = 2'); |
||||
828 | * </code> |
||||
829 | * |
||||
830 | * @param mixed $where The WHERE statement. |
||||
831 | * |
||||
832 | * @return $this This QueryBuilder instance. |
||||
833 | * |
||||
834 | * @see where() |
||||
835 | */ |
||||
836 | 3 | View Code Duplication | public function orWhere($where) |
||
837 | { |
||||
838 | 3 | $args = func_get_args(); |
|||
839 | 3 | $where = $this->getQueryPart('where'); |
|||
840 | |||||
841 | 3 | if ($where instanceof CompositeExpression && $where->getType() === CompositeExpression::TYPE_OR) { |
|||
842 | 1 | $where->addMultiple($args); |
|||
843 | } else { |
||||
844 | 3 | array_unshift($args, $where); |
|||
845 | 3 | $where = new CompositeExpression(CompositeExpression::TYPE_OR, $args); |
|||
846 | } |
||||
847 | |||||
848 | 3 | return $this->add('where', $where, true); |
|||
849 | } |
||||
850 | |||||
851 | /** |
||||
852 | * Specifies a grouping over the results of the query. |
||||
853 | * Replaces any previously specified groupings, if any. |
||||
854 | * |
||||
855 | * <code> |
||||
856 | * $qb = $conn->createQueryBuilder() |
||||
857 | * ->select('u.name') |
||||
858 | * ->from('users', 'u') |
||||
859 | * ->groupBy('u.id'); |
||||
860 | * </code> |
||||
861 | * |
||||
862 | * @param mixed $groupBy The grouping expression. |
||||
863 | * |
||||
864 | * @return $this This QueryBuilder instance. |
||||
865 | */ |
||||
866 | 10 | View Code Duplication | public function groupBy($groupBy) |
||
867 | { |
||||
868 | 10 | if (empty($groupBy)) { |
|||
869 | 1 | return $this; |
|||
870 | } |
||||
871 | |||||
872 | 9 | $groupBy = is_array($groupBy) ? $groupBy : func_get_args(); |
|||
873 | |||||
874 | 9 | return $this->add('groupBy', $groupBy, false); |
|||
875 | } |
||||
876 | |||||
877 | |||||
878 | /** |
||||
879 | * Adds a grouping expression to the query. |
||||
880 | * |
||||
881 | * <code> |
||||
882 | * $qb = $conn->createQueryBuilder() |
||||
883 | * ->select('u.name') |
||||
884 | * ->from('users', 'u') |
||||
885 | * ->groupBy('u.lastLogin'); |
||||
886 | * ->addGroupBy('u.createdAt') |
||||
887 | * </code> |
||||
888 | * |
||||
889 | * @param mixed $groupBy The grouping expression. |
||||
890 | * |
||||
891 | * @return $this This QueryBuilder instance. |
||||
892 | */ |
||||
893 | 3 | View Code Duplication | public function addGroupBy($groupBy) |
||
894 | { |
||||
895 | 3 | if (empty($groupBy)) { |
|||
896 | 1 | return $this; |
|||
897 | } |
||||
898 | |||||
899 | 2 | $groupBy = is_array($groupBy) ? $groupBy : func_get_args(); |
|||
900 | |||||
901 | 2 | return $this->add('groupBy', $groupBy, true); |
|||
902 | } |
||||
903 | |||||
904 | /** |
||||
905 | * Sets a value for a column in an insert query. |
||||
906 | * |
||||
907 | * <code> |
||||
908 | * $qb = $conn->createQueryBuilder() |
||||
909 | * ->insert('users') |
||||
910 | * ->values( |
||||
911 | * array( |
||||
912 | * 'name' => '?' |
||||
913 | * ) |
||||
914 | * ) |
||||
915 | * ->setValue('password', '?'); |
||||
916 | * </code> |
||||
917 | * |
||||
918 | * @param string $column The column into which the value should be inserted. |
||||
919 | * @param string $value The value that should be inserted into the column. |
||||
920 | * |
||||
921 | * @return $this This QueryBuilder instance. |
||||
922 | */ |
||||
923 | 2 | public function setValue($column, $value) |
|||
924 | { |
||||
925 | 2 | $this->sqlParts['values'][$column] = $value; |
|||
926 | |||||
927 | 2 | return $this; |
|||
928 | } |
||||
929 | |||||
930 | /** |
||||
931 | * Specifies values for an insert query indexed by column names. |
||||
932 | * Replaces any previous values, if any. |
||||
933 | * |
||||
934 | * <code> |
||||
935 | * $qb = $conn->createQueryBuilder() |
||||
936 | * ->insert('users') |
||||
937 | * ->values( |
||||
938 | * array( |
||||
939 | * 'name' => '?', |
||||
940 | * 'password' => '?' |
||||
941 | * ) |
||||
942 | * ); |
||||
943 | * </code> |
||||
944 | * |
||||
945 | * @param array $values The values to specify for the insert query indexed by column names. |
||||
946 | * |
||||
947 | * @return $this This QueryBuilder instance. |
||||
948 | */ |
||||
949 | 3 | public function values(array $values) |
|||
950 | { |
||||
951 | 3 | return $this->add('values', $values); |
|||
952 | } |
||||
953 | |||||
954 | /** |
||||
955 | * Specifies a restriction over the groups of the query. |
||||
956 | * Replaces any previous having restrictions, if any. |
||||
957 | * |
||||
958 | * @param mixed $having The restriction over the groups. |
||||
959 | * |
||||
960 | * @return $this This QueryBuilder instance. |
||||
961 | */ |
||||
962 | 4 | View Code Duplication | public function having($having) |
||
963 | { |
||||
964 | 4 | if ( ! (func_num_args() == 1 && $having instanceof CompositeExpression)) { |
|||
965 | 4 | $having = new CompositeExpression(CompositeExpression::TYPE_AND, func_get_args()); |
|||
966 | } |
||||
967 | |||||
968 | 4 | return $this->add('having', $having); |
|||
969 | } |
||||
970 | |||||
971 | /** |
||||
972 | * Adds a restriction over the groups of the query, forming a logical |
||||
973 | * conjunction with any existing having restrictions. |
||||
974 | * |
||||
975 | * @param mixed $having The restriction to append. |
||||
976 | * |
||||
977 | * @return $this This QueryBuilder instance. |
||||
978 | */ |
||||
979 | 3 | View Code Duplication | public function andHaving($having) |
||
980 | { |
||||
981 | 3 | $args = func_get_args(); |
|||
982 | 3 | $having = $this->getQueryPart('having'); |
|||
983 | |||||
984 | 3 | if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_AND) { |
|||
985 | 1 | $having->addMultiple($args); |
|||
986 | } else { |
||||
987 | 2 | array_unshift($args, $having); |
|||
988 | 2 | $having = new CompositeExpression(CompositeExpression::TYPE_AND, $args); |
|||
989 | } |
||||
990 | |||||
991 | 3 | return $this->add('having', $having); |
|||
992 | } |
||||
993 | |||||
994 | /** |
||||
995 | * Adds a restriction over the groups of the query, forming a logical |
||||
996 | * disjunction with any existing having restrictions. |
||||
997 | * |
||||
998 | * @param mixed $having The restriction to add. |
||||
999 | * |
||||
1000 | * @return $this This QueryBuilder instance. |
||||
1001 | */ |
||||
1002 | 3 | View Code Duplication | public function orHaving($having) |
||
1003 | { |
||||
1004 | 3 | $args = func_get_args(); |
|||
1005 | 3 | $having = $this->getQueryPart('having'); |
|||
1006 | |||||
1007 | 3 | if ($having instanceof CompositeExpression && $having->getType() === CompositeExpression::TYPE_OR) { |
|||
1008 | 1 | $having->addMultiple($args); |
|||
1009 | } else { |
||||
1010 | 3 | array_unshift($args, $having); |
|||
1011 | 3 | $having = new CompositeExpression(CompositeExpression::TYPE_OR, $args); |
|||
1012 | } |
||||
1013 | |||||
1014 | 3 | return $this->add('having', $having); |
|||
1015 | } |
||||
1016 | |||||
1017 | /** |
||||
1018 | * Specifies an ordering for the query results. |
||||
1019 | * Replaces any previously specified orderings, if any. |
||||
1020 | * |
||||
1021 | * @param string $sort The ordering expression. |
||||
1022 | * @param string $order The ordering direction. |
||||
1023 | * |
||||
1024 | * @return $this This QueryBuilder instance. |
||||
1025 | */ |
||||
1026 | 3 | public function orderBy($sort, $order = null) |
|||
1027 | { |
||||
1028 | 3 | return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), false); |
|||
1029 | } |
||||
1030 | |||||
1031 | /** |
||||
1032 | * Adds an ordering to the query results. |
||||
1033 | * |
||||
1034 | * @param string $sort The ordering expression. |
||||
1035 | * @param string $order The ordering direction. |
||||
1036 | * |
||||
1037 | * @return $this This QueryBuilder instance. |
||||
1038 | */ |
||||
1039 | 2 | public function addOrderBy($sort, $order = null) |
|||
1040 | { |
||||
1041 | 2 | return $this->add('orderBy', $sort . ' ' . (! $order ? 'ASC' : $order), true); |
|||
1042 | } |
||||
1043 | |||||
1044 | /** |
||||
1045 | * Gets a query part by its name. |
||||
1046 | * |
||||
1047 | * @param string $queryPartName |
||||
1048 | * |
||||
1049 | * @return mixed |
||||
1050 | */ |
||||
1051 | 13 | public function getQueryPart($queryPartName) |
|||
1052 | { |
||||
1053 | 13 | return $this->sqlParts[$queryPartName]; |
|||
1054 | } |
||||
1055 | |||||
1056 | /** |
||||
1057 | * Gets all query parts. |
||||
1058 | * |
||||
1059 | * @return array |
||||
1060 | */ |
||||
1061 | 1 | public function getQueryParts() |
|||
1062 | { |
||||
1063 | 1 | return $this->sqlParts; |
|||
1064 | } |
||||
1065 | |||||
1066 | /** |
||||
1067 | * Resets SQL parts. |
||||
1068 | * |
||||
1069 | * @param array|null $queryPartNames |
||||
1070 | * |
||||
1071 | * @return $this This QueryBuilder instance. |
||||
1072 | */ |
||||
1073 | 1 | public function resetQueryParts($queryPartNames = null) |
|||
1074 | { |
||||
1075 | 1 | if (is_null($queryPartNames)) { |
|||
1076 | $queryPartNames = array_keys($this->sqlParts); |
||||
1077 | } |
||||
1078 | |||||
1079 | 1 | foreach ($queryPartNames as $queryPartName) { |
|||
1080 | 1 | $this->resetQueryPart($queryPartName); |
|||
1081 | } |
||||
1082 | |||||
1083 | 1 | return $this; |
|||
1084 | } |
||||
1085 | |||||
1086 | /** |
||||
1087 | * Resets a single SQL part. |
||||
1088 | * |
||||
1089 | * @param string $queryPartName |
||||
1090 | * |
||||
1091 | * @return $this This QueryBuilder instance. |
||||
1092 | */ |
||||
1093 | 2 | public function resetQueryPart($queryPartName) |
|||
1094 | { |
||||
1095 | 2 | $this->sqlParts[$queryPartName] = is_array($this->sqlParts[$queryPartName]) |
|||
1096 | 2 | ? [] : null; |
|||
1097 | |||||
1098 | 2 | $this->state = self::STATE_DIRTY; |
|||
1099 | |||||
1100 | 2 | return $this; |
|||
1101 | } |
||||
1102 | |||||
1103 | /** |
||||
1104 | * @return string |
||||
1105 | * |
||||
1106 | * @throws \Doctrine\DBAL\Query\QueryException |
||||
1107 | */ |
||||
1108 | 46 | private function getSQLForSelect() |
|||
1109 | { |
||||
1110 | 46 | $query = 'SELECT ' . implode(', ', $this->sqlParts['select']); |
|||
1111 | |||||
1112 | 46 | $query .= ($this->sqlParts['from'] ? ' FROM ' . implode(', ', $this->getFromClauses()) : '') |
|||
1113 | 44 | . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : '') |
|||
1114 | 44 | . ($this->sqlParts['groupBy'] ? ' GROUP BY ' . implode(', ', $this->sqlParts['groupBy']) : '') |
|||
1115 | 44 | . ($this->sqlParts['having'] !== null ? ' HAVING ' . ((string) $this->sqlParts['having']) : '') |
|||
1116 | 44 | . ($this->sqlParts['orderBy'] ? ' ORDER BY ' . implode(', ', $this->sqlParts['orderBy']) : ''); |
|||
1117 | |||||
1118 | 44 | if ($this->isLimitQuery()) { |
|||
1119 | return $this->connection->getDatabasePlatform()->modifyLimitQuery( |
||||
1120 | $query, |
||||
1121 | $this->maxResults, |
||||
1122 | $this->firstResult |
||||
1123 | ); |
||||
1124 | } |
||||
1125 | |||||
1126 | 44 | return $query; |
|||
1127 | } |
||||
1128 | |||||
1129 | /** |
||||
1130 | * @return string[] |
||||
1131 | */ |
||||
1132 | 45 | private function getFromClauses() |
|||
1133 | { |
||||
1134 | 45 | $fromClauses = []; |
|||
1135 | 45 | $knownAliases = []; |
|||
1136 | |||||
1137 | // Loop through all FROM clauses |
||||
1138 | 45 | foreach ($this->sqlParts['from'] as $from) { |
|||
1139 | 45 | if ($from['alias'] === null) { |
|||
1140 | 6 | $tableSql = $from['table']; |
|||
1141 | 6 | $tableReference = $from['table']; |
|||
1142 | } else { |
||||
1143 | 40 | $tableSql = $from['table'] . ' ' . $from['alias']; |
|||
1144 | 40 | $tableReference = $from['alias']; |
|||
1145 | } |
||||
1146 | |||||
1147 | 45 | $knownAliases[$tableReference] = true; |
|||
1148 | |||||
1149 | 45 | $fromClauses[$tableReference] = $tableSql . $this->getSQLForJoins($tableReference, $knownAliases); |
|||
1150 | } |
||||
1151 | |||||
1152 | 44 | $this->verifyAllAliasesAreKnown($knownAliases); |
|||
1153 | |||||
1154 | 43 | return $fromClauses; |
|||
1155 | } |
||||
1156 | |||||
1157 | /** |
||||
1158 | * @param array $knownAliases |
||||
1159 | * |
||||
1160 | * @throws QueryException |
||||
1161 | */ |
||||
1162 | 44 | private function verifyAllAliasesAreKnown(array $knownAliases) |
|||
1163 | { |
||||
1164 | 44 | foreach ($this->sqlParts['join'] as $fromAlias => $joins) { |
|||
1165 | 11 | if ( ! isset($knownAliases[$fromAlias])) { |
|||
1166 | 11 | throw QueryException::unknownAlias($fromAlias, array_keys($knownAliases)); |
|||
1167 | } |
||||
1168 | } |
||||
1169 | 43 | } |
|||
1170 | |||||
1171 | /** |
||||
1172 | * @return bool |
||||
1173 | */ |
||||
1174 | 44 | private function isLimitQuery() |
|||
1175 | { |
||||
1176 | 44 | return $this->maxResults !== null || $this->firstResult !== null; |
|||
1177 | } |
||||
1178 | |||||
1179 | /** |
||||
1180 | * Converts this instance into an INSERT string in SQL. |
||||
1181 | * |
||||
1182 | * @return string |
||||
1183 | */ |
||||
1184 | 4 | private function getSQLForInsert() |
|||
1185 | { |
||||
1186 | 4 | return 'INSERT INTO ' . $this->sqlParts['from']['table'] . |
|||
1187 | 4 | ' (' . implode(', ', array_keys($this->sqlParts['values'])) . ')' . |
|||
1188 | 4 | ' VALUES(' . implode(', ', $this->sqlParts['values']) . ')'; |
|||
1189 | } |
||||
1190 | |||||
1191 | /** |
||||
1192 | * Converts this instance into an UPDATE string in SQL. |
||||
1193 | * |
||||
1194 | * @return string |
||||
1195 | */ |
||||
1196 | 3 | private function getSQLForUpdate() |
|||
1197 | { |
||||
1198 | 3 | $table = $this->sqlParts['from']['table'] . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : ''); |
|||
1199 | 3 | $query = 'UPDATE ' . $table |
|||
1200 | 3 | . ' SET ' . implode(", ", $this->sqlParts['set']) |
|||
1201 | 3 | . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : ''); |
|||
1202 | |||||
1203 | 3 | return $query; |
|||
1204 | } |
||||
1205 | |||||
1206 | /** |
||||
1207 | * Converts this instance into a DELETE string in SQL. |
||||
1208 | * |
||||
1209 | * @return string |
||||
1210 | */ |
||||
1211 | 3 | private function getSQLForDelete() |
|||
1212 | { |
||||
1213 | 3 | $table = $this->sqlParts['from']['table'] . ($this->sqlParts['from']['alias'] ? ' ' . $this->sqlParts['from']['alias'] : ''); |
|||
1214 | 3 | $query = 'DELETE FROM ' . $table . ($this->sqlParts['where'] !== null ? ' WHERE ' . ((string) $this->sqlParts['where']) : ''); |
|||
1215 | |||||
1216 | 3 | return $query; |
|||
1217 | } |
||||
1218 | |||||
1219 | /** |
||||
1220 | * Gets a string representation of this QueryBuilder which corresponds to |
||||
1221 | * the final SQL query being constructed. |
||||
1222 | * |
||||
1223 | * @return string The string representation of this QueryBuilder. |
||||
1224 | */ |
||||
1225 | 49 | public function __toString() |
|||
1226 | { |
||||
1227 | 49 | return $this->getSQL(); |
|||
1228 | } |
||||
1229 | |||||
1230 | /** |
||||
1231 | * Creates a new named parameter and bind the value $value to it. |
||||
1232 | * |
||||
1233 | * This method provides a shortcut for PDOStatement::bindValue |
||||
1234 | * when using prepared statements. |
||||
1235 | * |
||||
1236 | * The parameter $value specifies the value that you want to bind. If |
||||
1237 | * $placeholder is not provided bindValue() will automatically create a |
||||
1238 | * placeholder for you. An automatic placeholder will be of the name |
||||
1239 | * ':dcValue1', ':dcValue2' etc. |
||||
1240 | * |
||||
1241 | * For more information see {@link http://php.net/pdostatement-bindparam} |
||||
1242 | * |
||||
1243 | * Example: |
||||
1244 | * <code> |
||||
1245 | * $value = 2; |
||||
1246 | * $q->eq( 'id', $q->bindValue( $value ) ); |
||||
1247 | * $stmt = $q->executeQuery(); // executed with 'id = 2' |
||||
1248 | * </code> |
||||
1249 | * |
||||
1250 | * @license New BSD License |
||||
1251 | * @link http://www.zetacomponents.org |
||||
1252 | * |
||||
1253 | * @param mixed $value |
||||
1254 | * @param mixed $type |
||||
1255 | * @param string $placeHolder The name to bind with. The string must start with a colon ':'. |
||||
1256 | * |
||||
1257 | * @return string the placeholder name used. |
||||
1258 | */ |
||||
1259 | 2 | public function createNamedParameter($value, $type = \PDO::PARAM_STR, $placeHolder = null) |
|||
1260 | { |
||||
1261 | 2 | if ($placeHolder === null) { |
|||
1262 | 1 | $this->boundCounter++; |
|||
1263 | 1 | $placeHolder = ":dcValue" . $this->boundCounter; |
|||
1264 | } |
||||
1265 | 2 | $this->setParameter(substr($placeHolder, 1), $value, $type); |
|||
1266 | |||||
1267 | 2 | return $placeHolder; |
|||
1268 | } |
||||
1269 | |||||
1270 | /** |
||||
1271 | * Creates a new positional parameter and bind the given value to it. |
||||
1272 | * |
||||
1273 | * Attention: If you are using positional parameters with the query builder you have |
||||
1274 | * to be very careful to bind all parameters in the order they appear in the SQL |
||||
1275 | * statement , otherwise they get bound in the wrong order which can lead to serious |
||||
1276 | * bugs in your code. |
||||
1277 | * |
||||
1278 | * Example: |
||||
1279 | * <code> |
||||
1280 | * $qb = $conn->createQueryBuilder(); |
||||
1281 | * $qb->select('u.*') |
||||
1282 | * ->from('users', 'u') |
||||
1283 | * ->where('u.username = ' . $qb->createPositionalParameter('Foo', PDO::PARAM_STR)) |
||||
1284 | * ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', PDO::PARAM_STR)) |
||||
1285 | * </code> |
||||
1286 | * |
||||
1287 | * @param mixed $value |
||||
1288 | * @param integer $type |
||||
1289 | * |
||||
1290 | * @return string |
||||
1291 | */ |
||||
1292 | 1 | public function createPositionalParameter($value, $type = \PDO::PARAM_STR) |
|||
1293 | { |
||||
1294 | 1 | $this->boundCounter++; |
|||
1295 | 1 | $this->setParameter($this->boundCounter, $value, $type); |
|||
1296 | |||||
1297 | 1 | return "?"; |
|||
1298 | } |
||||
1299 | |||||
1300 | /** |
||||
1301 | * @param string $fromAlias |
||||
1302 | * @param array $knownAliases |
||||
1303 | * |
||||
1304 | * @return string |
||||
1305 | * |
||||
1306 | * @throws QueryException |
||||
1307 | */ |
||||
1308 | 45 | private function getSQLForJoins($fromAlias, array &$knownAliases) |
|||
1309 | { |
||||
1310 | 45 | $sql = ''; |
|||
1311 | |||||
1312 | 45 | if (isset($this->sqlParts['join'][$fromAlias])) { |
|||
1313 | 12 | foreach ($this->sqlParts['join'][$fromAlias] as $join) { |
|||
1314 | 12 | if (array_key_exists($join['joinAlias'], $knownAliases)) { |
|||
1315 | 1 | throw QueryException::nonUniqueAlias($join['joinAlias'], array_keys($knownAliases)); |
|||
1316 | } |
||||
1317 | 11 | $sql .= ' ' . strtoupper($join['joinType']) |
|||
1318 | 11 | . ' JOIN ' . $join['joinTable'] . ' ' . $join['joinAlias'] |
|||
1319 | 11 | . ' ON ' . ((string) $join['joinCondition']); |
|||
1320 | 11 | $knownAliases[$join['joinAlias']] = true; |
|||
1321 | } |
||||
1322 | |||||
1323 | 11 | foreach ($this->sqlParts['join'][$fromAlias] as $join) { |
|||
1324 | 11 | $sql .= $this->getSQLForJoins($join['joinAlias'], $knownAliases); |
|||
1325 | } |
||||
1326 | } |
||||
1327 | |||||
1328 | 44 | return $sql; |
|||
1329 | } |
||||
1330 | |||||
1331 | /** |
||||
1332 | * Deep clone of all expression objects in the SQL parts. |
||||
1333 | * |
||||
1334 | * @return void |
||||
1335 | */ |
||||
1336 | 1 | public function __clone() |
|||
1337 | { |
||||
1338 | 1 | foreach ($this->sqlParts as $part => $elements) { |
|||
1339 | 1 | if (is_array($this->sqlParts[$part])) { |
|||
1340 | 1 | foreach ($this->sqlParts[$part] as $idx => $element) { |
|||
1341 | 1 | if (is_object($element)) { |
|||
1342 | 1 | $this->sqlParts[$part][$idx] = clone $element; |
|||
1343 | } |
||||
1344 | } |
||||
1345 | 1 | } elseif (is_object($elements)) { |
|||
1346 | 1 | $this->sqlParts[$part] = clone $elements; |
|||
1347 | } |
||||
1348 | } |
||||
1349 | |||||
1350 | 1 | foreach ($this->params as $name => $param) { |
|||
1351 | 1 | if (is_object($param)) { |
|||
1352 | 1 | $this->params[$name] = clone $param; |
|||
1353 | } |
||||
1354 | } |
||||
1355 | 1 | } |
|||
1356 | } |
||||
1357 |