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:     /**
297:      * @return bool
298:      */
299:     public function addWhere($condition, ...$params)
300:     {
301:         return $this->addCondition($condition, $params, $this->where, $this->parameters['where']);
302:     }
303: 
304: 
305:     /**
306:      * @return array
307:      */
308:     public function addJoinCondition($tableChain, $condition, ...$params)
309:     {
310:         $this->parameters['joinConditionSorted'] = null;
311:         if (!isset($this->joinCondition[$tableChain])) {
312:             $this->joinCondition[$tableChain] = $this->parameters['joinCondition'][$tableChain] = [];
313:         }
314:         return $this->addCondition($condition, $params, $this->joinCondition[$tableChain], $this->parameters['joinCondition'][$tableChain]);
315:     }
316: 
317: 
318:     /**
319:      * @return bool
320:      */
321:     protected function addCondition($condition, array $params, array &$conditions, array &$conditionsParameters)
322:     {
323:         if (is_array($condition) && !empty($params[0]) && is_array($params[0])) {
324:             return $this->addConditionComposition($condition, $params[0], $conditions, $conditionsParameters);
325:         }
326: 
327:         $hash = $this->getConditionHash($condition, $params);
328:         if (isset($this->conditions[$hash])) {
329:             return false;
330:         }
331: 
332:         $this->conditions[$hash] = $condition;
333:         $placeholderCount = substr_count($condition, '?');
334:         if ($placeholderCount > 1 && count($params) === 1 && is_array($params[0])) {
335:             $params = $params[0];
336:         }
337: 
338:         $condition = trim($condition);
339:         if ($placeholderCount === 0 && count($params) === 1) {
340:             $condition .= ' ?';
341:         } elseif ($placeholderCount !== count($params)) {
342:             throw new Nette\InvalidArgumentException('Argument count does not match placeholder count.');
343:         }
344: 
345:         $replace = null;
346:         $placeholderNum = 0;
347:         foreach ($params as $arg) {
348:             preg_match('#(?:.*?\?.*?){' . $placeholderNum . '}(((?:&|\||^|~|\+|-|\*|/|%|\(|,|<|>|=|(?<=\W|^)(?:REGEXP|ALL|AND|ANY|BETWEEN|EXISTS|IN|[IR]?LIKE|OR|NOT|SOME|INTERVAL))\s*)?(?:\(\?\)|\?))#s', $condition, $match, PREG_OFFSET_CAPTURE);
349:             $hasOperator = ($match[1][0] === '?' && $match[1][1] === 0) ? true : !empty($match[2][0]);
350: 
351:             if ($arg === null) {
352:                 $replace = 'IS NULL';
353:                 if ($hasOperator) {
354:                     if (trim($match[2][0]) === 'NOT') {
355:                         $replace = 'IS NOT NULL';
356:                     } else {
357:                         throw new Nette\InvalidArgumentException('Column operator does not accept null argument.');
358:                     }
359:                 }
360:             } elseif (is_array($arg) || $arg instanceof Selection) {
361:                 if ($hasOperator) {
362:                     if (trim($match[2][0]) === 'NOT') {
363:                         $match[2][0] = rtrim($match[2][0]) . ' IN ';
364:                     } elseif (trim($match[2][0]) !== 'IN') {
365:                         throw new Nette\InvalidArgumentException('Column operator does not accept array argument.');
366:                     }
367:                 } else {
368:                     $match[2][0] = 'IN ';
369:                 }
370: 
371:                 if ($arg instanceof Selection) {
372:                     $clone = clone $arg;
373:                     if (!$clone->getSqlBuilder()->select) {
374:                         try {
375:                             $clone->select($clone->getPrimary());
376:                         } catch (\LogicException $e) {
377:                             throw new Nette\InvalidArgumentException('Selection argument must have defined a select column.', 0, $e);
378:                         }
379:                     }
380: 
381:                     if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SUBSELECT)) {
382:                         $arg = null;
383:                         $replace = $match[2][0] . '(' . $clone->getSql() . ')';
384:                         $conditionsParameters = array_merge($conditionsParameters, $clone->getSqlBuilder()->getParameters());
385:                     } else {
386:                         $arg = [];
387:                         foreach ($clone as $row) {
388:                             $arg[] = array_values(iterator_to_array($row));
389:                         }
390:                     }
391:                 }
392: 
393:                 if ($arg !== null) {
394:                     if (!$arg) {
395:                         $hasBrackets = strpos($condition, '(') !== false;
396:                         $hasOperators = preg_match('#AND|OR#', $condition);
397:                         $hasNot = strpos($condition, 'NOT') !== false;
398:                         $hasPrefixNot = strpos($match[2][0], 'NOT') !== false;
399:                         if (!$hasBrackets && ($hasOperators || ($hasNot && !$hasPrefixNot))) {
400:                             throw new Nette\InvalidArgumentException('Possible SQL query corruption. Add parentheses around operators.');
401:                         }
402:                         if ($hasPrefixNot) {
403:                             $replace = 'IS NULL OR TRUE';
404:                         } else {
405:                             $replace = 'IS NULL AND FALSE';
406:                         }
407:                         $arg = null;
408:                     } else {
409:                         $replace = $match[2][0] . '(?)';
410:                         $conditionsParameters[] = $arg;
411:                     }
412:                 }
413:             } elseif ($arg instanceof SqlLiteral) {
414:                 $conditionsParameters[] = $arg;
415:             } else {
416:                 if (!$hasOperator) {
417:                     $replace = '= ?';
418:                 }
419:                 $conditionsParameters[] = $arg;
420:             }
421: 
422:             if ($replace) {
423:                 $condition = substr_replace($condition, $replace, $match[1][1], strlen($match[1][0]));
424:                 $replace = null;
425:             }
426: 
427:             if ($arg !== null) {
428:                 $placeholderNum++;
429:             }
430:         }
431: 
432:         $conditions[] = $condition;
433:         return true;
434:     }
435: 
436: 
437:     /**
438:      * @return array
439:      */
440:     public function getConditions()
441:     {
442:         return array_values($this->conditions);
443:     }
444: 
445: 
446:     /**
447:      * Adds alias.
448:      * @param  string
449:      * @param  string
450:      * @return void
451:      */
452:     public function addAlias($chain, $alias)
453:     {
454:         if (isset($chain[0]) && $chain[0] !== '.' && $chain[0] !== ':') {
455:             $chain = '.' . $chain; // unified chain format
456:         }
457:         $this->checkUniqueTableName($alias, $chain);
458:         $this->aliases[$alias] = $chain;
459:     }
460: 
461: 
462:     /**
463:      * @param  string
464:      * @param  string
465:      * @return void
466:      */
467:     protected function checkUniqueTableName($tableName, $chain)
468:     {
469:         if (isset($this->aliases[$tableName]) && ($chain === '.' . $tableName)) {
470:             $chain = $this->aliases[$tableName];
471:         }
472:         if (isset($this->reservedTableNames[$tableName])) {
473:             if ($this->reservedTableNames[$tableName] === $chain) {
474:                 return;
475:             }
476:             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.");
477:         }
478:         $this->reservedTableNames[$tableName] = $chain;
479:     }
480: 
481: 
482:     public function addOrder($columns, ...$params)
483:     {
484:         $this->order[] = $columns;
485:         $this->parameters['order'] = array_merge($this->parameters['order'], $params);
486:     }
487: 
488: 
489:     public function setOrder(array $columns, array $parameters)
490:     {
491:         $this->order = $columns;
492:         $this->parameters['order'] = $parameters;
493:     }
494: 
495: 
496:     /**
497:      * @return array
498:      */
499:     public function getOrder()
500:     {
501:         return $this->order;
502:     }
503: 
504: 
505:     /**
506:      * @param  int|null
507:      * @param  int|null
508:      * @return void
509:      */
510:     public function setLimit($limit, $offset)
511:     {
512:         $this->limit = $limit;
513:         $this->offset = $offset;
514:     }
515: 
516: 
517:     /**
518:      * @return int|null
519:      */
520:     public function getLimit()
521:     {
522:         return $this->limit;
523:     }
524: 
525: 
526:     /**
527:      * @return int|null
528:      */
529:     public function getOffset()
530:     {
531:         return $this->offset;
532:     }
533: 
534: 
535:     public function setGroup($columns, ...$params)
536:     {
537:         $this->group = $columns;
538:         $this->parameters['group'] = $params;
539:     }
540: 
541: 
542:     /**
543:      * @return string
544:      */
545:     public function getGroup()
546:     {
547:         return $this->group;
548:     }
549: 
550: 
551:     public function setHaving($having, ...$params)
552:     {
553:         $this->having = $having;
554:         $this->parameters['having'] = $params;
555:     }
556: 
557: 
558:     /**
559:      * @return string
560:      */
561:     public function getHaving()
562:     {
563:         return $this->having;
564:     }
565: 
566: 
567:     /********************* SQL building ****************d*g**/
568: 
569: 
570:     /**
571:      * @return string
572:      */
573:     protected function buildSelect(array $columns)
574:     {
575:         return 'SELECT ' . implode(', ', $columns);
576:     }
577: 
578: 
579:     /**
580:      * @return array
581:      */
582:     protected function parseJoinConditions(&$joins, $joinConditions)
583:     {
584:         $tableJoins = $leftJoinDependency = $finalJoinConditions = [];
585:         foreach ($joinConditions as $tableChain => &$joinCondition) {
586:             $fooQuery = $tableChain . '.foo';
587:             $requiredJoins = [];
588:             $this->parseJoins($requiredJoins, $fooQuery);
589:             $tableAlias = substr($fooQuery, 0, -4);
590:             $tableJoins[$tableAlias] = $requiredJoins;
591:             $leftJoinDependency[$tableAlias] = [];
592:             $finalJoinConditions[$tableAlias] = preg_replace_callback($this->getColumnChainsRegxp(), function ($match) use ($tableAlias, &$tableJoins, &$leftJoinDependency) {
593:                 $requiredJoins = [];
594:                 $query = $this->parseJoinsCb($requiredJoins, $match);
595:                 $queryParts = explode('.', $query);
596:                 $tableJoins[$queryParts[0]] = $requiredJoins;
597:                 if ($queryParts[0] !== $tableAlias) {
598:                     foreach (array_keys($requiredJoins) as $requiredTable) {
599:                         $leftJoinDependency[$tableAlias][$requiredTable] = $requiredTable;
600:                     }
601:                 }
602:                 return $query;
603:             }, $joinCondition);
604:         }
605:         $this->parameters['joinConditionSorted'] = [];
606:         if (count($joinConditions)) {
607:             while (reset($tableJoins)) {
608:                 $this->getSortedJoins(key($tableJoins), $leftJoinDependency, $tableJoins, $joins);
609:             }
610:         }
611:         return $finalJoinConditions;
612:     }
613: 
614: 
615:     protected function getSortedJoins($table, &$leftJoinDependency, &$tableJoins, &$finalJoins)
616:     {
617:         if (isset($this->expandingJoins[$table])) {
618:             $path = implode("' => '", array_map(function ($value) { return $this->reservedTableNames[$value]; }, array_merge(array_keys($this->expandingJoins), [$table])));
619:             throw new Nette\InvalidArgumentException("Circular reference detected at left join conditions (tables '$path').");
620:         }
621:         if (isset($tableJoins[$table])) {
622:             $this->expandingJoins[$table] = true;
623:             if (isset($leftJoinDependency[$table])) {
624:                 foreach ($leftJoinDependency[$table] as $requiredTable) {
625:                     if ($requiredTable === $table) {
626:                         continue;
627:                     }
628:                     $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
629:                 }
630:             }
631:             if ($tableJoins[$table]) {
632:                 foreach ($tableJoins[$table] as $requiredTable => $tmp) {
633:                     if ($requiredTable === $table) {
634:                         continue;
635:                     }
636:                     $this->getSortedJoins($requiredTable, $leftJoinDependency, $tableJoins, $finalJoins);
637:                 }
638:             }
639:             $finalJoins += $tableJoins[$table];
640:             $key = isset($this->aliases[$table]) ? $table : $this->reservedTableNames[$table];
641:             $this->parameters['joinConditionSorted'] += isset($this->parameters['joinCondition'][$key])
642:                 ? [$table => $this->parameters['joinCondition'][$key]]
643:                 : [];
644:             unset($tableJoins[$table], $this->expandingJoins[$table]);
645:         }
646:     }
647: 
648: 
649:     protected function parseJoins(&$joins, &$query)
650:     {
651:         $query = preg_replace_callback($this->getColumnChainsRegxp(), function ($match) use (&$joins) {
652:             return $this->parseJoinsCb($joins, $match);
653:         }, $query);
654:     }
655: 
656: 
657:     /**
658:      * @return string
659:      */
660:     private function getColumnChainsRegxp()
661:     {
662:         return '~
663:             (?(DEFINE)
664:                 (?P<word> [\w_]*[a-z][\w_]* )
665:                 (?P<del> [.:] )
666:                 (?P<node> (?&del)? (?&word) (\((?&word)\))? )
667:             )
668:             (?P<chain> (?!\.) (?&node)*)  \. (?P<column> (?&word) | \*  )
669:         ~xi';
670:     }
671: 
672: 
673:     /**
674:      * @return string
675:      */
676:     public function parseJoinsCb(&$joins, $match)
677:     {
678:         $chain = $match['chain'];
679:         if (!empty($chain[0]) && ($chain[0] !== '.' && $chain[0] !== ':')) {
680:             $chain = '.' . $chain;  // unified chain format
681:         }
682: 
683:         preg_match_all('~
684:             (?(DEFINE)
685:                 (?P<word> [\w_]*[a-z][\w_]* )
686:             )
687:             (?P<del> [.:])?(?P<key> (?&word))(\((?P<throughColumn> (?&word))\))?
688:         ~xi', $chain, $keyMatches, PREG_SET_ORDER);
689: 
690:         $parent = $this->tableName;
691:         $parentAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $this->tableName);
692: 
693:         // join schema keyMatch and table keyMatch to schema.table keyMatch
694:         if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_SCHEMA) && count($keyMatches) > 1) {
695:             $tables = $this->getCachedTableList();
696:             if (!isset($tables[$keyMatches[0]['key']]) && isset($tables[$keyMatches[0]['key'] . '.' . $keyMatches[1]['key']])) {
697:                 $keyMatch = array_shift($keyMatches);
698:                 $keyMatches[0]['key'] = $keyMatch['key'] . '.' . $keyMatches[0]['key'];
699:                 $keyMatches[0]['del'] = $keyMatch['del'];
700:             }
701:         }
702: 
703:         // do not make a join when referencing to the current table column - inner conditions
704:         // check it only when not making backjoin on itself - outer condition
705:         if ($keyMatches[0]['del'] === '.') {
706:             if (count($keyMatches) > 1 && ($parent === $keyMatches[0]['key'] || $parentAlias === $keyMatches[0]['key'])) {
707:                 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.");
708:             }
709:             if ($parent === $keyMatches[0]['key']) {
710:                 return "{$parent}.{$match['column']}";
711:             } elseif ($parentAlias === $keyMatches[0]['key']) {
712:                 return "{$parentAlias}.{$match['column']}";
713:             }
714:         }
715:         $tableChain = null;
716:         foreach ($keyMatches as $index => $keyMatch) {
717:             $isLast = !isset($keyMatches[$index + 1]);
718:             if (!$index && isset($this->aliases[$keyMatch['key']])) {
719:                 if ($keyMatch['del'] === ':') {
720:                     throw new Nette\InvalidArgumentException("You are using has many syntax with alias (':{$keyMatch['key']}'). You have to move it to alias definition.");
721:                 } else {
722:                     $previousAlias = $this->currentAlias;
723:                     $this->currentAlias = $keyMatch['key'];
724:                     $requiredJoins = [];
725:                     $query = $this->aliases[$keyMatch['key']] . '.foo';
726:                     $this->parseJoins($requiredJoins, $query);
727:                     $aliasJoin = array_pop($requiredJoins);
728:                     $joins += $requiredJoins;
729:                     list($table, , $parentAlias, $column, $primary) = $aliasJoin;
730:                     $this->currentAlias = $previousAlias;
731:                 }
732:             } elseif ($keyMatch['del'] === ':') {
733:                 if (isset($keyMatch['throughColumn'])) {
734:                     $table = $keyMatch['key'];
735:                     $belongsTo = $this->conventions->getBelongsToReference($table, $keyMatch['throughColumn']);
736:                     if (!$belongsTo) {
737:                         throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
738:                     }
739:                     list(, $primary) = $belongsTo;
740: 
741:                 } else {
742:                     $hasMany = $this->conventions->getHasManyReference($parent, $keyMatch['key']);
743:                     if (!$hasMany) {
744:                         throw new Nette\InvalidArgumentException("No reference found for \${$parent}->related({$keyMatch['key']}).");
745:                     }
746:                     list($table, $primary) = $hasMany;
747:                 }
748:                 $column = $this->conventions->getPrimary($parent);
749: 
750:             } else {
751:                 $belongsTo = $this->conventions->getBelongsToReference($parent, $keyMatch['key']);
752:                 if (!$belongsTo) {
753:                     throw new Nette\InvalidArgumentException("No reference found for \${$parent}->{$keyMatch['key']}.");
754:                 }
755:                 list($table, $column) = $belongsTo;
756:                 $primary = $this->conventions->getPrimary($table);
757:             }
758: 
759:             if ($this->currentAlias && $isLast) {
760:                 $tableAlias = $this->currentAlias;
761:             } elseif ($parent === $table) {
762:                 $tableAlias = $parentAlias . '_ref';
763:             } elseif ($keyMatch['key']) {
764:                 $tableAlias = $keyMatch['key'];
765:             } else {
766:                 $tableAlias = preg_replace('#^(.*\.)?(.*)$#', '$2', $table);
767:             }
768: 
769:             $tableChain .= $keyMatch[0];
770:             if (!$isLast || !$this->currentAlias) {
771:                 $this->checkUniqueTableName($tableAlias, $tableChain);
772:             }
773:             $joins[$tableAlias] = [$table, $tableAlias, $parentAlias, $column, $primary];
774:             $parent = $table;
775:             $parentAlias = $tableAlias;
776:         }
777: 
778:         return $tableAlias . ".{$match['column']}";
779:     }
780: 
781: 
782:     /**
783:      * @return string
784:      */
785:     protected function buildQueryJoins(array $joins, array $leftJoinConditions = [])
786:     {
787:         $return = '';
788:         foreach ($joins as list($joinTable, $joinAlias, $table, $tableColumn, $joinColumn)) {
789:             $return .=
790:                 " LEFT JOIN {$joinTable}" . ($joinTable !== $joinAlias ? " {$joinAlias}" : '') .
791:                 " ON {$table}.{$tableColumn} = {$joinAlias}.{$joinColumn}" .
792:                 (isset($leftJoinConditions[$joinAlias]) ? " {$leftJoinConditions[$joinAlias]}" : '');
793:         }
794:         return $return;
795:     }
796: 
797: 
798:     /**
799:      * @return array
800:      */
801:     protected function buildJoinConditions()
802:     {
803:         $conditions = [];
804:         foreach ($this->joinCondition as $tableChain => $joinConditions) {
805:             $conditions[$tableChain] = 'AND (' . implode(') AND (', $joinConditions) . ')';
806:         }
807:         return $conditions;
808:     }
809: 
810: 
811:     /**
812:      * @return string
813:      */
814:     protected function buildConditions()
815:     {
816:         return $this->where ? ' WHERE (' . implode(') AND (', $this->where) . ')' : '';
817:     }
818: 
819: 
820:     /**
821:      * @return string
822:      */
823:     protected function buildQueryEnd()
824:     {
825:         $return = '';
826:         if ($this->group) {
827:             $return .= ' GROUP BY ' . $this->group;
828:         }
829:         if ($this->having) {
830:             $return .= ' HAVING ' . $this->having;
831:         }
832:         if ($this->order) {
833:             $return .= ' ORDER BY ' . implode(', ', $this->order);
834:         }
835:         return $return;
836:     }
837: 
838: 
839:     /**
840:      * @return string
841:      */
842:     protected function tryDelimite($s)
843:     {
844:         return preg_replace_callback('#(?<=[^\w`"\[?]|^)[a-z_][a-z0-9_]*(?=[^\w`"(\]]|\z)#i', function ($m) {
845:             return strtoupper($m[0]) === $m[0] ? $m[0] : $this->driver->delimite($m[0]);
846:         }, $s);
847:     }
848: 
849: 
850:     /**
851:      * @return bool
852:      */
853:     protected function addConditionComposition(array $columns, array $parameters, array &$conditions, array &$conditionsParameters)
854:     {
855:         if ($this->driver->isSupported(ISupplementalDriver::SUPPORT_MULTI_COLUMN_AS_OR_COND)) {
856:             $conditionFragment = '(' . implode(' = ? AND ', $columns) . ' = ?) OR ';
857:             $condition = substr(str_repeat($conditionFragment, count($parameters)), 0, -4);
858:             return $this->addCondition($condition, [Nette\Utils\Arrays::flatten($parameters)], $conditions, $conditionsParameters);
859:         } else {
860:             return $this->addCondition('(' . implode(', ', $columns) . ') IN', [$parameters], $conditions, $conditionsParameters);
861:         }
862:     }
863: 
864: 
865:     /**
866:      * @return string
867:      */
868:     private function getConditionHash($condition, array $parameters)
869:     {
870:         foreach ($parameters as $key => &$parameter) {
871:             if ($parameter instanceof Selection) {
872:                 $parameter = $this->getConditionHash($parameter->getSql(), $parameter->getSqlBuilder()->getParameters());
873:             } elseif ($parameter instanceof SqlLiteral) {
874:                 $parameter = $this->getConditionHash($parameter->__toString(), $parameter->getParameters());
875:             } elseif (is_object($parameter) && method_exists($parameter, '__toString')) {
876:                 $parameter = $parameter->__toString();
877:             } elseif (is_array($parameter) || $parameter instanceof \ArrayAccess) {
878:                 $parameter = $this->getConditionHash($key, $parameter);
879:             }
880:         }
881:         return md5($condition . json_encode($parameters));
882:     }
883: 
884: 
885:     /**
886:      * @return array
887:      */
888:     private function getCachedTableList()
889:     {
890:         if (!$this->cacheTableList) {
891:             $this->cacheTableList = array_flip(array_map(function ($pair) {
892:                 return isset($pair['fullName']) ? $pair['fullName'] : $pair['name'];
893:             }, $this->structure->getTables()));
894:         }
895: 
896:         return $this->cacheTableList;
897:     }
898: }
899: