src/Query.php
<?php
declare(strict_types=1);
namespace Atk4\Dsql;
use Doctrine\DBAL\Result as DbalResult;
/**
* Perform query operation on SQL server (such as select, insert, delete, etc).
*/
class Query extends Expression
{
/**
* Query will use one of the predefined templates. The $mode will contain
* name of template used. Basically it's part of Query property name -
* Query::template_[$mode].
*
* @var string
*/
public $mode = 'select';
/**
* If no fields are defined, this field is used.
*
* @var string|Expression
*/
public $defaultField = '*';
/** @var string Expression classname */
protected $expression_class = Expression::class;
/** @var bool */
public $wrapInParentheses = true;
/** @var string */
protected $template_select = '[with]select[option] [field] [from] [table][join][where][group][having][order][limit]';
/** @var string */
protected $template_insert = 'insert[option] into [table_noalias] ([set_fields]) values ([set_values])';
/** @var string */
protected $template_replace = 'replace[option] into [table_noalias] ([set_fields]) values ([set_values])';
/** @var string */
protected $template_delete = '[with]delete [from] [table_noalias][where][having]';
/** @var string */
protected $template_update = '[with]update [table_noalias] set [set] [where]';
/** @var string */
protected $template_truncate = 'truncate table [table_noalias]';
/**
* Name or alias of base table to use when using default join().
*
* This is set by table(). If you are using multiple tables,
* then $main_table is set to false as it is irrelevant.
*
* @var false|string|null
*/
protected $main_table;
// {{{ Field specification and rendering
/**
* Adds new column to resulting select by querying $field.
*
* Examples:
* $q->field('name');
*
* You can use a dot to prepend table name to the field:
* $q->field('user.name');
* $q->field('user.name')->field('address.line1');
*
* Array as a first argument will specify multiple fields, same as calling field() multiple times
* $q->field(['name', 'surname', 'address.line1']);
*
* You can pass first argument as Expression or Query
* $q->field( $q->expr('2+2'), 'alias'); // must always use alias
*
* You can use $q->dsql() for subqueries. Subqueries will be wrapped in
* brackets.
* $q->field( $q->dsql()->table('x')... , 'alias');
*
* Associative array will assume that "key" holds the field alias.
* Value may be field name, Expression or Query.
* $q->field(['alias' => 'name', 'alias2' => 'mother.surname']);
* $q->field(['alias' => $q->expr(..), 'alias2' => $q->dsql()->.. ]);
*
* If you need to use funky name for the field (e.g, one containing
* a dot or a space), you should wrap it into expression:
* $q->field($q->expr('{}', ['fun...ky.field']), 'f');
*
* @param mixed $field Specifies field to select
* @param string $alias Specify alias for this field
*
* @return $this
*/
public function field($field, $alias = null)
{
// field is passed as string, may contain commas
if (is_string($field) && strpos($field, ',') !== false) {
$field = explode(',', $field);
}
// recursively add array fields
if (is_array($field)) {
if ($alias !== null) {
throw (new Exception('Alias must not be specified when $field is an array'))
->addMoreInfo('alias', $alias);
}
foreach ($field as $alias => $f) {
$this->field($f, is_numeric($alias) ? null : $alias);
}
return $this;
}
// save field in args
$this->_set_args('field', $alias, $field);
return $this;
}
/**
* Returns template component for [field].
*
* @param bool $add_alias Should we add aliases, see _render_field_noalias()
*
* @return string Parsed template chunk
*/
protected function _render_field($add_alias = true): string
{
// will be joined for output
$ret = [];
// If no fields were defined, use defaultField
if (empty($this->args['field'])) {
if ($this->defaultField instanceof Expression) {
return $this->consume($this->defaultField);
}
return (string) $this->defaultField;
}
// process each defined field
foreach ($this->args['field'] as $alias => $field) {
// Do not add alias, if:
// - we don't want aliases OR
// - alias is the same as field OR
// - alias is numeric
if (
$add_alias === false
|| (is_string($field) && $alias === $field)
|| is_numeric($alias)
) {
$alias = null;
}
// Will parameterize the value and escape if necessary
$field = $this->consume($field, self::ESCAPE_IDENTIFIER_SOFT);
if ($alias) {
// field alias cannot be expression, so simply escape it
$field .= ' ' . $this->escapeIdentifier($alias);
}
$ret[] = $field;
}
return implode(', ', $ret);
}
protected function _render_field_noalias(): string
{
return $this->_render_field(false);
}
// }}}
// {{{ Table specification and rendering
/**
* Specify a table to be used in a query.
*
* @param mixed $table Specifies table
* @param string $alias Specify alias for this table
*
* @return $this
*/
public function table($table, $alias = null)
{
// comma-separated table names
if (is_string($table) && strpos($table, ',') !== false) {
$table = explode(',', $table);
}
// array of tables - recursively process each
if (is_array($table)) {
if ($alias !== null) {
throw (new Exception('You cannot use single alias with multiple tables'))
->addMoreInfo('alias', $alias);
}
foreach ($table as $alias => $t) {
if (is_numeric($alias)) {
$alias = null;
}
$this->table($t, $alias);
}
return $this;
}
// if table is set as sub-Query, then alias is mandatory
if ($table instanceof self && $alias === null) {
throw new Exception('If table is set as Query, then table alias is mandatory');
}
if (is_string($table) && $alias === null) {
$alias = $table;
}
// main_table will be set only if table() is called once.
// it's used as "default table" when joining with other tables, see join().
// on multiple calls, main_table will be false and we won't
// be able to join easily anymore.
$this->main_table = ($this->main_table === null && $alias !== null ? $alias : false);
// save table in args
$this->_set_args('table', $alias, $table);
return $this;
}
/**
* @param bool $add_alias Should we add aliases, see _render_table_noalias()
*/
protected function _render_table($add_alias = true): ?string
{
// will be joined for output
$ret = [];
if (empty($this->args['table'])) {
return '';
}
// process tables one by one
foreach ($this->args['table'] as $alias => $table) {
// throw exception if we don't want to add alias and table is defined as Expression
if ($add_alias === false && $table instanceof self) {
throw new Exception('Table cannot be Query in UPDATE, INSERT etc. query modes');
}
// Do not add alias, if:
// - we don't want aliases OR
// - alias is the same as table name OR
// - alias is numeric
if (
$add_alias === false
|| (is_string($table) && $alias === $table)
|| is_numeric($alias)
) {
$alias = null;
}
// consume or escape table
$table = $this->consume($table, self::ESCAPE_IDENTIFIER_SOFT);
// add alias if needed
if ($alias) {
$table .= ' ' . $this->escapeIdentifier($alias);
}
$ret[] = $table;
}
return implode(', ', $ret);
}
protected function _render_table_noalias(): ?string
{
return $this->_render_table(false);
}
protected function _render_from(): ?string
{
return empty($this->args['table']) ? '' : 'from';
}
/// }}}
// {{{ with()
/**
* Specify WITH query to be used.
*
* @param Query $cursor Specifies cursor query or array [alias=>query] for adding multiple
* @param string $alias Specify alias for this cursor
* @param array $fields Optional array of field names used in cursor
* @param bool $recursive Is it recursive?
*
* @return $this
*/
public function with(self $cursor, string $alias, array $fields = null, bool $recursive = false)
{
// save cursor in args
$this->_set_args('with', $alias, [
'cursor' => $cursor,
'fields' => $fields,
'recursive' => $recursive,
]);
return $this;
}
/**
* Recursive WITH query.
*
* @param Query $cursor Specifies cursor query or array [alias=>query] for adding multiple
* @param string $alias Specify alias for this cursor
* @param array $fields Optional array of field names used in cursor
*
* @return $this
*/
public function withRecursive(self $cursor, string $alias, array $fields = null)
{
return $this->with($cursor, $alias, $fields, true);
}
protected function _render_with(): ?string
{
// will be joined for output
$ret = [];
if (empty($this->args['with'])) {
return '';
}
// process each defined cursor
$isRecursive = false;
foreach ($this->args['with'] as $alias => ['cursor' => $cursor, 'fields' => $fields, 'recursive' => $recursive]) {
// cursor alias cannot be expression, so simply escape it
$s = $this->escapeIdentifier($alias) . ' ';
// set cursor fields
if ($fields !== null) {
$s .= '(' . implode(', ', array_map([$this, 'escapeIdentifier'], $fields)) . ') ';
}
// will parameterize the value and escape if necessary
$s .= 'as ' . $this->consume($cursor, self::ESCAPE_IDENTIFIER_SOFT);
// is at least one recursive ?
$isRecursive = $isRecursive || $recursive;
$ret[] = $s;
}
return 'with ' . ($isRecursive ? 'recursive ' : '') . implode(', ', $ret) . ' ';
}
/// }}}
// {{{ join()
/**
* Joins your query with another table. Join will use $main_table
* to reference the main table, unless you specify it explicitly.
*
* Examples:
* $q->join('address'); // on user.address_id=address.id
* $q->join('address.user_id'); // on address.user_id=user.id
* $q->join('address a'); // With alias
* $q->join(array('a'=>'address')); // Also alias
*
* Second argument may specify the field of the master table
* $q->join('address', 'billing_id');
* $q->join('address.code', 'code');
* $q->join('address.code', 'user.code');
*
* Third argument may specify which kind of join to use.
* $q->join('address', null, 'left');
* $q->join('address.code', 'user.code', 'inner');
*
* Using array syntax you can join multiple tables too
* $q->join(array('a'=>'address', 'p'=>'portfolio'));
*
* You can use expression for more complex joins
* $q->join('address',
* $q->orExpr()
* ->where('user.billing_id=address.id')
* ->where('user.technical_id=address.id')
* )
*
* @param string|array $foreign_table Table to join with
* @param mixed $master_field Field in master table
* @param string $join_kind 'left' or 'inner', etc
* @param string $_foreign_alias Internal, don't use
*
* @return $this
*/
public function join(
$foreign_table,
$master_field = null,
$join_kind = null,
$_foreign_alias = null
) {
// If array - add recursively
if (is_array($foreign_table)) {
foreach ($foreign_table as $alias => $foreign) {
if (is_numeric($alias)) {
$alias = null;
}
$this->join($foreign, $master_field, $join_kind, $alias);
}
return $this;
}
$j = [];
// try to find alias in foreign table definition. this behaviour should be deprecated
if ($_foreign_alias === null) {
[$foreign_table, $_foreign_alias] = array_pad(explode(' ', $foreign_table, 2), 2, null);
}
// Split and deduce fields
// NOTE that this will not allow table names with dots in there !!!
[$f1, $f2] = array_pad(explode('.', $foreign_table, 2), 2, null);
if (is_object($master_field)) {
$j['expr'] = $master_field;
} else {
// Split and deduce primary table
if ($master_field === null) {
[$m1, $m2] = [null, null];
} else {
[$m1, $m2] = array_pad(explode('.', $master_field, 2), 2, null);
}
if ($m2 === null) {
$m2 = $m1;
$m1 = null;
}
if ($m1 === null) {
$m1 = $this->main_table;
}
// Identify fields we use for joins
if ($f2 === null && $m2 === null) {
$m2 = $f1 . '_id';
}
if ($m2 === null) {
$m2 = 'id';
}
$j['m1'] = $m1;
$j['m2'] = $m2;
}
$j['f1'] = $f1;
if ($f2 === null) {
$f2 = 'id';
}
$j['f2'] = $f2;
$j['t'] = $join_kind ?: 'left';
$j['fa'] = $_foreign_alias;
$this->args['join'][] = $j;
return $this;
}
public function _render_join(): ?string
{
if (!isset($this->args['join'])) {
return '';
}
$joins = [];
foreach ($this->args['join'] as $j) {
$jj = '';
$jj .= $j['t'] . ' join ';
$jj .= $this->escapeIdentifierSoft($j['f1']);
if ($j['fa'] !== null) {
$jj .= ' ' . $this->escapeIdentifier($j['fa']);
}
$jj .= ' on ';
if (isset($j['expr'])) {
$jj .= $this->consume($j['expr']);
} else {
$jj .=
$this->escapeIdentifier($j['fa'] ?: $j['f1']) . '.' .
$this->escapeIdentifier($j['f2']) . ' = ' .
($j['m1'] === null ? '' : $this->escapeIdentifier($j['m1']) . '.') .
$this->escapeIdentifier($j['m2']);
}
$joins[] = $jj;
}
return ' ' . implode(' ', $joins);
}
// }}}
// {{{ where() and having() specification and rendering
/**
* Adds condition to your query.
*
* Examples:
* $q->where('id',1);
*
* By default condition implies equality. You can specify a different comparison
* operator by either including it along with the field or using 3-argument
* format:
* $q->where('id>','1');
* $q->where('id','>',1);
*
* You may use Expression as any part of the query.
* $q->where($q->expr('a=b'));
* $q->where('date>',$q->expr('now()'));
* $q->where($q->expr('length(password)'),'>',5);
*
* If you specify Query as an argument, it will be automatically
* surrounded by brackets:
* $q->where('user_id',$q->dsql()->table('users')->field('id'));
*
* To specify OR conditions:
* $q->where($q->orExpr()->where('a',1)->where('b',1));
*
* @param mixed $field Field or Expression
* @param mixed $cond Condition such as '=', '>' or 'is not'
* @param mixed $value Value. Will be quoted unless you pass expression
* @param string $kind Do not use directly. Use having()
* @param int $num_args when $kind is passed, we can't determine number of
* actual arguments, so this argument must be specified
*
* @return $this
*/
public function where($field, $cond = null, $value = null, $kind = 'where', $num_args = null)
{
// Number of passed arguments will be used to determine if arguments were specified or not
if ($num_args === null) {
$num_args = func_num_args();
}
// Array as first argument means we have to replace it with orExpr()
// remove in v2.5
if (is_array($field)) {
throw new Exception('Array input / OR conditions is no longer supported');
}
// first argument is string containing more than just a field name and no more than 2
// arguments means that we either have a string expression or embedded condition.
if ($num_args === 2 && is_string($field) && !preg_match('/^[.a-zA-Z0-9_]*$/', $field)) {
// field contains non-alphanumeric values. Look for condition
preg_match(
'/^([^ <>!=]*)([><!=]*|( *(not|is|in|like))*) *$/',
$field,
$matches
);
// matches[2] will contain the condition, but $cond will contain the value
$value = $cond;
$cond = $matches[2];
// if we couldn't clearly identify the condition, we might be dealing with
// a more complex expression. If expression is followed by another argument
// we need to add equation sign where('now()',123).
if (!$cond) {
$matches[1] = $this->expr($field);
$cond = '=';
} else {
++$num_args;
}
$field = $matches[1];
}
switch ($num_args) {
case 1:
if (is_string($field)) {
$field = $this->expr($field);
$field->wrapInParentheses = true;
} elseif (!$field->wrapInParentheses) {
$field = $this->expr('[]', [$field]);
$field->wrapInParentheses = true;
}
$this->args[$kind][] = [$field];
break;
case 2:
if (is_object($cond) && !$cond instanceof Expressionable) {
throw (new Exception('Value cannot be converted to SQL-compatible expression'))
->addMoreInfo('field', $field)
->addMoreInfo('value', $cond);
}
$this->args[$kind][] = [$field, $cond];
break;
case 3:
if (is_object($value) && !$value instanceof Expressionable) {
throw (new Exception('Value cannot be converted to SQL-compatible expression'))
->addMoreInfo('field', $field)
->addMoreInfo('cond', $cond)
->addMoreInfo('value', $value);
}
$this->args[$kind][] = [$field, $cond, $value];
break;
}
return $this;
}
/**
* Same syntax as where().
*
* @param mixed $field Field or Expression
* @param mixed $cond Condition such as '=', '>' or 'is not'
* @param mixed $value Value. Will be quoted unless you pass expression
*
* @return $this
*/
public function having($field, $cond = null, $value = null)
{
return $this->where($field, $cond, $value, 'having', func_num_args());
}
/**
* Subroutine which renders either [where] or [having].
*
* @param string $kind 'where' or 'having'
*
* @return string[]
*/
protected function _sub_render_where($kind): array
{
// will be joined for output
$ret = [];
// where() might have been called multiple times. Collect all conditions,
// then join them with AND keyword
foreach ($this->args[$kind] as $row) {
$ret[] = $this->_sub_render_condition($row);
}
return $ret;
}
protected function _sub_render_condition(array $row): string
{
if (count($row) === 3) {
[$field, $cond, $value] = $row;
} elseif (count($row) === 2) {
[$field, $cond] = $row;
} elseif (count($row) === 1) {
[$field] = $row;
} else {
throw new \InvalidArgumentException();
}
$field = $this->consume($field, self::ESCAPE_IDENTIFIER_SOFT);
if (count($row) === 1) {
// Only a single parameter was passed, so we simply include all
return $field;
}
// below are only cases when 2 or 3 arguments are passed
// if no condition defined - set default condition
if (count($row) === 2) {
$value = $cond; // @phpstan-ignore-line see https://github.com/phpstan/phpstan/issues/4173
if ($value instanceof Expressionable) {
$value = $value->getDsqlExpression($this);
}
if (is_array($value)) {
$cond = 'in';
} elseif ($value instanceof self && $value->mode === 'select') {
$cond = 'in';
} else {
$cond = '=';
}
} else {
$cond = trim(strtolower($cond)); // @phpstan-ignore-line see https://github.com/phpstan/phpstan/issues/4173
}
// below we can be sure that all 3 arguments has been passed
// special conditions (IS | IS NOT) if value is null
if ($value === null) { // @phpstan-ignore-line see https://github.com/phpstan/phpstan/issues/4173
if (in_array($cond, ['=', 'is'], true)) {
return $field . ' is null';
} elseif (in_array($cond, ['!=', '<>', 'not', 'is not'], true)) {
return $field . ' is not null';
}
}
// value should be array for such conditions
if (in_array($cond, ['in', 'not in', 'not'], true) && is_string($value)) {
$value = array_map('trim', explode(',', $value));
}
// special conditions (IN | NOT IN) if value is array
if (is_array($value)) {
$cond = in_array($cond, ['!=', '<>', 'not', 'not in'], true) ? 'not in' : 'in';
// special treatment of empty array condition
if (empty($value)) {
if ($cond === 'in') {
return '1 = 0'; // never true
}
return '1 = 1'; // always true
}
$value = '(' . implode(', ', array_map(function ($v) { return $this->escapeParam($v); }, $value)) . ')';
return $field . ' ' . $cond . ' ' . $value;
}
// if value is object, then it should be Expression or Query itself
// otherwise just escape value
$value = $this->consume($value, self::ESCAPE_PARAM);
return $field . ' ' . $cond . ' ' . $value;
}
protected function _render_where(): ?string
{
if (!isset($this->args['where'])) {
return null;
}
return ' where ' . implode(' and ', $this->_sub_render_where('where'));
}
protected function _render_orwhere(): ?string
{
if (isset($this->args['where']) && isset($this->args['having'])) {
throw new Exception('Mixing of WHERE and HAVING conditions not allowed in query expression');
}
foreach (['where', 'having'] as $kind) {
if (isset($this->args[$kind])) {
return implode(' or ', $this->_sub_render_where($kind));
}
}
return null;
}
protected function _render_andwhere(): ?string
{
if (isset($this->args['where']) && isset($this->args['having'])) {
throw new Exception('Mixing of WHERE and HAVING conditions not allowed in query expression');
}
foreach (['where', 'having'] as $kind) {
if (isset($this->args[$kind])) {
return implode(' and ', $this->_sub_render_where($kind));
}
}
return null;
}
protected function _render_having(): ?string
{
if (!isset($this->args['having'])) {
return null;
}
return ' having ' . implode(' and ', $this->_sub_render_where('having'));
}
// }}}
// {{{ group()
/**
* Implements GROUP BY functionality. Simply pass either field name
* as string or expression.
*
* @param mixed $group Group by this
*
* @return $this
*/
public function group($group)
{
// Case with comma-separated fields
if (is_string($group) && !$this->isUnescapablePattern($group) && strpos($group, ',') !== false) {
$group = explode(',', $group);
}
if (is_array($group)) {
foreach ($group as $g) {
$this->args['group'][] = $g;
}
return $this;
}
$this->args['group'][] = $group;
return $this;
}
protected function _render_group(): ?string
{
if (!isset($this->args['group'])) {
return '';
}
$g = array_map(function ($a) {
return $this->consume($a, self::ESCAPE_IDENTIFIER_SOFT);
}, $this->args['group']);
return ' group by ' . implode(', ', $g);
}
// }}}
// {{{ Set field implementation
/**
* Sets field value for INSERT or UPDATE statements.
*
* @param string|Expression|Expressionable|array $field Name of the field
* @param mixed $value Value of the field
*
* @return $this
*/
public function set($field, $value = null)
{
if (is_array($value)) {
throw (new Exception('Array values are not supported by SQL'))
->addMoreInfo('field', $field)
->addMoreInfo('value', $value);
}
if (is_array($field)) {
foreach ($field as $key => $value) {
$this->set($key, $value);
}
return $this;
}
if (is_string($field) || $field instanceof Expressionable) {
$this->args['set'][] = [$field, $value];
} else {
throw (new Exception('Field name should be string or Expressionable'))
->addMoreInfo('field', $field);
}
return $this;
}
protected function _render_set(): ?string
{
// will be joined for output
$ret = [];
if (isset($this->args['set']) && $this->args['set']) {
foreach ($this->args['set'] as [$field, $value]) {
$field = $this->consume($field, self::ESCAPE_IDENTIFIER);
$value = $this->consume($value, self::ESCAPE_PARAM);
$ret[] = $field . '=' . $value;
}
}
return implode(', ', $ret);
}
protected function _render_set_fields(): ?string
{
// will be joined for output
$ret = [];
if ($this->args['set']) {
foreach ($this->args['set'] as [$field/*, $value*/]) {
$field = $this->consume($field, self::ESCAPE_IDENTIFIER);
$ret[] = $field;
}
}
return implode(', ', $ret);
}
protected function _render_set_values(): ?string
{
// will be joined for output
$ret = [];
if ($this->args['set']) {
foreach ($this->args['set'] as [/*$field*/ , $value]) {
$value = $this->consume($value, self::ESCAPE_PARAM);
$ret[] = $value;
}
}
return implode(', ', $ret);
}
// }}}
// {{{ Option
/**
* Set options for particular mode.
*
* @param mixed $option
* @param string $mode select|insert|replace
*
* @return $this
*/
public function option($option, $mode = 'select')
{
// Case with comma-separated options
if (is_string($option) && strpos($option, ',') !== false) {
$option = explode(',', $option);
}
if (is_array($option)) {
foreach ($option as $opt) {
$this->args['option'][$mode][] = $opt;
}
return $this;
}
$this->args['option'][$mode][] = $option;
return $this;
}
protected function _render_option(): ?string
{
if (!isset($this->args['option'][$this->mode])) {
return '';
}
return ' ' . implode(' ', $this->args['option'][$this->mode]);
}
// }}}
// {{{ Query Modes
/**
* Execute select statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function select(): object
{
return $this->mode('select')->execute();
}
/**
* Execute insert statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function insert(): object
{
return $this->mode('insert')->execute();
}
/**
* Execute update statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function update(): object
{
return $this->mode('update')->execute();
}
/**
* Execute replace statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function replace(): object
{
return $this->mode('replace')->execute();
}
/**
* Execute delete statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function delete(): object
{
return $this->mode('delete')->execute();
}
/**
* Execute truncate statement.
*
* @return DbalResult|\PDOStatement PDOStatement iff for DBAL 2.x
*/
public function truncate(): object
{
return $this->mode('truncate')->execute();
}
// }}}
// {{{ Limit
/**
* Limit how many rows will be returned.
*
* @param int $cnt Number of rows to return
* @param int $shift Offset, how many rows to skip
*
* @return $this
*/
public function limit($cnt, $shift = null)
{
$this->args['limit'] = [
'cnt' => $cnt,
'shift' => $shift,
];
return $this;
}
public function _render_limit(): ?string
{
if (!isset($this->args['limit'])) {
return null;
}
return ' limit ' .
(int) $this->args['limit']['shift'] .
', ' .
(int) $this->args['limit']['cnt'];
}
// }}}
// {{{ Order
/**
* Orders results by field or Expression. See documentation for full
* list of possible arguments.
*
* $q->order('name');
* $q->order('name desc');
* $q->order('name desc, id asc')
* $q->order('name',true);
*
* @param string|Expressionable|array $order order by
* @param string|bool $desc true to sort descending
*
* @return $this
*/
public function order($order, $desc = null)
{
// Case with comma-separated fields or first argument being an array
if (is_string($order) && strpos($order, ',') !== false) {
$order = explode(',', $order);
}
if (is_array($order)) {
if ($desc !== null) {
throw new Exception(
'If first argument is array, second argument must not be used'
);
}
foreach (array_reverse($order) as $o) {
$this->order($o);
}
return $this;
}
// First argument may contain space, to divide field and ordering keyword.
// Explode string only if ordering keyword is 'desc' or 'asc'.
if ($desc === null && is_string($order) && strpos($order, ' ') !== false) {
$_chunks = explode(' ', $order);
$_desc = strtolower(array_pop($_chunks));
if (in_array($_desc, ['desc', 'asc'], true)) {
$order = implode(' ', $_chunks);
$desc = $_desc;
}
}
if (is_bool($desc)) {
$desc = $desc ? 'desc' : '';
} elseif (strtolower($desc ?? '') === 'asc') {
$desc = '';
}
// no else - allow custom order like "order by name desc nulls last" for Oracle
$this->args['order'][] = [$order, $desc];
return $this;
}
public function _render_order(): ?string
{
if (!isset($this->args['order'])) {
return '';
}
$x = [];
foreach ($this->args['order'] as $tmp) {
[$arg, $desc] = $tmp;
$x[] = $this->consume($arg, self::ESCAPE_IDENTIFIER_SOFT) . ($desc ? (' ' . $desc) : '');
}
return ' order by ' . implode(', ', array_reverse($x));
}
// }}}
// {{{ Exists
/**
* Creates 'select exists' query based on the query object.
*
* @return self
*/
public function exists()
{
return $this->dsql()->mode('select')->option('exists')->field($this);
}
// }}}
public function __debugInfo(): array
{
$arr = [
'R' => false,
'mode' => $this->mode,
//'template' => $this->template,
//'params' => $this->params,
//'connection' => $this->connection,
//'main_table' => $this->main_table,
//'args' => $this->args,
];
try {
$arr['R'] = $this->getDebugQuery();
} catch (\Exception $e) {
$arr['R'] = $e->getMessage();
}
return $arr;
}
// {{{ Miscelanious
/**
* Renders query template. If the template is not explicitly set will use "select" mode.
*/
public function render(): string
{
if (!$this->template) {
$this->mode('select');
}
return parent::render();
}
/**
* Switch template for this query. Determines what would be done
* on execute.
*
* By default it is in SELECT mode
*
* @param string $mode
*
* @return $this
*/
public function mode($mode)
{
$prop = 'template_' . $mode;
if (isset($this->{$prop})) {
$this->mode = $mode;
$this->template = $this->{$prop};
} else {
throw (new Exception('Query does not have this mode'))
->addMoreInfo('mode', $mode);
}
return $this;
}
/**
* Use this instead of "new Query()" if you want to automatically bind
* query to the same connection as the parent.
*
* @param array $properties
*
* @return Query
*/
public function dsql($properties = [])
{
$q = new static($properties);
$q->connection = $this->connection;
return $q;
}
/**
* Returns Expression object for the corresponding Query
* sub-class (e.g. Mysql\Query will return Mysql\Expression).
*
* Connection is not mandatory, but if set, will be preserved. This
* method should be used for building parts of the query internally.
*
* @param string|array $properties
* @param array $arguments
*
* @return Expression
*/
public function expr($properties = [], $arguments = null)
{
$c = $this->expression_class;
$e = new $c($properties, $arguments);
$e->connection = $this->connection;
return $e;
}
/**
* Returns Expression object for NOW() or CURRENT_TIMESTAMP() method.
*/
public function exprNow(int $precision = null): Expression
{
return $this->expr(
'current_timestamp(' . ($precision !== null ? '[]' : '') . ')',
$precision !== null ? [$precision] : []
);
}
/**
* Returns new Query object of [or] expression.
*
* @return Query
*/
public function orExpr()
{
return $this->dsql(['template' => '[orwhere]']);
}
/**
* Returns new Query object of [and] expression.
*
* @return Query
*/
public function andExpr()
{
return $this->dsql(['template' => '[andwhere]']);
}
/**
* Returns Query object of [case] expression.
*
* @param mixed $operand optional operand for case expression
*
* @return Query
*/
public function caseExpr($operand = null)
{
$q = $this->dsql(['template' => '[case]']);
if ($operand !== null) {
$q->args['case_operand'] = $operand;
}
return $q;
}
/**
* Returns a query for a function, which can be used as part of the GROUP
* query which would concatenate all matching fields.
*
* MySQL, SQLite - group_concat
* PostgreSQL - string_agg
* Oracle - listagg
*
* @param mixed $field
*
* @return Expression
*/
public function groupConcat($field, string $delimiter = ',')
{
throw new Exception('groupConcat() is SQL-dependent, so use a correct class');
}
/**
* Add when/then condition for [case] expression.
*
* @param mixed $when Condition as array for normal form [case] statement or just value in case of short form [case] statement
* @param mixed $then Then expression or value
*
* @return $this
*/
public function when($when, $then)
{
$this->args['case_when'][] = [$when, $then];
return $this;
}
/**
* Add else condition for [case] expression.
*
* @param mixed $else Else expression or value
*
* @return $this
*/
//public function else($else) // PHP 5.6 restricts to use such method name. PHP 7 is fine with it
public function otherwise($else)
{
$this->args['case_else'] = $else;
return $this;
}
protected function _render_case(): ?string
{
if (!isset($this->args['case_when'])) {
return null;
}
$ret = '';
// operand
if ($short_form = isset($this->args['case_operand'])) {
$ret .= ' ' . $this->consume($this->args['case_operand'], self::ESCAPE_IDENTIFIER_SOFT);
}
// when, then
foreach ($this->args['case_when'] as $row) {
if (!array_key_exists(0, $row) || !array_key_exists(1, $row)) {
throw (new Exception('Incorrect use of "when" method parameters'))
->addMoreInfo('row', $row);
}
$ret .= ' when ';
if ($short_form) {
// short-form
if (is_array($row[0])) {
throw (new Exception('When using short form CASE statement, then you should not set array as when() method 1st parameter'))
->addMoreInfo('when', $row[0]);
}
$ret .= $this->consume($row[0], self::ESCAPE_PARAM);
} else {
$ret .= $this->_sub_render_condition($row[0]);
}
// then
$ret .= ' then ' . $this->consume($row[1], self::ESCAPE_PARAM);
}
// else
if (array_key_exists('case_else', $this->args)) {
$ret .= ' else ' . $this->consume($this->args['case_else'], self::ESCAPE_PARAM);
}
return ' case' . $ret . ' end';
}
/**
* Sets value in args array. Doesn't allow duplicate aliases.
*
* @param string $what Where to set it - table|field
* @param string|null $alias Alias name
* @param mixed $value Value to set in args array
*/
protected function _set_args($what, $alias, $value): void
{
// save value in args
if ($alias === null) {
$this->args[$what][] = $value;
} else {
// don't allow multiple values with same alias
if (isset($this->args[$what][$alias])) {
throw (new Exception('Alias should be unique'))
->addMoreInfo('what', $what)
->addMoreInfo('alias', $alias);
}
$this->args[$what][$alias] = $value;
}
}
/// }}}
}