Namespaces

  • Latte
    • Loaders
    • Macros
    • Runtime
  • Nette
    • Application
      • Responses
      • Routers
      • UI
    • Bridges
      • ApplicationDI
      • ApplicationLatte
      • ApplicationTracy
      • CacheDI
      • CacheLatte
      • DatabaseDI
      • DatabaseTracy
      • DITracy
      • FormsDI
      • FormsLatte
      • Framework
      • HttpDI
      • HttpTracy
      • MailDI
      • ReflectionDI
      • SecurityDI
      • SecurityTracy
    • Caching
      • Storages
    • ComponentModel
    • Database
      • Conventions
      • Drivers
      • Table
    • DI
      • Config
        • Adapters
      • Extensions
    • Forms
      • Controls
      • Rendering
    • Http
    • Iterators
    • Loaders
    • Localization
    • Mail
    • Neon
    • PhpGenerator
      • Traits
    • Reflection
    • Security
    • Tokenizer
    • Utils
  • Tracy
    • Bridges
      • Nette
  • none

Classes

  • ActiveRow
  • GroupedSelection
  • Selection
  • SqlBuilder

Interfaces

  • IRow
  • IRowContainer
  • Overview
  • Namespace
  • Class
  • Tree
  • Deprecated
  1: <?php
  2: 
  3: /**
  4:  * This file is part of the Nette Framework (https://nette.org)
  5:  * Copyright (c) 2004 David Grudl (https://davidgrudl.com)
  6:  */
  7: 
  8: namespace Nette\Database\Table;
  9: 
 10: use Nette;
 11: use Nette\Database\Context;
 12: use Nette\Database\IConventions;
 13: use Nette\Database\IStructure;
 14: use Nette\Database\ISupplementalDriver;
 15: use Nette\Database\SqlLiteral;
 16: 
 17: 
 18: /**
 19:  * Builds SQL query.
 20:  * SqlBuilder is based on great library NotORM http://www.notorm.com written by Jakub Vrana.
 21:  */
 22: class SqlBuilder
 23: {
 24:     use Nette\SmartObject;
 25: 
 26:     /** @var string */
 27:     protected $tableName;
 28: 
 29:     /** @var IConventions */
 30:     protected $conventions;
 31: 
 32:     /** @var string delimited table name */
 33:     protected $delimitedTable;
 34: 
 35:     /** @var array of column to select */
 36:     protected $select = [];
 37: 
 38:     /** @var array of where conditions */
 39:     protected $where = [];
 40: 
 41:     /** @var array of array of join conditions */
 42:     protected $joinCondition = [];
 43: 
 44:     /** @var array of where conditions for caching */
 45:     protected $conditions = [];
 46: 
 47:     /** @var array of parameters passed to where conditions */
 48:     protected $parameters = [
 49:         'select' => [],
 50:         'joinCondition' => [],
 51:         'where' => [],
 52:         'group' => [],
 53:         'having' => [],
 54:         'order' => [],
 55:     ];
 56: 
 57:     /** @var array or columns to order by */
 58:     protected $order = [];
 59: 
 60:     /** @var int number of rows to fetch */
 61:     protected $limit;
 62: 
 63:     /** @var int first row to fetch */
 64:     protected $offset;
 65: 
 66:     /** @var string columns to grouping */
 67:     protected $group = '';
 68: 
 69:     /** @var string grouping condition */
 70:     protected $having = '';
 71: 
 72:     /** @var array of reserved table names associated with chain */
 73:     protected $reservedTableNames = [];
 74: 
 75:     /** @var array of table aliases */
 76:     protected $aliases = [];
 77: 
 78:     /** @var string currently parsing alias for joins */
 79:     protected $currentAlias;
 80: 
 81:     /** @var ISupplementalDriver */
 82:     private $driver;
 83: 
 84:     /** @var IStructure */
 85:     private $structure;
 86: 
 87:     /** @var array */
 88:     private $cacheTableList;
 89: 
 90:     /** @var array of expanding joins */
 91:     private $expandingJoins = [];
 92: 
 93: 
 94:     public function __construct($tableName, Context $context)
 95:     {
 96:         $this->tableName = $tableName;
 97:         $this->driver = $context->getConnection()->getSupplementalDriver();
 98:         $this->conventions = $context->getConventions();
 99:         $this->structure = $context->getStructure();
100:         $tableNameParts = explode('.', $tableName);
101:         $this->delimitedTable = implode('.', array_map([$this->driver, 'delimite'], $tableNameParts));
102:         $this->checkUniqueTableName(end($tableNameParts), $tableName);
103:     }
104: 
105: 
106:     /**
107:      * @return string
108:      */
109:     public function getTableName()
110:     {
111:         return $this->tableName;
112:     }
113: 
114: 
115:     /**
116:      * @param  string
117:      */
118:     public function buildInsertQuery()
119:     {
120:         return "INSERT INTO {$this->delimitedTable}";
121:     }
122: 
123: 
124:     /**
125:      * @param  string
126:      */
127:     public function buildUpdateQuery()
128:     {
129:         $query = "UPDATE {$this->delimitedTable} SET ?set" . $this->tryDelimite($this->buildConditions());
130: 
131:         if ($this->order !== []) {
132:             $query .= ' ORDER BY ' . implode(', ', $this->order);
133:         }
134: 
135:         if ($this->limit !== null || $this->offset) {
136:             $this->driver->applyLimit($query, $this->limit, $this->offset);
137:         }
138: 
139:         return $query;
140:     }
141: 
142: 
143:     /**
144:      * @param  string
145:      */
146:     public function buildDeleteQuery()
147:     {
148:         $query = "DELETE FROM {$this->delimitedTable}" . $this->tryDelimite($this->buildConditions());
149:         if ($this->limit !== null || $this->offset) {
150:             $this->driver->applyLimit($query, $this->limit, $this->offset);
151:         }
152: 
153:         return $query;
154:     }
155: 
156: 
157:     /**
158:      * Returns select query hash for caching.
159:      * @return string
160:      */
161:     public function getSelectQueryHash(array $columns = null)
162:     {
163:         $parts = [
164:             'delimitedTable' => $this->delimitedTable,
165:             'queryCondition' => $this->buildConditions(),
166:             'queryEnd' => $this->buildQueryEnd(),
167:             $this->aliases,
168:             $this->limit, $this->offset,
169:         ];
170:         if ($this->select) {
171:             $parts[] = $this->select;
172:         } elseif ($columns) {
173:             $parts[] = [$this->delimitedTable, $columns];
174:         } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
175:             $parts[] = [$this->group];
176:         } else {
177:             $parts[] = "{$this->delimitedTable}.*";
178:         }
179:         return $this->getConditionHash(json_encode($parts), [
180:             $this->parameters['select'],
181:             $this->parameters['joinCondition'],
182:             $this->parameters['where'],
183:             $this->parameters['group'],
184:             $this->parameters['having'],
185:             $this->parameters['order'],
186:         ]);
187:     }
188: 
189: 
190:     /**
191:      * Returns SQL query.
192:      * @param  string[] list of columns
193:      * @return string
194:      */
195:     public function buildSelectQuery(array $columns = null)
196:     {
197:         if (!$this->order && ($this->limit !== null || $this->offset)) {
198:             $this->order = array_map(
199:                 function ($col) { return "$this->tableName.$col"; },
200:                 (array) $this->conventions->getPrimary($this->tableName)
201:             );
202:         }
203: 
204:         $queryJoinConditions = $this->buildJoinConditions();
205:         $queryCondition = $this->buildConditions();
206:         $queryEnd = $this->buildQueryEnd();
207: 
208:         $joins = [];
209:         $finalJoinConditions = $this->parseJoinConditions($joins, $queryJoinConditions);
210:         $this->parseJoins($joins, $queryCondition);
211:         $this->parseJoins($joins, $queryEnd);
212: 
213:         if ($this->select) {
214:             $querySelect = $this->buildSelect($this->select);
215:             $this->parseJoins($joins, $querySelect);
216: 
217:         } elseif ($columns) {
218:             $prefix = $joins ? "{$this->delimitedTable}." : '';
219:             $cols = [];
220:             foreach ($columns as $col) {
221:                 $cols[] = $prefix . $col;
222:             }
223:             $querySelect = $this->buildSelect($cols);
224: 
225:         } elseif ($this->group && !$this->driver->isSupported(ISupplementalDriver::SUPPORT_SELECT_UNGROUPED_COLUMNS)) {
226:             $querySelect = $this->buildSelect([$this->group]);
227:             $this->parseJoins($joins, $querySelect);
228: 
229:         } else {
230:             $prefix = $joins ? "{$this->delimitedTable}." : '';
231:             $querySelect = $this->buildSelect([$prefix . '*']);
232:         }
233: 
234:         $queryJoins = $this->buildQueryJoins($joins, $finalJoinConditions);
235:         $query = "{$querySelect} FROM {$this->delimitedTable}{$queryJoins}{$queryCondition}{$queryEnd}";
236: 
237:         $this->driver->applyLimit($query, $this->limit, $this->offset);
238: 
239:         return $this->tryDelimite($query);
240:     }
241: 
242: 
243:     /**
244:      * @return array
245:      */
246:     public function getParameters()
247:     {
248:         if (!isset($this->parameters['joinConditionSorted'])) {
249:             $this->buildSelectQuery();
250:         }
251:         return array_merge(
252:             $this->parameters['select'],
253:             $this->parameters['joinConditionSorted'] ? call_user_func_array('array_merge', $this->parameters['joinConditionSorted']) : [],
254:             $this->parameters['where'],
255:             $this->parameters['group'],
256:             $this->parameters['having'],
257:             $this->parameters['order']
258:         );
259:     }
260: 
261: 
262:     public function importConditions(self $builder)
263:     {
264:         $this->where = $builder->where;
265:         $this->joinCondition = $builder->joinCondition;
266:         $this->parameters['where'] = $builder->parameters['where'];
267:         $this->parameters['joinCondition'] = $builder->parameters['joinCondition'];
268:         $this->conditions = $builder->conditions;
269:         $this->aliases = $builder->aliases;
270:         $this->reservedTableNames = $builder->reservedTableNames;
271:     }
272: 
273: 
274:     /********************* SQL selectors ****************d*g**/
275: 
276: 
277:     public function addSelect($columns, ...$params)
278:     {
279:         if (is_array($columns)) {
280:             throw new Nette\InvalidArgumentException('Select column must be a string.');
281:         }
282:         $this->select[] = $columns;
283:         $this->parameters['select'] = array_merge($this->parameters['select'], $params);
284:     }
285: 
286: 
287:     /**
288:      * @return array
289:      */
290:     public function getSelect()
291:     {
292:         return $this->select;
293:     }
294: 
295: 
296:     public function resetSelect()
297:     {
298:         $this->select = [];
299:         $this->parameters['select'] = [];
300:     }
301: 
302: 
303:     /**
304:      * @return bool
305:      */
306:     public function addWhere($condition, ...$params)
307:     {
308:         return $this->addCondition($condition, $params, $this->where, $this->parameters['where']);
309:     }
310: 
311: 
312:     /**
313:      * @return array
314:      */
315:     public function addJoinCondition($tableChain, $condition, ...$params)
316:     {
317:         $this->parameters['joinConditionSorted'] = null;
318:         if (!isset($this->joinCondition[$tableChain])) {
319:             $this->joinCondition[$tableChain] = $this->parameters['joinCondition'][$tableChain] = [];
320:         }
321:         return $this->addCondition($condition, $params, $this->joinCondition[$tableChain], $this->parameters['joinCondition'][$tableChain]);
322:     }
323: 
324: 
325:     /**
326:      * @return bool
327:      */
328:     protected function addCondition($condition, array $params, array &$conditions, array &$conditionsParameters)
329:     {
330:         if (is_array($condition) && !empty($params[0]) && is_array($params[0])) {
331:             return $this->addConditionComposition($condition, $params[0], $conditions, $conditionsParameters);
332:         }
333: 
334:         $hash = $this->getConditionHash($condition, $params);
335:         if (isset($this->conditions[$hash])) {
336:             return false;
337:         }
338: 
339:         $this->conditions[$hash] = $condition;
340:         $placeholderCount = substr_count($condition, '?');
341:         if ($placeholderCount > 1 && count($params) === 1 && is_array($params[0])) {
342:             $params = $params[0];
343:         }
344: 
345:         $condition = trim($condition);
346:         if ($placeholderCount === 0 && count($params) === 1) {
347:             $condition .= ' ?';
348:         } elseif ($placeholderCount !== count($params)) {
349:             throw new Nette\InvalidArgumentException('Argument count does not match placeholder count.');
350:         }
351: 
352:         $replace = null;
353:         $placeholderNum = 0;
354:         foreach ($params as $arg) {
355:             preg_match('#(?:.*?\?.*?){' . $placeholderNum . '}(((?:&|\||^|~|\+|-|\*|/|%|\(|,|<|>|=|(?<=\W|^)(?:REGEXP|ALL|AND|ANY|BETWEEN|EXISTS|IN|[IR]?LIKE|OR|NOT|SOME|INTERVAL))\s*)?(?:\(\?\)|\?))#s', $condition, $match, PREG_OFFSET_CAPTURE);
356:             $hasOperator = ($match[1][0] === '?' && $match[1][1] === 0) ? true : !empty($match[2][0]);
357: 
358:             if ($arg === null) {
359:                 $replace = 'IS NULL';
360:                 if ($hasOperator) {
361:                     if (trim($match[2][0]) === 'NOT') {
362:                         $replace = 'IS NOT NULL';
363:                     } else {
364:                         throw new Nette\InvalidArgumentException('Column operator does not accept null argument.');
365:                     }
366:                 }
367:             } elseif (is_array($arg) || $arg instanceof Selection) {
368:                 if ($hasOperator) {
369:                     if (trim($match[2][0]) === 'NOT') {
370:                         $match[2][0] = rtrim($match[2][0]) . ' IN ';
371:                     } elseif (trim($match[2][0]) !== 'IN') {
372:                         throw new Nette\InvalidArgumentException('Column operator does not accept array argument.');
373:                     }
374:                 } else {
375:                     $match[2][0] = 'IN ';
376:                 }
377: 
378:                 if ($arg instanceof Selection) {
379:                     $clone = clone $arg;
380:                     if (!$clone->getSqlBuilder()->select) {
381:                         try {
382:                             $clone->select($clone->getPrimary());
383:                         } catch (\LogicException $e) {
384:                             throw new Nette\InvalidArgumentException('Selection argument must have defined a select column.', 0, $e);
385:                         }
386:                     }
387: 
388:                     if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SUBSELECT)) {
389:                         $arg = null;
390:                         $replace = $match[2][0] . '(' . $clone->getSql() . ')';
391:                         $conditionsParameters = array_merge($conditionsParameters, $clone->getSqlBuilder()->getParameters());
392:                     } else {
393:                         $arg = [];
394:                         foreach ($clone as $row) {
395:                             $arg[] = array_values(iterator_to_array($row));
396:                         }
397:                     }
398:                 }
399: 
400:                 if ($arg !== null) {
401:                     if (!$arg) {
402:                         $hasBrackets = strpos($condition, '(') !== false;
403:                         $hasOperators = preg_match('#AND|OR#', $condition);
404:                         $hasNot = strpos($condition, 'NOT') !== false;
405:                         $hasPrefixNot = strpos($match[2][0], 'NOT') !== false;
406:                         if (!$hasBrackets && ($hasOperators || ($hasNot && !$hasPrefixNot))) {
407:                             throw new Nette\InvalidArgumentException('Possible SQL query corruption. Add parentheses around operators.');
408:                         }
409:                         if ($hasPrefixNot) {
410:                             $replace = 'IS NULL OR TRUE';
411:                         } else {
412:                             $replace = 'IS NULL AND FALSE';
413:                         }
414:                         $arg = null;
415:                     } else {
416:                         $replace = $match[2][0] . '(?)';
417:                         $conditionsParameters[] = $arg;
418:                     }
419:                 }
420:             } elseif ($arg instanceof SqlLiteral) {
421:                 $conditionsParameters[] = $arg;
422:             } else {
423:                 if (!$hasOperator) {
424:                     $replace = '= ?';
425:                 }
426:                 $conditionsParameters[] = $arg;
427:             }
428: 
429:             if ($replace) {
430:                 $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
431:                 $replace = null;
432:             }
433: 
434:             if ($arg !== null) {
435:                 $placeholderNum++;
436:             }
437:         }
438: 
439:         $conditions[] = $condition;
440:         return true;
441:     }
442: 
443: 
444:     /**
445:      * @return array
446:      */
447:     public function getConditions()
448:     {
449:         return array_values($this->conditions);
450:     }
451: 
452: 
453:     /**
454:      * Adds alias.
455:      * @param  string
456:      * @param  string
457:      * @return void
458:      */
459:     public function addAlias($chain, $alias)
460:     {
461:         if (isset($chain[0]) && $chain[0] !== '.' && $chain[0] !== ':') {
462:             $chain = '.' . $chain; // unified chain format
463:         }
464:         $this->checkUniqueTableName($alias, $chain);
465:         $this->aliases[$alias] = $chain;
466:     }
467: 
468: 
469:     /**
470:      * @param  string
471:      * @param  string
472:      * @return void
473:      */
474:     protected function checkUniqueTableName($tableName, $chain)
475:     {
476:         if (isset($this->aliases[$tableName]) && ($chain === '.' . $tableName)) {
477:             $chain = $this->aliases[$tableName];
478:         }
479:         if (isset($this->reservedTableNames[$tableName])) {
480:             if ($this->reservedTableNames[$tableName] === $chain) {
481:                 return;
482:             }
483:             throw new Nette\InvalidArgumentException("Table alias '$tableName' from chain '$chain' is already in use by chain '{$this->reservedTableNames[$tableName]}'. Please add/change alias for one of them.");
484:         }
485:         $this->reservedTableNames[$tableName] = $chain;
486:     }
487: 
488: 
489:     public function addOrder($columns, ...$params)
490:     {
491:         $this->order[] = $columns;
492:         $this->parameters['order'] = array_merge($this->parameters['order'], $params);
493:     }
494: 
495: 
496:     public function setOrder(array $columns, array $parameters)
497:     {
498:         $this->order = $columns;
499:         $this->parameters['order'] = $parameters;
500:     }
501: 
502: 
503:     /**
504:      * @return array
505:      */
506:     public function getOrder()
507:     {
508:         return $this->order;
509:     }
510: 
511: 
512:     /**
513:      * @param  int|null
514:      * @param  int|null
515:      * @return void
516:      */
517:     public function setLimit($limit, $offset)
518:     {
519:         $this->limit = $limit;
520:         $this->offset = $offset;
521:     }
522: 
523: 
524:     /**
525:      * @return int|null
526:      */
527:     public function getLimit()
528:     {
529:         return $this->limit;
530:     }
531: 
532: 
533:     /**
534:      * @return int|null
535:      */
536:     public function getOffset()
537:     {
538:         return $this->offset;
539:     }
540: 
541: 
542:     public function setGroup($columns, ...$params)
543:     {
544:         $this->group = $columns;
545:         $this->parameters['group'] = $params;
546:     }
547: 
548: 
549:     /**
550:      * @return string
551:      */
552:     public function getGroup()
553:     {
554:         return $this->group;
555:     }
556: 
557: 
558:     public function setHaving($having, ...$params)
559:     {
560:         $this->having = $having;
561:         $this->parameters['having'] = $params;
562:     }
563: 
564: 
565:     /**
566:      * @return string
567:      */
568:     public function getHaving()
569:     {
570:         return $this->having;
571:     }
572: 
573: 
574:     /********************* SQL building ****************d*g**/
575: 
576: 
577:     /**
578:      * @return string
579:      */
580:     protected function buildSelect(array $columns)
581:     {
582:         return 'SELECT ' . implode(', ', $columns);
583:     }
584: 
585: 
586:     /**
587:      * @return array
588:      */
589:     protected function parseJoinConditions(&$joins, $joinConditions)
590:     {
591:         $tableJoins = $leftJoinDependency = $finalJoinConditions = [];
592:         foreach ($joinConditions as $tableChain => &$joinCondition) {
593:             $fooQuery = $tableChain . '.foo';
594:             $requiredJoins = [];
595:             $this->parseJoins($requiredJoins, $fooQuery);
596:             $tableAlias = substr($fooQuery, 0, -4);
597:             $tableJoins[$tableAlias] = $requiredJoins;
598:             $leftJoinDependency[$tableAlias] = [];
599:             $finalJoinConditions[$tableAlias] = preg_replace_callback($this->getColumnChainsRegxp(), function (array $match) use ($tableAlias, &$tableJoins, &$leftJoinDependency) {
600:                 $requiredJoins = [];
601:                 $query = $this->parseJoinsCb($requiredJoins, $match);
602:                 $queryParts = explode('.', $query);
603:                 $tableJoins[$queryParts[0]] = $requiredJoins;
604:                 if ($queryParts[0] !== $tableAlias) {
605:                     foreach (array_keys($requiredJoins) as $requiredTable) {
606:                         $leftJoinDependency[$tableAlias][$requiredTable] = $requiredTable;
607:                     }
608:                 }
609:                 return $query;
610:             }, $joinCondition);
611:         }
612:         $this->parameters['joinConditionSorted'] = [];
613:         if (count($joinConditions)) {
614:             while (reset($tableJoins)) {
615:                 $this->getSortedJoins(key($tableJoins), $leftJoinDependency, $tableJoins, $joins);
616:             }
617:         }
618:         return $finalJoinConditions;
619:     }
620: 
621: 
622:     protected function getSortedJoins($table, &$leftJoinDependency, &$tableJoins, &$finalJoins)
623:     {
624:         if (isset($this->expandingJoins[$table])) {
625:             $path = implode("' => '", array_map(function ($value) { return $this->reservedTableNames[$value]; }, array_merge(array_keys($this->expandingJoins), [$table])));
626:             throw new Nette\InvalidArgumentException("Circular reference detected at left join conditions (tables '$path').");
627:         }
628:         if (isset($tableJoins[$table])) {
629:             $this->expandingJoins[$table] = true;
630:             if (isset($leftJoinDependency[$table])) {
631:                 foreach ($leftJoinDependency[$table] as $requiredTable) {
632:                     if ($requiredTable === $table) {
633:                         continue;
634:                     }
635:                     $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
636:                 }
637:             }
638:             if ($tableJoins[$table]) {
639:                 foreach ($tableJoins[$table] as $requiredTable => $tmp) {
640:                     if ($requiredTable === $table) {
641:                         continue;
642:                     }
643:                     $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
644:                 }
645:             }
646:             $finalJoins += $tableJoins[$table];
647:             $key = isset($this->aliases[$table]) ? $table : $this->reservedTableNames[$table];
648:             $this->parameters['joinConditionSorted'] += isset($this->parameters['joinCondition'][$key])
649:                 ? [$table => $this->parameters['joinCondition'][$key]]
650:                 : [];
651:             unset($tableJoins[$table], $this->expandingJoins[$table]);
652:         }
653:     }
654: 
655: 
656:     protected function parseJoins(&$joins, &$query)
657:     {
658:         $query = preg_replace_callback($this->getColumnChainsRegxp(), function (array $match) use (&$joins) {
659:             return $this->parseJoinsCb($joins, $match);
660:         }, $query);
661:     }
662: 
663: 
664:     /**
665:      * @return string
666:      */
667:     private function getColumnChainsRegxp()
668:     {
669:         return '~
670:             (?P<chain> (?!\.) (?: [.:]? (?>[\w_]*[a-z][\w_]*) (\([\w_]*[a-z][\w_]*\))? ) *)  \. (?P<column> (?>[\w_]*[a-z][\w_]*) | \*  )
671:         ~xi';
672:     }
673: 
674: 
675:     /**
676:      * @return string
677:      */
678:     public function parseJoinsCb(&$joins, $match)
679:     {
680:         $chain = $match['chain'];
681:         if (!empty($chain[0]) && ($chain[0] !== '.' && $chain[0] !== ':')) {
682:             $chain = '.' . $chain;  // unified chain format
683:         }
684: 
685:         preg_match_all('~
686:             (?P<del> [.:])?(?P<key> [\w_]*[a-z][\w_]* )(\((?P<throughColumn> [\w_]*[a-z][\w_]* )\))?
687:         ~xi', $chain, $keyMatches, PREG_SET_ORDER);
688: 
689:         $parent = $this->tableName;
690:         $parentAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $this->tableName);
691: 
692:         // join schema keyMatch and table keyMatch to schema.table keyMatch
693:         if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SCHEMA) && count($keyMatches) > 1) {
694:             $tables = $this->getCachedTableList();
695:             if (!isset($tables[$keyMatches[0]['key']]) && isset($tables[$keyMatches[0]['key'] . '.' . $keyMatches[1]['key']])) {
696:                 $keyMatch = array_shift($keyMatches);
697:                 $keyMatches[0]['key'] = $keyMatch['key'] . '.' . $keyMatches[0]['key'];
698:                 $keyMatches[0]['del'] = $keyMatch['del'];
699:             }
700:         }
701: 
702:         // do not make a join when referencing to the current table column - inner conditions
703:         // check it only when not making backjoin on itself - outer condition
704:         if ($keyMatches[0]['del'] === '.') {
705:             if (count($keyMatches) > 1 && ($parent === $keyMatches[0]['key'] || $parentAlias === $keyMatches[0]['key'])) {
706:                 throw new Nette\InvalidArgumentException("Do not prefix table chain with origin table name '{$keyMatches[0]['key']}'. If you want to make self reference, please add alias.");
707:             }
708:             if ($parent === $keyMatches[0]['key']) {
709:                 return "{$parent}.{$match['column']}";
710:             } elseif ($parentAlias === $keyMatches[0]['key']) {
711:                 return "{$parentAlias}.{$match['column']}";
712:             }
713:         }
714:         $tableChain = null;
715:         foreach ($keyMatches as $index => $keyMatch) {
716:             $isLast = !isset($keyMatches[$index + 1]);
717:             if (!$index && isset($this->aliases[$keyMatch['key']])) {
718:                 if ($keyMatch['del'] === ':') {
719:                     throw new Nette\InvalidArgumentException("You are using has many syntax with alias (':{$keyMatch['key']}'). You have to move it to alias definition.");
720:                 } else {
721:                     $previousAlias = $this->currentAlias;
722:                     $this->currentAlias = $keyMatch['key'];
723:                     $requiredJoins = [];
724:                     $query = $this->aliases[$keyMatch['key']] . '.foo';
725:                     $this->parseJoins($requiredJoins, $query);
726:                     $aliasJoin = array_pop($requiredJoins);
727:                     $joins += $requiredJoins;
728:                     list($table, , $parentAlias, $column, $primary) = $aliasJoin;
729:                     $this->currentAlias = $previousAlias;
730:                 }
731:             } elseif ($keyMatch['del'] === ':') {
732:                 if (isset($keyMatch['throughColumn'])) {
733:                     $table = $keyMatch['key'];
734:                     $belongsTo = $this->conventions->getBelongsToReference($table, $keyMatch['throughColumn']);
735:                     if (!$belongsTo) {
736:                         throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
737:                     }
738:                     list(, $primary) = $belongsTo;
739: 
740:                 } else {
741:                     $hasMany = $this->conventions->getHasManyReference($parent, $keyMatch['key']);
742:                     if (!$hasMany) {
743:                         throw new Nette\InvalidArgumentException("No reference found for \${$parent}->related({$keyMatch['key']}).");
744:                     }
745:                     list($table, $primary) = $hasMany;
746:                 }
747:                 $column = $this->conventions->getPrimary($parent);
748: 
749:             } else {
750:                 $belongsTo = $this->conventions->getBelongsToReference($parent, $keyMatch['key']);
751:                 if (!$belongsTo) {
752:                     throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
753:                 }
754:                 list($table, $column) = $belongsTo;
755:                 $primary = $this->conventions->getPrimary($table);
756:             }
757: 
758:             if ($this->currentAlias && $isLast) {
759:                 $tableAlias = $this->currentAlias;
760:             } elseif ($parent === $table) {
761:                 $tableAlias = $parentAlias . '_ref';
762:             } elseif ($keyMatch['key']) {
763:                 $tableAlias = $keyMatch['key'];
764:             } else {
765:                 $tableAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $table);
766:             }
767: 
768:             $tableChain .= $keyMatch[0];
769:             if (!$isLast || !$this->currentAlias) {
770:                 $this->checkUniqueTableName($tableAlias, $tableChain);
771:             }
772:             $joins[$tableAlias] = [$table, $tableAlias, $parentAlias, $column, $primary];
773:             $parent = $table;
774:             $parentAlias = $tableAlias;
775:         }
776: 
777:         return $tableAlias . ".{$match['column']}";
778:     }
779: 
780: 
781:     /**
782:      * @return string
783:      */
784:     protected function buildQueryJoins(array $joins, array $leftJoinConditions = [])
785:     {
786:         $return = '';
787:         foreach ($joins as list($joinTable, $joinAlias, $table, $tableColumn, $joinColumn)) {
788:             $return .=
789:                 " LEFT JOIN {$joinTable}" . ($joinTable !== $joinAlias ? " {$joinAlias}" : '') .
790:                 " ON {$table}.{$tableColumn} = {$joinAlias}.{$joinColumn}" .
791:                 (isset($leftJoinConditions[$joinAlias]) ? " {$leftJoinConditions[$joinAlias]}" : '');
792:         }
793:         return $return;
794:     }
795: 
796: 
797:     /**
798:      * @return array
799:      */
800:     protected function buildJoinConditions()
801:     {
802:         $conditions = [];
803:         foreach ($this->joinCondition as $tableChain => $joinConditions) {
804:             $conditions[$tableChain] = 'AND (' . implode(') AND (', $joinConditions) . ')';
805:         }
806:         return $conditions;
807:     }
808: 
809: 
810:     /**
811:      * @return string
812:      */
813:     protected function buildConditions()
814:     {
815:         return $this->where ? ' WHERE (' . implode(') AND (', $this->where) . ')' : '';
816:     }
817: 
818: 
819:     /**
820:      * @return string
821:      */
822:     protected function buildQueryEnd()
823:     {
824:         $return = '';
825:         if ($this->group) {
826:             $return .= ' GROUP BY ' . $this->group;
827:         }
828:         if ($this->having) {
829:             $return .= ' HAVING ' . $this->having;
830:         }
831:         if ($this->order) {
832:             $return .= ' ORDER BY ' . implode(', ', $this->order);
833:         }
834:         return $return;
835:     }
836: 
837: 
838:     /**
839:      * @return string
840:      */
841:     protected function tryDelimite($s)
842:     {
843:         return preg_replace_callback('#(?<=[^\w`"\[?]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|\z)#i', function ($m) {
844:             return strtoupper($m[0]) === $m[0] ? $m[0] : $this->driver->delimite($m[0]);
845:         }, $s);
846:     }
847: 
848: 
849:     /**
850:      * @return bool
851:      */
852:     protected function addConditionComposition(array $columns, array $parameters, array &$conditions, array &$conditionsParameters)
853:     {
854:         if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_COLUMN_AS_OR_COND)) {
855:             $conditionFragment = '(' . implode(' = ? AND ', $columns) . ' = ?) OR ';
856:             $condition = substr(str_repeat($conditionFragment, count($parameters)), 0, -4);
857:             return $this->addCondition($condition, [Nette\Utils\Arrays::flatten($parameters)], $conditions, $conditionsParameters);
858:         } else {
859:             return $this->addCondition('(' . implode(', ', $columns) . ') IN', [$parameters], $conditions, $conditionsParameters);
860:         }
861:     }
862: 
863: 
864:     /**
865:      * @return string
866:      */
867:     private function getConditionHash($condition, array $parameters)
868:     {
869:         foreach ($parameters as $key => &$parameter) {
870:             if ($parameter instanceof Selection) {
871:                 $parameter = $this->getConditionHash($parameter->getSql(), $parameter->getSqlBuilder()->getParameters());
872:             } elseif ($parameter instanceof SqlLiteral) {
873:                 $parameter = $this->getConditionHash($parameter->__toString(), $parameter->getParameters());
874:             } elseif (is_object($parameter) && method_exists($parameter, '__toString')) {
875:                 $parameter = $parameter->__toString();
876:             } elseif (is_array($parameter) || $parameter instanceof \ArrayAccess) {
877:                 $parameter = $this->getConditionHash($key, $parameter);
878:             }
879:         }
880:         return md5($condition . json_encode($parameters));
881:     }
882: 
883: 
884:     /**
885:      * @return array
886:      */
887:     private function getCachedTableList()
888:     {
889:         if (!$this->cacheTableList) {
890:             $this->cacheTableList = array_flip(array_map(function (array $pair) {
891:                 return isset($pair['fullName']) ? $pair['fullName'] : $pair['name'];
892:             }, $this->structure->getTables()));
893:         }
894: 
895:         return $this->cacheTableList;
896:     }
897: }
898: 
Nette 2.4-20191120 API API documentation generated by ApiGen 2.8.0