Namespaces

Classes

Interfaces

Traits

Exceptions

1: <?php
2:
3: declare(strict_types=1);
4:
5: namespace Atk4\Data\Persistence\Sql;
6:
7: /**
8: * Perform query operation on SQL server (such as select, insert, delete, etc).
9: */
10: abstract class Query extends Expression
11: {
12: /** Template name for render. */
13: public string $mode = 'select';
14:
15: /** @var string|Expression If no fields are defined, this field is used. */
16: public $defaultField = '*';
17:
18: /** @var class-string<Expression> */
19: protected string $expressionClass;
20:
21: public bool $wrapInParentheses = true;
22:
23: /** @var array<string> */
24: protected array $supportedOperators = ['=', '!=', '<', '>', '<=', '>=', 'like', 'not like', 'in', 'not in'];
25:
26: protected string $templateSelect = '[with]select[option] [field] [from] [table][join][where][group][having][order][limit]';
27: protected string $templateInsert = 'insert[option] into [tableNoalias] ([setFields]) values ([setValues])';
28: protected string $templateReplace = 'replace[option] into [tableNoalias] ([setFields]) values ([setValues])';
29: protected string $templateDelete = '[with]delete [from] [tableNoalias][where][having]';
30: protected string $templateUpdate = '[with]update [tableNoalias] set [set] [where]';
31: protected string $templateTruncate = 'truncate table [tableNoalias]';
32:
33: // {{{ Field specification and rendering
34:
35: /**
36: * Adds new column to resulting select by querying $field.
37: *
38: * Examples:
39: * $q->field('name');
40: *
41: * You can use a dot to prepend table name to the field:
42: * $q->field('user.name');
43: * $q->field('user.name')->field('address.line1');
44: *
45: * You can pass first argument as Expression or Query
46: * $q->field($q->expr('2 + 2'), 'alias'); // must always use alias
47: *
48: * You can use $q->dsql() for subqueries. Subqueries will be wrapped in parentheses.
49: * $q->field($q->dsql()->table('x')..., 'alias');
50: *
51: * If you need to use funky name for the field (e.g, one containing
52: * a dot or a space), you should wrap it into expression:
53: * $q->field($q->expr('{}', ['fun...ky.field']), 'f');
54: *
55: * @param string|Expressionable $field Specifies field to select
56: * @param string $alias Specify alias for this field
57: *
58: * @return $this
59: */
60: public function field($field, $alias = null)
61: {
62: $this->_setArgs('field', $alias, $field);
63:
64: return $this;
65: }
66:
67: /**
68: * Returns template component for [field].
69: *
70: * @param bool $addAlias Should we add aliases, see _renderFieldNoalias()
71: *
72: * @return string Parsed template chunk
73: */
74: protected function _renderField($addAlias = true): string
75: {
76: // if no fields were defined, use defaultField
77: if (($this->args['field'] ?? []) === []) {
78: if ($this->defaultField instanceof Expression) {
79: return $this->consume($this->defaultField, self::ESCAPE_PARAM);
80: }
81:
82: return $this->defaultField;
83: }
84:
85: $res = [];
86: foreach ($this->args['field'] as $alias => $field) {
87: // do not add alias when:
88: // - we don't want aliases
89: // - OR alias is the same as field
90: // - OR alias is numeric
91: if ($addAlias === false
92: || (is_string($field) && $alias === $field)
93: || is_int($alias)
94: ) {
95: $alias = null;
96: }
97:
98: // will parameterize the value and escape if necessary
99: $field = $this->consume($field, self::ESCAPE_IDENTIFIER_SOFT);
100:
101: if ($alias) {
102: // field alias cannot be expression, so simply escape it
103: $field .= ' ' . $this->escapeIdentifier($alias);
104: }
105:
106: $res[] = $field;
107: }
108:
109: return implode(', ', $res);
110: }
111:
112: protected function _renderFieldNoalias(): string
113: {
114: return $this->_renderField(false);
115: }
116:
117: // }}}
118:
119: // {{{ Table specification and rendering
120:
121: /**
122: * Specify a table to be used in a query.
123: *
124: * @param string|Expressionable $table Specifies table
125: * @param string $alias Specify alias for this table
126: *
127: * @return $this
128: */
129: public function table($table, $alias = null)
130: {
131: if ($table instanceof self && $alias === null) {
132: throw new Exception('If table is set as subquery, then table alias is required');
133: }
134:
135: if (is_string($table) && $alias === null) {
136: $alias = $table;
137: }
138:
139: $this->_setArgs('table', $alias, $table);
140:
141: return $this;
142: }
143:
144: /**
145: * Name or alias of base table to use when using default join().
146: *
147: * It is set by table(). If you are using multiple tables,
148: * then false is returned as it is irrelevant.
149: *
150: * @return string|false|null
151: */
152: protected function getMainTable()
153: {
154: $c = count($this->args['table'] ?? []);
155: if ($c === 0) {
156: return null;
157: } elseif ($c !== 1) {
158: return false;
159: }
160:
161: $alias = array_key_first($this->args['table']);
162: if (!is_int($alias)) {
163: return $alias;
164: }
165:
166: return $this->args['table'][$alias];
167: }
168:
169: /**
170: * @param bool $addAlias Should we add aliases, see _renderTableNoalias()
171: */
172: protected function _renderTable($addAlias = true): ?string
173: {
174: $res = [];
175: foreach ($this->args['table'] ?? [] as $alias => $table) {
176: if ($addAlias === false && $table instanceof self) {
177: throw new Exception('Table cannot be Query in UPDATE, INSERT etc. query modes');
178: }
179:
180: // do not add alias when:
181: // - we don't want aliases
182: // - OR alias is the same as table name
183: // - OR alias is numeric
184: if ($addAlias === false
185: || (is_string($table) && $alias === $table)
186: || is_int($alias)
187: ) {
188: $alias = null;
189: }
190:
191: // consume or escape table
192: $table = $this->consume($table, self::ESCAPE_IDENTIFIER_SOFT);
193:
194: // add alias if needed
195: if ($alias) {
196: $table .= ' ' . $this->escapeIdentifier($alias);
197: }
198:
199: $res[] = $table;
200: }
201:
202: return implode(', ', $res);
203: }
204:
205: protected function _renderTableNoalias(): ?string
206: {
207: return $this->_renderTable(false);
208: }
209:
210: protected function _renderFrom(): ?string
211: {
212: return isset($this->args['table']) ? 'from' : '';
213: }
214:
215: // }}}
216:
217: // {{{ with()
218:
219: /**
220: * Specify WITH query to be used.
221: *
222: * @param Query $cursor Specifies cursor query or array [alias => query] for adding multiple
223: * @param string $alias Specify alias for this cursor
224: * @param array<int, string>|null $fields Optional array of field names used in cursor
225: * @param bool $recursive Is it recursive?
226: *
227: * @return $this
228: */
229: public function with(self $cursor, string $alias, array $fields = null, bool $recursive = false)
230: {
231: $this->_setArgs('with', $alias, [
232: 'cursor' => $cursor,
233: 'fields' => $fields,
234: 'recursive' => $recursive,
235: ]);
236:
237: return $this;
238: }
239:
240: protected function _renderWith(): ?string
241: {
242: if (($this->args['with'] ?? []) === []) {
243: return '';
244: }
245:
246: $res = [];
247:
248: $isRecursive = false;
249: foreach ($this->args['with'] as $alias => ['cursor' => $cursor, 'fields' => $fields, 'recursive' => $recursive]) {
250: // cursor alias cannot be expression, so simply escape it
251: $s = $this->escapeIdentifier($alias) . ' ';
252:
253: // set cursor fields
254: if ($fields !== null) {
255: $s .= '(' . implode(', ', array_map([$this, 'escapeIdentifier'], $fields)) . ') ';
256: }
257:
258: // will parameterize the value and escape if necessary
259: $s .= 'as ' . $this->consume($cursor, self::ESCAPE_IDENTIFIER_SOFT);
260:
261: if ($recursive) {
262: $isRecursive = true;
263: }
264:
265: $res[] = $s;
266: }
267:
268: return 'with ' . ($isRecursive ? 'recursive ' : '') . implode(',' . "\n", $res) . "\n";
269: }
270:
271: // }}}
272:
273: // {{{ join()
274:
275: /**
276: * Joins your query with another table. Join will use $this->getMainTable()
277: * to reference the main table, unless you specify it explicitly.
278: *
279: * Examples:
280: * $q->join('address'); // on user.address_id = address.id
281: * $q->join('address.user_id'); // on address.user_id = user.id
282: * $q->join('address a'); // with alias
283: *
284: * Second argument may specify the field of the master table
285: * $q->join('address', 'billing_id');
286: * $q->join('address.code', 'code');
287: * $q->join('address.code', 'user.code');
288: *
289: * Third argument may specify which kind of join to use.
290: * $q->join('address', null, 'left');
291: * $q->join('address.code', 'user.code', 'inner');
292: *
293: * You can use expression for more complex joins
294: * $q->join('address',
295: * $q->orExpr()
296: * ->where('user.billing_id', 'address.id')
297: * ->where('user.technical_id', 'address.id')
298: * )
299: *
300: * @param string $foreignTable Table to join with
301: * @param string|Expression $masterField Field in master table
302: * @param string $joinKind 'left' or 'inner', etc
303: * @param string $foreignAlias
304: *
305: * @return $this
306: */
307: public function join(
308: $foreignTable,
309: $masterField = null,
310: $joinKind = null,
311: $foreignAlias = null
312: ) {
313: $j = [];
314:
315: // try to find alias in foreign table definition
316: // TODO this behaviour should be deprecated
317: if ($foreignAlias === null) {
318: [$foreignTable, $foreignAlias] = array_pad(explode(' ', $foreignTable, 2), 2, null);
319: }
320:
321: // split and deduce fields
322: // TODO this will not allow table names with dots in there !!!
323: [$f1, $f2] = array_pad(explode('.', $foreignTable, 2), 2, null);
324:
325: if (is_object($masterField)) {
326: $j['expr'] = $masterField;
327: } else {
328: // split and deduce primary table
329: if ($masterField === null) {
330: [$m1, $m2] = [null, null];
331: } else {
332: [$m1, $m2] = array_pad(explode('.', $masterField, 2), 2, null);
333: }
334: if ($m2 === null) {
335: $m2 = $m1;
336: $m1 = null;
337: }
338: if ($m1 === null) {
339: $m1 = $this->getMainTable();
340: }
341:
342: // identify fields we use for joins
343: if ($f2 === null && $m2 === null) {
344: $m2 = $f1 . '_id';
345: }
346: if ($m2 === null) {
347: $m2 = 'id';
348: }
349: $j['m1'] = $m1;
350: $j['m2'] = $m2;
351: }
352:
353: $j['f1'] = $f1;
354: if ($f2 === null) {
355: $f2 = 'id';
356: }
357: $j['f2'] = $f2;
358:
359: $j['t'] = $joinKind ?? 'left';
360: $j['fa'] = $foreignAlias;
361:
362: $this->args['join'][] = $j;
363:
364: return $this;
365: }
366:
367: protected function _renderJoin(): ?string
368: {
369: if (!isset($this->args['join'])) {
370: return '';
371: }
372: $joins = [];
373: foreach ($this->args['join'] as $j) {
374: $jj = $j['t'] . ' join '
375: . $this->escapeIdentifierSoft($j['f1'])
376: . ($j['fa'] !== null ? ' ' . $this->escapeIdentifier($j['fa']) : '')
377: . ' on ';
378:
379: if (isset($j['expr'])) {
380: $jj .= $this->consume($j['expr'], self::ESCAPE_PARAM);
381: } else {
382: $jj .= $this->escapeIdentifier($j['fa'] ?? $j['f1']) . '.'
383: . $this->escapeIdentifier($j['f2']) . ' = '
384: . ($j['m1'] === null ? '' : $this->escapeIdentifier($j['m1']) . '.')
385: . $this->escapeIdentifier($j['m2']);
386: }
387: $joins[] = $jj;
388: }
389:
390: return ' ' . implode(' ', $joins);
391: }
392:
393: // }}}
394:
395: // {{{ where() and having() specification and rendering
396:
397: /**
398: * Adds condition to your query.
399: *
400: * Examples:
401: * $q->where('id', 1);
402: *
403: * By default condition implies equality. You can specify a different comparison operator
404: * by using 3-argument format:
405: * $q->where('id', '>', 1);
406: *
407: * You may use Expression as any part of the query.
408: * $q->where($q->expr('a = b'));
409: * $q->where('date', '>', $q->expr('now()'));
410: * $q->where($q->expr('length(password)'), '>', 5);
411: *
412: * If you specify Query as an argument, it will be automatically surrounded by parentheses:
413: * $q->where('user_id', $q->dsql()->table('users')->field('id'));
414: *
415: * To specify OR conditions:
416: * $q->where($q->orExpr()->where('a', 1)->where('b', 1));
417: *
418: * @param string|Expressionable $field Field or Expression
419: * @param mixed $cond Condition such as '=', '>' or 'not like'
420: * @param mixed $value Value. Will be quoted unless you pass expression
421: * @param string $kind Do not use directly. Use having()
422: * @param int $numArgs when $kind is passed, we can't determine number of
423: * actual arguments, so this argument must be specified
424: *
425: * @return $this
426: */
427: public function where($field, $cond = null, $value = null, $kind = 'where', $numArgs = null)
428: {
429: // number of passed arguments will be used to determine if arguments were specified or not
430: if ($numArgs === null) {
431: $numArgs = 'func_num_args'();
432: }
433:
434: if (is_string($field) && preg_match('~([><!=]|(<!\w)(not|is|in|like))\s*$~i', $field)) {
435: throw (new Exception('Field condition must be passed separately'))
436: ->addMoreInfo('field', $field);
437: }
438:
439: if ($numArgs === 1) {
440: if (is_string($field)) {
441: $field = $this->expr($field);
442: $field->wrapInParentheses = true;
443: } elseif (!$field instanceof Expression || !$field->wrapInParentheses) {
444: $field = $this->expr('[]', [$field]);
445: $field->wrapInParentheses = true;
446: }
447:
448: $this->args[$kind][] = [$field];
449: } else {
450: if ($numArgs === 2) {
451: $value = $cond;
452: unset($cond);
453: }
454:
455: if (is_object($value) && !$value instanceof Expressionable) {
456: throw (new Exception('Value cannot be converted to SQL-compatible expression'))
457: ->addMoreInfo('field', $field)
458: ->addMoreInfo('value', $value);
459: }
460:
461: if ($numArgs === 2) {
462: $this->args[$kind][] = [$field, $value];
463: } else {
464: $this->args[$kind][] = [$field, $cond, $value];
465: }
466: }
467:
468: return $this;
469: }
470:
471: /**
472: * Same syntax as where().
473: *
474: * @param string|Expressionable $field Field or Expression
475: * @param mixed $cond Condition such as '=', '>' or 'not like'
476: * @param mixed $value Value. Will be quoted unless you pass expression
477: *
478: * @return $this
479: */
480: public function having($field, $cond = null, $value = null)
481: {
482: return $this->where($field, $cond, $value, 'having', 'func_num_args'());
483: }
484:
485: /**
486: * Subroutine which renders either [where] or [having].
487: *
488: * @param string $kind 'where' or 'having'
489: *
490: * @return list<string>
491: */
492: protected function _subrenderWhere($kind): array
493: {
494: // where() might have been called multiple times
495: // collect all conditions, then join them with AND keyword
496: $res = [];
497: foreach ($this->args[$kind] as $row) {
498: $res[] = $this->_subrenderCondition($row);
499: }
500:
501: return $res;
502: }
503:
504: /**
505: * Override to fix numeric affinity for SQLite.
506: */
507: protected function _renderConditionBinary(string $operator, string $sqlLeft, string $sqlRight): string
508: {
509: return $sqlLeft . ' ' . $operator . ' ' . $sqlRight;
510: }
511:
512: /**
513: * Override to fix numeric affinity for SQLite.
514: *
515: * @param non-empty-list<string> $sqlValues
516: */
517: protected function _renderConditionInOperator(bool $negated, string $sqlLeft, array $sqlValues): string
518: {
519: return $sqlLeft . ($negated ? ' not' : '') . ' in (' . implode(', ', $sqlValues) . ')';
520: }
521:
522: /**
523: * @param array<0|1|2, mixed> $row
524: */
525: protected function _subrenderCondition(array $row): string
526: {
527: if (count($row) === 3) {
528: [$field, $cond, $value] = $row;
529: } elseif (count($row) === 2) {
530: [$field, $cond] = $row;
531: } elseif (count($row) === 1) {
532: [$field] = $row;
533: } else {
534: throw new \InvalidArgumentException();
535: }
536:
537: $field = $this->consume($field, self::ESCAPE_IDENTIFIER_SOFT);
538:
539: if (count($row) === 1) {
540: // only a single parameter was passed, so we simply include all
541: return $field;
542: }
543:
544: // if no condition defined - use default
545: if (count($row) === 2) {
546: $value = $cond;
547:
548: if ($value instanceof Expressionable) {
549: $value = $value->getDsqlExpression($this);
550: }
551:
552: if (is_array($value)) {
553: $cond = 'in';
554: } elseif ($value instanceof self && $value->mode === 'select') {
555: $cond = 'in';
556: } else {
557: $cond = '=';
558: }
559: } else {
560: $cond = strtolower($cond);
561: }
562:
563: // below we can be sure that all 3 arguments has been passed
564:
565: if (!in_array($cond, $this->supportedOperators, true)) {
566: throw (new Exception('Unsupported operator'))
567: ->addMoreInfo('operator', $cond);
568: }
569:
570: // special conditions (IS | IS NOT) if value is null
571: if ($value === null) { // @phpstan-ignore-line see https://github.com/phpstan/phpstan/issues/4173
572: if ($cond === '=') {
573: return $field . ' is null';
574: } elseif ($cond === '!=') {
575: return $field . ' is not null';
576: }
577:
578: throw (new Exception('Unsupported operator for null value'))
579: ->addMoreInfo('operator', $cond);
580: }
581:
582: // special conditions (IN | NOT IN) if value is array
583: if (is_array($value)) {
584: if (in_array($cond, ['in', 'not in'], true)) {
585: // special treatment of empty array condition
586: if (count($value) === 0) {
587: if ($cond === 'in') {
588: return '1 = 0'; // never true
589: }
590:
591: return '1 = 1'; // always true
592: }
593:
594: $values = array_map(fn ($v) => $this->consume($v, self::ESCAPE_PARAM), $value);
595:
596: return $this->_renderConditionInOperator($cond === 'not in', $field, $values);
597: }
598:
599: throw (new Exception('Unsupported operator for array value'))
600: ->addMoreInfo('operator', $cond);
601: } elseif (!$value instanceof Expressionable && in_array($cond, ['in', 'not in'], true)) {
602: throw (new Exception('Unsupported operator for non-array value'))
603: ->addMoreInfo('operator', $cond);
604: }
605:
606: // if value is object, then it should be Expression or Query itself
607: // otherwise just escape value
608: $value = $this->consume($value, self::ESCAPE_PARAM);
609:
610: return $this->_renderConditionBinary($cond, $field, $value);
611: }
612:
613: protected function _renderWhere(): ?string
614: {
615: if (!isset($this->args['where'])) {
616: return null;
617: }
618:
619: return ' where ' . implode(' and ', $this->_subrenderWhere('where'));
620: }
621:
622: protected function _renderOrwhere(): ?string
623: {
624: if (isset($this->args['where']) && isset($this->args['having'])) {
625: throw new Exception('Mixing of WHERE and HAVING conditions not allowed in query expression');
626: }
627:
628: foreach (['where', 'having'] as $kind) {
629: if (isset($this->args[$kind])) {
630: return implode(' or ', $this->_subrenderWhere($kind));
631: }
632: }
633:
634: return null;
635: }
636:
637: protected function _renderAndwhere(): ?string
638: {
639: if (isset($this->args['where']) && isset($this->args['having'])) {
640: throw new Exception('Mixing of WHERE and HAVING conditions not allowed in query expression');
641: }
642:
643: foreach (['where', 'having'] as $kind) {
644: if (isset($this->args[$kind])) {
645: return implode(' and ', $this->_subrenderWhere($kind));
646: }
647: }
648:
649: return null;
650: }
651:
652: protected function _renderHaving(): ?string
653: {
654: if (!isset($this->args['having'])) {
655: return null;
656: }
657:
658: return ' having ' . implode(' and ', $this->_subrenderWhere('having'));
659: }
660:
661: // }}}
662:
663: // {{{ group()
664:
665: /**
666: * Implements GROUP BY functionality. Simply pass either field name
667: * as string or expression.
668: *
669: * @param string|Expressionable $group
670: *
671: * @return $this
672: */
673: public function group($group)
674: {
675: $this->args['group'][] = $group;
676:
677: return $this;
678: }
679:
680: protected function _renderGroup(): ?string
681: {
682: if (!isset($this->args['group'])) {
683: return '';
684: }
685:
686: $g = array_map(function ($v) {
687: return $this->consume($v, self::ESCAPE_IDENTIFIER_SOFT);
688: }, $this->args['group']);
689:
690: return ' group by ' . implode(', ', $g);
691: }
692:
693: // }}}
694:
695: // {{{ Set field implementation
696:
697: /**
698: * Sets field value for INSERT or UPDATE statements.
699: *
700: * @param string|Expressionable $field Name of the field
701: * @param mixed $value Value of the field
702: *
703: * @return $this
704: */
705: public function set($field, $value = null)
706: {
707: if (is_array($value)) {
708: throw (new Exception('Array values are not supported by SQL'))
709: ->addMoreInfo('field', $field)
710: ->addMoreInfo('value', $value);
711: }
712:
713: $this->args['set'][] = [$field, $value];
714:
715: return $this;
716: }
717:
718: /**
719: * @param array<string, mixed> $fields
720: *
721: * @return $this
722: */
723: public function setMulti($fields)
724: {
725: foreach ($fields as $k => $v) {
726: $this->set($k, $v);
727: }
728:
729: return $this;
730: }
731:
732: protected function _renderSet(): ?string
733: {
734: $res = [];
735: foreach ($this->args['set'] as [$field, $value]) {
736: $field = $this->consume($field, self::ESCAPE_IDENTIFIER);
737: $value = $this->consume($value, self::ESCAPE_PARAM);
738:
739: $res[] = $field . '=' . $value;
740: }
741:
742: return implode(', ', $res);
743: }
744:
745: protected function _renderSetFields(): ?string
746: {
747: $res = [];
748: foreach ($this->args['set'] as $pair) {
749: $field = $this->consume($pair[0], self::ESCAPE_IDENTIFIER);
750:
751: $res[] = $field;
752: }
753:
754: return implode(', ', $res);
755: }
756:
757: protected function _renderSetValues(): ?string
758: {
759: $res = [];
760: foreach ($this->args['set'] as $pair) {
761: $value = $this->consume($pair[1], self::ESCAPE_PARAM);
762:
763: $res[] = $value;
764: }
765:
766: return implode(', ', $res);
767: }
768:
769: // }}}
770:
771: // {{{ Option
772:
773: /**
774: * Set options for particular mode.
775: *
776: * @param string|Expressionable $option
777: * @param string $mode
778: *
779: * @return $this
780: */
781: public function option($option, $mode = 'select')
782: {
783: $this->args['option'][$mode][] = $option;
784:
785: return $this;
786: }
787:
788: protected function _renderOption(): ?string
789: {
790: if (!isset($this->args['option'][$this->mode])) {
791: return '';
792: }
793:
794: return ' ' . implode(' ', $this->args['option'][$this->mode]);
795: }
796:
797: // }}}
798:
799: // {{{ Order
800:
801: /**
802: * Orders results by field or Expression. See documentation for full
803: * list of possible arguments.
804: *
805: * $q->order('name');
806: * $q->order('name desc');
807: * $q->order(['name desc', 'id asc'])
808: * $q->order('name', true);
809: *
810: * @param string|Expressionable|array<int, string|Expressionable> $order order by
811: * @param ($order is array ? never : string|bool) $direction true to sort descending
812: *
813: * @return $this
814: */
815: public function order($order, $direction = null)
816: {
817: if (is_string($order) && str_contains($order, ',')) {
818: throw new Exception('Comma-separated fields list is no longer accepted, use array instead');
819: }
820:
821: if (is_array($order)) {
822: if ($direction !== null) {
823: throw new Exception('If first argument is array, second argument must not be used');
824: }
825:
826: foreach (array_reverse($order) as $o) {
827: $this->order($o);
828: }
829:
830: return $this;
831: }
832:
833: // first argument may contain space, to divide field and ordering keyword
834: if ($direction === null && is_string($order) && str_contains($order, ' ')) {
835: $lastSpacePos = strrpos($order, ' ');
836: if (in_array(strtolower(substr($order, $lastSpacePos + 1)), ['desc', 'asc'], true)) {
837: $direction = substr($order, $lastSpacePos + 1);
838: $order = substr($order, 0, $lastSpacePos);
839: }
840: }
841:
842: if (is_bool($direction)) {
843: $direction = $direction ? 'desc' : '';
844: } elseif (strtolower($direction ?? '') === 'asc') {
845: $direction = '';
846: }
847: // no else - allow custom order like "order by name desc nulls last" for Oracle
848:
849: $this->args['order'][] = [$order, $direction];
850:
851: return $this;
852: }
853:
854: /**
855: * @param list<string> $sqls
856: *
857: * @return list<string>
858: */
859: protected function deduplicateRenderOrder(array $sqls): array
860: {
861: return $sqls;
862: }
863:
864: protected function _renderOrder(): ?string
865: {
866: if (!isset($this->args['order'])) {
867: return '';
868: }
869:
870: $sqls = [];
871: foreach ($this->args['order'] as $tmp) {
872: [$arg, $desc] = $tmp;
873: $sqls[] = $this->consume($arg, self::ESCAPE_IDENTIFIER_SOFT) . ($desc ? (' ' . $desc) : '');
874: }
875:
876: $sqls = array_reverse($sqls);
877: $sqlsDeduplicated = $this->deduplicateRenderOrder($sqls);
878:
879: return ' order by ' . implode(', ', $sqlsDeduplicated);
880: }
881:
882: // }}}
883:
884: // {{{ Limit
885:
886: /**
887: * Limit how many rows will be returned.
888: *
889: * @param int $cnt Number of rows to return
890: * @param int $shift Offset, how many rows to skip
891: *
892: * @return $this
893: */
894: public function limit($cnt, $shift = null)
895: {
896: $this->args['limit'] = [
897: 'cnt' => $cnt,
898: 'shift' => $shift,
899: ];
900:
901: return $this;
902: }
903:
904: protected function _renderLimit(): ?string
905: {
906: if (!isset($this->args['limit'])) {
907: return null;
908: }
909:
910: return ' limit ' . (int) $this->args['limit']['shift']
911: . ', ' . (int) $this->args['limit']['cnt'];
912: }
913:
914: // }}}
915:
916: // {{{ Exists
917:
918: /**
919: * Creates 'select exists' query based on the query object.
920: *
921: * @return self
922: */
923: public function exists()
924: {
925: return $this->dsql()->mode('select')->option('exists')->field($this);
926: }
927:
928: // }}}
929:
930: #[\Override]
931: public function __debugInfo(): array
932: {
933: $arr = [
934: // 'mode' => $this->mode,
935: 'R' => 'n/a',
936: 'R_params' => 'n/a',
937: // 'template' => $this->template,
938: // 'templateArgs' => $this->args,
939: ];
940:
941: try {
942: $arr['R'] = $this->getDebugQuery();
943: $arr['R_params'] = $this->render()[1];
944: } catch (\Exception $e) {
945: $arr['R'] = get_class($e) . ': ' . $e->getMessage();
946: }
947:
948: return $arr;
949: }
950:
951: // {{{ Miscelanious
952:
953: /**
954: * Renders query template. If the template is not explicitly use "select" mode.
955: */
956: #[\Override]
957: public function render(): array
958: {
959: if ($this->template === null) {
960: $modeBackup = $this->mode;
961: $templateBackup = $this->template;
962: try {
963: $this->mode('select');
964:
965: return parent::render();
966: } finally {
967: $this->mode = $modeBackup;
968: $this->template = $templateBackup;
969: }
970: }
971:
972: return parent::render();
973: }
974:
975: #[\Override]
976: protected function renderNested(): array
977: {
978: if (isset($this->args['order']) && !isset($this->args['limit'])) {
979: $orderOrig = $this->args['order'];
980: unset($this->args['order']);
981: } else {
982: $orderOrig = null;
983: }
984: try {
985: [$sql, $params] = parent::renderNested();
986: } finally {
987: if ($orderOrig !== null) {
988: $this->args['order'] = $orderOrig;
989: }
990: }
991:
992: return [$sql, $params];
993: }
994:
995: /**
996: * Switch template for this query. Determines what would be done
997: * on execute.
998: *
999: * By default it is in SELECT mode
1000: *
1001: * @param string $mode
1002: *
1003: * @return $this
1004: */
1005: public function mode($mode)
1006: {
1007: $templatePropertyName = 'template' . ucfirst($mode);
1008:
1009: if (@isset($this->{$templatePropertyName})) {
1010: $this->mode = $mode;
1011: $this->template = $this->{$templatePropertyName};
1012: } else {
1013: throw (new Exception('Query does not have this mode'))
1014: ->addMoreInfo('mode', $mode);
1015: }
1016:
1017: return $this;
1018: }
1019:
1020: #[\Override]
1021: public function expr($template = [], array $arguments = []): Expression
1022: {
1023: $class = $this->expressionClass;
1024: $e = new $class($template, $arguments);
1025: $e->connection = $this->connection;
1026:
1027: return $e;
1028: }
1029:
1030: /**
1031: * Create Query object with the same connection.
1032: *
1033: * @param string|array<string, mixed> $defaults
1034: *
1035: * @return self
1036: */
1037: public function dsql($defaults = [])
1038: {
1039: $q = new static($defaults);
1040: $q->connection = $this->connection;
1041:
1042: return $q;
1043: }
1044:
1045: /**
1046: * Returns Expression object for NOW() or CURRENT_TIMESTAMP() method.
1047: */
1048: public function exprNow(int $precision = null): Expression
1049: {
1050: return $this->expr(
1051: 'current_timestamp(' . ($precision !== null ? '[]' : '') . ')',
1052: $precision !== null ? [$precision] : []
1053: );
1054: }
1055:
1056: /**
1057: * Returns new Query object of [or] expression.
1058: *
1059: * @return self
1060: */
1061: public function orExpr()
1062: {
1063: return $this->dsql(['template' => '[orwhere]']);
1064: }
1065:
1066: /**
1067: * Returns new Query object of [and] expression.
1068: *
1069: * @return self
1070: */
1071: public function andExpr()
1072: {
1073: return $this->dsql(['template' => '[andwhere]']);
1074: }
1075:
1076: /**
1077: * Returns a query for a function, which can be used as part of the GROUP
1078: * query which would concatenate all matching fields.
1079: *
1080: * @param string|Expressionable $field
1081: *
1082: * @return Expression
1083: */
1084: public function groupConcat($field, string $separator = ',')
1085: {
1086: throw new Exception('groupConcat() is SQL-dependent, so use a correct class');
1087: }
1088:
1089: /**
1090: * Returns Query object of [case] expression.
1091: *
1092: * @param mixed $operand optional operand for case expression
1093: *
1094: * @return self
1095: */
1096: public function caseExpr($operand = null)
1097: {
1098: $q = $this->dsql(['template' => '[case]']);
1099:
1100: if ($operand !== null) {
1101: $q->args['case_operand'] = [$operand];
1102: }
1103:
1104: return $q;
1105: }
1106:
1107: /**
1108: * Add when/then condition for [case] expression.
1109: *
1110: * @param mixed $when Condition as array for normal form [case] statement or just value in case of short form [case] statement
1111: * @param mixed $then Then expression or value
1112: *
1113: * @return $this
1114: */
1115: public function caseWhen($when, $then)
1116: {
1117: $this->args['case_when'][] = [$when, $then];
1118:
1119: return $this;
1120: }
1121:
1122: /**
1123: * Add else condition for [case] expression.
1124: *
1125: * @param mixed $else Else expression or value
1126: *
1127: * @return $this
1128: */
1129: public function caseElse($else)
1130: {
1131: $this->args['case_else'] = [$else];
1132:
1133: return $this;
1134: }
1135:
1136: protected function _renderCase(): ?string
1137: {
1138: if (!isset($this->args['case_when'])) {
1139: return null;
1140: }
1141:
1142: $res = '';
1143:
1144: // operand
1145: $isShortForm = isset($this->args['case_operand']);
1146: if ($isShortForm) {
1147: $res .= ' ' . $this->consume($this->args['case_operand'][0], self::ESCAPE_IDENTIFIER_SOFT);
1148: }
1149:
1150: // when, then
1151: foreach ($this->args['case_when'] as $row) {
1152: if (!array_key_exists(0, $row) || !array_key_exists(1, $row)) {
1153: throw (new Exception('Incorrect use of "when" method parameters'))
1154: ->addMoreInfo('row', $row);
1155: }
1156:
1157: $res .= ' when ';
1158: if ($isShortForm) {
1159: // short-form
1160: if (is_array($row[0])) {
1161: throw (new Exception('When using short form CASE statement, then you should not set array as when() method 1st parameter'))
1162: ->addMoreInfo('when', $row[0]);
1163: }
1164: $res .= $this->consume($row[0], self::ESCAPE_PARAM);
1165: } else {
1166: $res .= $this->_subrenderCondition($row[0]);
1167: }
1168:
1169: // then
1170: $res .= ' then ' . $this->consume($row[1], self::ESCAPE_PARAM);
1171: }
1172:
1173: // else
1174: if (array_key_exists('case_else', $this->args)) {
1175: $res .= ' else ' . $this->consume($this->args['case_else'][0], self::ESCAPE_PARAM);
1176: }
1177:
1178: return ' case' . $res . ' end';
1179: }
1180:
1181: /**
1182: * Sets value in args array. Doesn't allow duplicate aliases.
1183: *
1184: * @param string $what Where to set it - table|field
1185: * @param string|null $alias Alias name
1186: * @param mixed $value Value to set in args array
1187: */
1188: protected function _setArgs($what, $alias, $value): void
1189: {
1190: if ($alias === null) {
1191: $this->args[$what][] = $value;
1192: } else {
1193: if (isset($this->args[$what][$alias])) {
1194: throw (new Exception('Alias must be unique'))
1195: ->addMoreInfo('what', $what)
1196: ->addMoreInfo('alias', $alias);
1197: }
1198:
1199: $this->args[$what][$alias] = $value;
1200: }
1201: }
1202:
1203: // }}}
1204: }
1205:
ATK Documentation API documentation generated by ApiGen v7.0.0-alpha.6