Class Atk4\Data\Persistence\Sql\Query

`abstract`
`extends`	`Expression`

Perform query operation on SQL server (such as select, insert, delete, etc).

Atk4\Data\Persistence\Sql\Expression implements Expressionable, ArrayAccess<int|string, mixed> uses DiContainerTrait
- Atk4\Data\Persistence\Sql\Query

Located at data/src/Persistence/Sql/Query.php

Methods


					
	public

field(string|Expressionable $field, string $alias = null): $this

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');

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 parentheses. $q->field($q->dsql()->table('x')..., 'alias');

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');

Parameters

`$field`	Specifies field to select
`$alias`	Specify alias for this field


					
	protected

_renderField(bool $addAlias = true): string

Returns template component for [field].

Parameters

$addAlias

Should we add aliases, see _renderFieldNoalias()

Returns

Parsed template chunk


					
	protected

_renderFieldNoalias(): string


					
	public

table(string|Expressionable $table, string $alias = null): $this

Specify a table to be used in a query.

Parameters

`$table`	Specifies table
`$alias`	Specify alias for this table


					
	protected

getMainTable(): string|false|null

Name or alias of base table to use when using default join().

It is set by table(). If you are using multiple tables, then false is returned as it is irrelevant.


					
	protected

_renderTable(bool $addAlias = true): ?string

Parameters

$addAlias

Should we add aliases, see _renderTableNoalias()


					
	protected

_renderTableNoalias(): ?string


					
	protected

_renderFrom(): ?string


					
	public

with(Query $cursor, string $alias, array<int, string>|null $fields = null, bool $recursive = false): $this

Specify WITH query to be used.

Parameters

`$cursor`	Specifies cursor query or array [alias => query] for adding multiple
`$alias`	Specify alias for this cursor
`$fields`	Optional array of field names used in cursor
`$recursive`	Is it recursive?


					
	protected

_renderWith(): ?string


					
	public

join(
	string $foreignTable,
	string|Expression $masterField = null,
	string $joinKind = null,
	string $foreignAlias = null,
): $this

Joins your query with another table. Join will use $this->getMainTable() to reference the main table, unless you…

Joins your query with another table. Join will use $this->getMainTable() 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

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');

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') )

Parameters

`$foreignTable`	Table to join with
`$masterField`	Field in master table
`$joinKind`	'left' or 'inner', etc


					
	protected

_renderJoin(): ?string


					
	public

where(
	string|Expressionable $field,
	mixed $cond = null,
	mixed $value = null,
	string $kind = 'where',
	int $numArgs = null,
): $this

Adds condition to your query.

Examples: $q->where('id', 1);

By default condition implies equality. You can specify a different comparison operator by using 3-argument format: $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 parentheses: $q->where('user_id', $q->dsql()->table('users')->field('id'));

To specify OR conditions: $q->where($q->orExpr()->where('a', 1)->where('b', 1));

Parameters

`$field`	Field or Expression
`$cond`	Condition such as '=', '>' or 'not like'
`$value`	Value. Will be quoted unless you pass expression
`$kind`	Do not use directly. Use having()
`$numArgs`	when $kind is passed, we can't determine number of actual arguments, so this argument must be specified


					
	public

having(string|Expressionable $field, mixed $cond = null, mixed $value = null): $this

Same syntax as where().

Parameters

`$field`	Field or Expression
`$cond`	Condition such as '=', '>' or 'not like'
`$value`	Value. Will be quoted unless you pass expression


					
	protected

_subrenderWhere(string $kind): list<string>

Subroutine which renders either [where] or [having].

Parameters

$kind

'where' or 'having'


					
	protected

_renderConditionBinary(string $operator, string $sqlLeft, string $sqlRight): string

Override to fix numeric affinity for SQLite.

Overriden by

Atk4\Data\Persistence\Sql\Sqlite\Query::_renderConditionBinary()


					
	protected

_renderConditionInOperator(bool $negated, string $sqlLeft, non-empty-list<string> $sqlValues): string

Override to fix numeric affinity for SQLite.

Overriden by

Atk4\Data\Persistence\Sql\Sqlite\Query::_renderConditionInOperator()


					
	protected

_subrenderCondition(array<0|1|2, mixed> $row): string

Overriden by


					
	protected

_renderWhere(): ?string


					
	protected

_renderOrwhere(): ?string


					
	protected

_renderAndwhere(): ?string


					
	protected

_renderHaving(): ?string


					
	public

group(string|Expressionable $group): $this

Implements GROUP BY functionality. Simply pass either field name as string or expression.


					
	protected

_renderGroup(): ?string


					
	public

set(string|Expressionable $field, mixed $value = null): $this

Sets field value for INSERT or UPDATE statements.

Parameters

`$field`	Name of the field
`$value`	Value of the field


					
	public

setMulti(array<string, mixed> $fields): $this


					
	protected

_renderSet(): ?string


					
	protected

_renderSetFields(): ?string


					
	protected

_renderSetValues(): ?string


					
	public

option(string|Expressionable $option, string $mode = 'select'): $this

Set options for particular mode.


					
	protected

_renderOption(): ?string


					
	public

order(
	string|Expressionable|array<int, string|Expressionable> $order,
	($order is array ? never : string|bool) $direction = null,
): $this

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);

Parameters

`$order`	order by
`$direction`	true to sort descending


					
	protected

deduplicateRenderOrder(list<string> $sqls): list<string>

Overriden by

Atk4\Data\Persistence\Sql\Mssql\Query::deduplicateRenderOrder()


					
	protected

_renderOrder(): ?string


					
	public

limit(int $cnt, int $shift = null): $this

Limit how many rows will be returned.

Parameters

`$cnt`	Number of rows to return
`$shift`	Offset, how many rows to skip


					
	protected

_renderLimit(): ?string

Overriden by


					
	public

exists(): self

Creates 'select exists' query based on the query object.

Overriden by


					
	public

__debugInfo(): array

Overrides

Atk4\Data\Persistence\Sql\Expression::__debugInfo()


					
	public

render(): array

Renders query template. If the template is not explicitly use "select" mode.

Overrides

Atk4\Data\Persistence\Sql\Expression::render()

Overriden by

Atk4\Data\Persistence\Sql\Oracle\Query::render()


					
	protected

renderNested(): array

Overrides

Atk4\Data\Persistence\Sql\Expression::renderNested()


					
	public

mode(string $mode): $this

Switch template for this query. Determines what would be done on execute.

By default it is in SELECT mode


					
	public

expr($template = [], array $arguments = []): Expression

Create Expression object with the same connection.

Overrides

Atk4\Data\Persistence\Sql\Expression::expr()


					
	public

dsql(string|array<string, mixed> $defaults = []): self

Create Query object with the same connection.


					
	public

exprNow(int $precision = null): Expression

Returns Expression object for NOW() or CURRENT_TIMESTAMP() method.


					
	public

orExpr(): self

Returns new Query object of [or] expression.


					
	public

andExpr(): self

Returns new Query object of [and] expression.


					
	public

groupConcat(string|Expressionable $field, string $separator = ','): Expression

Returns a query for a function, which can be used as part of the GROUP query which would concatenate all matching…

Returns a query for a function, which can be used as part of the GROUP query which would concatenate all matching fields.

Overriden by


					
	public

caseExpr(mixed $operand = null): self

Returns Query object of [case] expression.

Parameters

$operand

optional operand for case expression


					
	public

caseWhen(mixed $when, mixed $then): $this

Add when/then condition for [case] expression.

Parameters

`$when`	Condition as array for normal form [case] statement or just value in case of short form [case] statement
`$then`	Then expression or value


					
	public

caseElse(mixed $else): $this

Add else condition for [case] expression.

Parameters

$else

Else expression or value


					
	protected

_renderCase(): ?string


					
	protected

_setArgs(string $what, string|null $alias, mixed $value): void

Sets value in args array. Doesn't allow duplicate aliases.

Parameters

`$what`	Where to set it - table\|field
`$alias`	Alias name
`$value`	Value to set in args array

Methods inherited from Atk4\Data\Persistence\Sql\Expression
`__construct(), getDsqlExpression(), offsetExists(), offsetGet(), offsetSet(), offsetUnset(), reset(), consume(), escapeParam(), escapeStringLiteral(), escapeIdentifier(), escapeIdentifierSoft(), isUnescapableIdentifier(), getDebugQuery(), hasNativeNamedParamSupport(), updateRenderBeforeExecute(), _executeStatement(), _execute(), executeQuery(), executeStatement(), castFloatToString(), getRowsIterator(), getRows(), getRow(), getOne()`

Methods inherited from Atk4\Data\Persistence\Sql\Expression

__construct(), getDsqlExpression(), offsetExists(), offsetGet(), offsetSet(), offsetUnset(), reset(), consume(), escapeParam(), escapeStringLiteral(), escapeIdentifier(), escapeIdentifierSoft(), isUnescapableIdentifier(), getDebugQuery(), hasNativeNamedParamSupport(), updateRenderBeforeExecute(), _executeStatement(), _execute(), executeQuery(), executeStatement(), castFloatToString(), getRowsIterator(), getRows(), getRow(), getOne()

Methods used from Atk4\Core\DiContainerTrait
`setDefaults(), setMissingProperty(), assertInstanceOf(), _fromSeedPrecheck(), fromSeed(), fromSeedUnsafe()`

Constants inherited from Atk4\Data\Persistence\Sql\Expression
`ESCAPE_PARAM, ESCAPE_IDENTIFIER, ESCAPE_IDENTIFIER_SOFT, ESCAPE_NONE, QUOTED_TOKEN_REGEX`

Properties
`public`	`string`	`$mode = 'select'` Template name for render. Template name for render.	#
`public`	`string\|Expression`	`$defaultField = '*'` If no fields are defined, this field is used. If no fields are defined, this field is used.	#
`protected`	`class-string<Expression>`	`$expressionClass`	#
`public`	`bool`	`$wrapInParentheses = true`	#
`protected`	`array<string>`	`$supportedOperators = ['=', '!=', '<', '>', '<=', '>=', 'like', 'not like', 'in', 'not in']`	#
`protected`	`string`	`$templateSelect = '[with]select[option] [field] [from] [table][join][where][group][having][order][limit]'`	#
`protected`	`string`	`$templateInsert = 'insert[option] into [tableNoalias] ([setFields]) values ([setValues])'`	#
`protected`	`string`	`$templateReplace = 'replace[option] into [tableNoalias] ([setFields]) values ([setValues])'`	#
`protected`	`string`	`$templateDelete = '[with]delete [from] [tableNoalias][where][having]'`	#
`protected`	`string`	`$templateUpdate = '[with]update [tableNoalias] set [set] [where]'`	#
`protected`	`string`	`$templateTruncate = 'truncate table [tableNoalias]'`	#

Properties inherited from Atk4\Data\Persistence\Sql\Expression
`$template, $args, $paramBase, $identifierEscapeChar, $renderParamBase, $renderParams, $connection,`

Namespaces

Classes

Interfaces

Traits

Exceptions

Class Atk4\Data\Persistence\Sql\Query

Parameters

Parameters

Returns

Parameters

Parameters

Parameters

Parameters

Parameters

Parameters

Parameters

Overriden by

Overriden by

Overriden by

Parameters

Parameters

Overriden by

Parameters

Overriden by

Overriden by

Overrides

Overrides

Overriden by

Overrides

Overrides

Overriden by

Parameters

Parameters

Parameters

Parameters