ExpressionBuilder

class \TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder

Extends
ExpressionBuilder

ExpressionBuilder class is responsible to dynamically create SQL query parts.

It takes care building query conditions while ensuring table and column names are quoted within the created expressions / SQL fragments. It is a facade to the actual Doctrine ExpressionBuilder.

The ExpressionBuilder is used within the context of the QueryBuilder to ensure queries are being build based on the requirements of the database platform in use.

Table of Contents

Properties

$connection  : Connection
$container  : ContainerInterface

Methods

__construct()  : mixed
and()  : CompositeExpression
Creates a conjunction of the given boolean expressions
as()  : string
Creates an expression to alias a value, field value or sub-expression.
avg()  : string
Creates an AVG expression for the given field/alias.
bitAnd()  : string
Creates a bitwise AND expression with the given arguments.
castInt()  : string
Create a `CAST` statement to cast a value or expression result to signed integer type.
castText()  : string
Creates a cast for the `$expression` result to a text datatype depending on the database management system.
castVarchar()  : string
Create a `CAST()` statement to cast value or expression to a varchar with a given dynamic max length.
comparison()  : string
Creates a comparison expression.
concat()  : string
Concatenate multiple values or expressions into one string value.
count()  : string
Creates a COUNT expression for the given field/alias.
eq()  : string
Creates an equality comparison expression with the given arguments.
getContainer()  : ContainerInterface
gt()  : string
Creates a greater-than comparison expression with the given arguments.
gte()  : string
Creates a greater-than-equal comparison expression with the given arguments.
if()  : string
Creates IF-THEN-ELSE expression construct compatible with all supported database vendors.
in()  : string
Creates an IN () comparison expression with the given arguments.
inSet()  : string
Returns a comparison that is true if the comma-separated list stored in $fieldName contains $value, e.g. inSet('fe_group', $queryBuilder->quote('2')) matches a record with fe_group = '1,2,5'.
isNotNull()  : string
Creates an IS NOT NULL expression with the given arguments.
isNull()  : string
Creates an IS NULL expression with the given arguments.
left()  : string
Extract $length character of $value from the right side.
leftPad()  : string
Left-pad the value or sub-expression result with $paddingValue, to a total length of $length.
length()  : string
Creates a LENGTH expression for the given field/alias.
like()  : string
Creates a LIKE() comparison expression with the given arguments.
literal()  : string
Quotes a given input parameter.
lt()  : string
Creates a lower-than comparison expression with the given arguments.
lte()  : string
Creates a lower-than-equal comparison expression with the given arguments.
max()  : string
Creates a MAX expression for the given field/alias.
min()  : string
Creates a MIN expression for the given field/alias.
neq()  : string
Creates a non equality comparison expression with the given arguments.
notIn()  : string
Creates a NOT IN () comparison expression with the given arguments.
notInSet()  : string
Returns a comparison that is true if the comma-separated list stored in $fieldName does not contain $value. This is the negation of inSet(), see there for details on the argument order.
notLike()  : string
Creates a NOT LIKE() comparison expression with the given arguments.
or()  : CompositeExpression
Creates a disjunction of the given boolean expressions.
repeat()  : string
Create a statement to generate a value repeating defined $value for $numberOfRepeats times.
right()  : string
Extract $length character of $value from the right side.
rightPad()  : string
Right-pad the value or sub-expression result with $paddingValue, to a total length of $length.
space()  : string
Create statement containing $numberOfSpaces spaces.
sum()  : string
Creates a SUM expression for the given field/alias.
trim()  : string
Creates a TRIM expression for the given field.
calculation()  : string
Create an SQL aggregate function.
unquoteLiteral()  : string
Unquote a string literal. Used to unquote values for internal platform adjustments.

Properties

$connection read-only

protected Connection $connection

$container read-only

protected ContainerInterface $container

Methods

__construct()

public __construct(Connection $connection, ContainerInterface $container) : mixed

Parameters

$connection : Connection
$container : ContainerInterface

as()

Creates an expression to alias a value, field value or sub-expression.

public as(string $expression[, string $asIdentifier = '' ]) : string

Example:

$queryBuilder->selectLiteral(
  $queryBuilder->quoteIdentifier('uid'),
  $queryBuilder->expr()->as('(1 + 1 + 1)', 'calculated_field'),
);

Result with MySQL:

(1 + 1 + 1) AS `calculated_field`

Parameters

$expression : string

Value, identifier or expression which should be aliased

$asIdentifier : string = ''

Alias identifier

Return values

string —

Returns aliased expression

avg()

Creates an AVG expression for the given field/alias.

public avg(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

bitAnd()

Creates a bitwise AND expression with the given arguments.

public bitAnd(string $fieldName, int $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : int

Argument to be used in the bitwise AND operation

Return values

string

castInt()

Create a `CAST` statement to cast a value or expression result to signed integer type.

public castInt(string $value[, string $asIdentifier = '' ]) : string

Be aware that some database vendors will emit an error if an invalid type has been provided (PostgreSQL), and other silently return valid integer from the string discarding the non-integer part (starting with digits) or silently returning unrelated integer value. Use with care.

No automatic quoting or value casting! Ensure each part evaluates to a valid value!

Example:

$queryBuilder->expr()->castInt(
 '(' . '1 * 10' . ')',
 'virtual_field',
);

Result with MySQL:

CAST(('1 * 10') AS INTEGER) AS `virtual_field`

Parameters

$value : string

Quoted value or expression result which should be cast to integer type

$asIdentifier : string = ''

Optionally add a field identifier alias (AS)

Return values

string —

Returns the integer cast expression compatible with the connection database platform

castText()

Creates a cast for the `$expression` result to a text datatype depending on the database management system.

public castText(CompositeExpression|Stringable|string $expression[, string $asIdentifier = '' ]) : string

Note that for MySQL/MariaDB the corresponding CHAR/VARCHAR types are used with a length of 16383 reflecting 65554 bytes with utf8mb4 and working with default max_packet_size=16KB. For SQLite and PostgreSQL the text type conversion is used.

Main purpose of this expression is to use it in a expression chain to convert non-text values to text in chain with other expressions, for example to self::concat() multiple values or to ensure the type, within UNION/UNION ALL query parts for example in recursive Common Table Expressions parts.

This is a replacement for QueryBuilder::castFieldToTextType() with minor adjustments like enforcing and limiting the size to a fixed variant to be more usable in sensible areas like Common Table Expressions.

Alternatively the self::castVarchar() can be used which allows for dynamic length setting per expression call.

Example:

$queryBuilder->expr()->castText(
   '(' . '1 * 10' . ')',
   'virtual_field'
);

Result with MySQL:

CAST((1 * 10) AS CHAR(16383) AS `virtual_field`

Parameters

$expression : CompositeExpression|Stringable|string
$asIdentifier : string = ''

Tags

throws
RuntimeException

when used with a unsupported platform.

Return values

string

castVarchar()

Create a `CAST()` statement to cast value or expression to a varchar with a given dynamic max length.

public castVarchar(string $value[, int $length = 255 ][, string $asIdentifier = '' ]) : string

MySQL does not support VARCHAR as cast type, therefor CHAR is used.

Example:

$fieldVarcharCastExpression = $queryBuilder->expr()->castVarchar(
  $queryBuilder->quote('123'), // integer as string
  255,                         // convert to varchar(255) field - dynamic length
  'new_field_identifier',
);

Result with MySQL:

CAST("123" AS VARCHAR(255))

Parameters

$value : string

Unquoted value or expression, which should be cast

$length : int = 255

Dynamic varchar field length

$asIdentifier : string = ''

Used to add a field identifier alias (AS) if non-empty string (optional)

Return values

string —

Returns the cast expression compatible for the database platform

comparison()

Creates a comparison expression.

public comparison(mixed $leftExpression, string $operator, mixed $rightExpression) : string

Parameters

$leftExpression : mixed

The left expression.

$operator : string

One of the ExpressionBuilder::* constants.

$rightExpression : mixed

The right expression.

Tags

todo:

Add types to signature - either mixed, or (better) string like doctrine. Similar for other methods below. Especially have a look at $value below.

Return values

string

concat()

Concatenate multiple values or expressions into one string value.

public concat(string ...$parts) : string

No automatic quoting or value casting! Ensure each part evaluates to a valid varchar value!

Example:

// Combine value of two fields with a space
$concatExpressionAsString = $queryBuilder->expr()->concat(
  $queryBuilder->quoteIdentifier('first_name_field'),
  $queryBuilder->quote(' '),
  $queryBuilder->quoteIdentifier('last_name_field')
);

Result with MySQL:

CONCAT(`first_name_field`, " ", `last_name_field`)

Parameters

$parts : string

Unquoted value or expression part to concatenated with the other parts

Return values

string —

Returns the concatenation expression compatible with the database connection platform

count()

Creates a COUNT expression for the given field/alias.

public count(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

eq()

Creates an equality comparison expression with the given arguments.

public eq(string $fieldName, mixed $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

getContainer()

public getContainer() : ContainerInterface

Return values

ContainerInterface

gt()

Creates a greater-than comparison expression with the given arguments.

public gt(string $fieldName, mixed $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

gte()

Creates a greater-than-equal comparison expression with the given arguments.

public gte(string $fieldName, mixed $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

if()

Creates IF-THEN-ELSE expression construct compatible with all supported database vendors.

public if(CompositeExpression|CompositeExpression|Stringable|string $condition, Stringable|string $truePart, Stringable|string $falsePart[, Stringable|string|null $as = null ]) : string

No automatic quoting or escaping is done, which allows to build up nested expression statements.

Example:

$queryBuilder
  ->selectLiteral(
    $queryBuilder->expr()->if(
      $queryBuilder->expr()->eq('hidden', $queryBuilder->createNamedParameter(0, Connection::PARAM_INT)),
      $queryBuilder->quote('page-is-visible'),
      $queryBuilder->quote('page-is-not-visible'),
      'result_field_name'
    ),
  )
  ->from('pages');

Result with MySQL:

SELECT (IF(`hidden` = 0, 'page-is-visible', 'page-is-not-visible')) AS `result_field_name` FROM `pages`

Parameters

$condition : CompositeExpression|CompositeExpression|Stringable|string
$truePart : Stringable|string
$falsePart : Stringable|string
$as : Stringable|string|null = null

Return values

string

in()

Creates an IN () comparison expression with the given arguments.

public in(string $fieldName, string|array $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : string|array

The placeholder or the array of values to be used by IN() comparison. No automatic quoting/escaping is done.

Return values

string

inSet()

Returns a comparison that is true if the comma-separated list stored in $fieldName contains $value, e.g. inSet('fe_group', $queryBuilder->quote('2')) matches a record with fe_group = '1,2,5'.

public inSet(string $fieldName, string $value[, bool $isColumn = false ]) : string

Mind the argument order: the field holding the list (haystack) comes first, the value to look for (needle) second. This is the reverse of MySQL's FIND_IN_SET(needle, haystack). To check whether a field matches one of several given values, use in() instead.

Parameters

$fieldName : string

Field containing the comma-separated list (haystack). Quoted automatically.

$value : string

Single value to look for (needle), must not contain a comma. It is NOT quoted automatically: pass a quoted literal, e.g. from quote() or literal(), or a quoted column identifier together with $isColumn. Placeholders are not supported on all database platforms.

$isColumn : bool = false

Set if $value is a column identifier instead of a literal, to cast it to text

Tags

throws
InvalidArgumentException
throws
RuntimeException

Return values

string

isNotNull()

Creates an IS NOT NULL expression with the given arguments.

public isNotNull(string $fieldName) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

Return values

string

isNull()

Creates an IS NULL expression with the given arguments.

public isNull(string $fieldName) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

Return values

string

left()

Extract $length character of $value from the right side.

public left(int|string $length, string $value[, string $asIdentifier = '' ]) : string

$length can be an integer like value or a sub-expression evaluating to an integer value to define the length of the extracted length from the right side. This method does not quote anything! Ensure proper quoting (value/identifier) $length and $value!

Example:

$queryBuilder->expr()->left(
  $queryBuilder->castInt('(' . '23' . ')'),
  $queryBuilder->quoteIdentifier('table_field_name'),
  'virtual_field'
);

Result with MySQL:

LEFT(CAST(`table_field_name` AS INTEGER), CAST("23" AS INTEGER)) AS `virtual_field`

Parameters

$length : int|string

Integer value or expression providing the length as integer

$value : string

Value, identifier or expression defining the value to extract from the left

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Return the expression to extract defined substring from the right side.

leftPad()

Left-pad the value or sub-expression result with $paddingValue, to a total length of $length.

public leftPad(string $value, int|string $length, string $paddingValue[, string $asIdentifier = '' ]) : string

No automatic quoting or escaping is done, which allows the usage of a sub-expression for $value!

Example:

$queryBuilder->expr()->leftPad(
  $queryBuilder->quote('123'),
  10,
  '0',
  'padded_value'
);

Result with MySQL:

LPAD("123", CAST("10" AS INTEGER), "0") AS `padded_value`

Parameters

$value : string

Value, identifier or expression defining the value which should be left padded

$length : int|string

Padded length, to either fill up with $paddingValue on the left side or crop to

$paddingValue : string

Padding character used to fill up if characters are missing on the left side

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Returns database connection platform compatible left-pad expression.

length()

Creates a LENGTH expression for the given field/alias.

public length(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

like()

Creates a LIKE() comparison expression with the given arguments.

public like(string $fieldName, mixed $value[, string|null $escapeChar = null ]) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

Argument to be used in LIKE() comparison. No automatic quoting/escaping is done.

$escapeChar : string|null = null

Return values

string

literal()

Quotes a given input parameter.

public literal(string $input) : string

Parameters

$input : string

The parameter to be quoted.

Return values

string

lt()

Creates a lower-than comparison expression with the given arguments.

public lt(string $fieldName, mixed $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

lte()

Creates a lower-than-equal comparison expression with the given arguments.

public lte(string $fieldName, mixed $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

max()

Creates a MAX expression for the given field/alias.

public max(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

min()

Creates a MIN expression for the given field/alias.

public min(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

neq()

Creates a non equality comparison expression with the given arguments.

public neq(string $fieldName, mixed $value) : string

First argument is considered the left expression and the second is the right expression. When converted to string, it will generate a <> . Example::

[php]
// u.id <> 1
$q->where($q->expr()->neq('u.id', '1'));

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

The value. No automatic quoting/escaping is done.

Return values

string

notIn()

Creates a NOT IN () comparison expression with the given arguments.

public notIn(string $fieldName, string|array $value) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : string|array

The placeholder or the array of values to be used by NOT IN() comparison. No automatic quoting/escaping is done.

Return values

string

notInSet()

Returns a comparison that is true if the comma-separated list stored in $fieldName does not contain $value. This is the negation of inSet(), see there for details on the argument order.

public notInSet(string $fieldName, string $value[, bool $isColumn = false ]) : string

Parameters

$fieldName : string

Field containing the comma-separated list (haystack). Quoted automatically.

$value : string

Single value to look for (needle), must not contain a comma. It is NOT quoted automatically: pass a quoted literal, e.g. from quote() or literal(), or a quoted column identifier together with $isColumn. Placeholders are not supported on all database platforms.

$isColumn : bool = false

Set if $value is a column identifier instead of a literal, to cast it to text

Tags

throws
InvalidArgumentException
throws
RuntimeException

Return values

string

notLike()

Creates a NOT LIKE() comparison expression with the given arguments.

public notLike(string $fieldName, mixed $value[, string|null $escapeChar = null ]) : string

Parameters

$fieldName : string

The fieldname. Will be quoted according to database platform automatically.

$value : mixed

Argument to be used in NOT LIKE() comparison. No automatic quoting/escaping is done.

$escapeChar : string|null = null

Return values

string

repeat()

Create a statement to generate a value repeating defined $value for $numberOfRepeats times.

public repeat(int|string $numberOfRepeats, string $value[, string $asIdentifier = '' ]) : string

This method can be used to provide the repeat number as a sub-expression or calculation.

This method does not quote anything! Ensure proper quoting (value/identifier) for $numberOfRepeats and $value.

Example:

$queryBuilder->expr()->repeat(
  20,
  $queryBuilder->quote('0'),
  $queryBuilder->quoteIdentifier('aliased_field'),
);

Result with MySQL:

REPEAT("0", 20) AS `aliased_field`

Parameters

$numberOfRepeats : int|string

Statement or value defining how often the $value should be repeated. Proper quoting must be ensured.

$value : string

Value which should be repeated. Proper quoting must be ensured

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Returns the platform compatible statement to create the x-times repeated value

right()

Extract $length character of $value from the right side.

public right(int|string $length, string $value[, string $asIdentifier = '' ]) : string

$length can be an integer like value or a sub-expression evaluating to an integer value to define the length of the extracted length from the right side. This method does not quote anything! Ensure proper quoting (value/identifier) $length and $value!

Example:

$expression5 = $queryBuilder->expr()->right(
   6,
   $queryBuilder->quote('some-string'),
   'calculated_row_field',
 );

Result with MySQL:

RIGHT("some-string", CAST(6 AS INTEGER)) AS `calculated_row_field`

Parameters

$length : int|string

Integer value or expression providing the length as integer

$value : string

Value, identifier or expression defining the value to extract from the left

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Return the expression to extract defined substring from the right side

rightPad()

Right-pad the value or sub-expression result with $paddingValue, to a total length of $length.

public rightPad(string $value, int|string $length, string $paddingValue[, string $asIdentifier = '' ]) : string

No automatic quoting or escaping is done, which allows the usage of a sub-expression for $value!

Example:

$queryBuilder->expr()->rightPad(
  $queryBuilder->quote('123'),
  10,
  '0',
  'padded_value'
);

Result with MySQL:

RPAD("123", CAST("10" AS INTEGER), "0") AS `padded_value`

Parameters

$value : string

Value, identifier or expression defining the value which should be right padded

$length : int|string

Value, identifier or expression defining the padding length to fill up or crop

$paddingValue : string

Padding character used to fill up if characters are missing on the right side

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Returns database connection platform compatible right-pad expression

space()

Create statement containing $numberOfSpaces spaces.

public space(int|string $numberOfSpaces[, string $asIdentifier = '' ]) : string

This method does not quote anything! Ensure proper quoting (value/identifier) for $numberOfSpaces!

Example:

$queryBuilder->expr()->space(
  $queryBuilder->expr()->castInt(
    $queryBuilder->quoteIdentifier('table_repeat_number_field')
  ),
  $queryBuilder->quoteIdentifier('aliased_field'),
);

Result with MySQL:

SPACE(CAST(`table_repeat_number_field` AS INTEGER)) AS `aliased_field`

Parameters

$numberOfSpaces : int|string

Statement or value defining how often a space should be repeated. Proper quoting must be ensured.

$asIdentifier : string = ''

Provide AS identifier if not empty

Return values

string —

Returns the platform compatible statement to create the x-times repeated space(s).

sum()

Creates a SUM expression for the given field/alias.

public sum(string $fieldName[, string|null $alias = null ]) : string

Parameters

$fieldName : string
$alias : string|null = null

Return values

string

trim()

Creates a TRIM expression for the given field.

public trim(string $fieldName[, TrimMode $position = TrimMode::UNSPECIFIED ][, string|null $char = null ]) : string

Parameters

$fieldName : string

Field name to build expression for

$position : TrimMode = TrimMode::UNSPECIFIED

Either constant out of LEADING, TRAILING, BOTH

$char : string|null = null

Character to be trimmed (defaults to space)

Return values

string

calculation()

Create an SQL aggregate function.

protected calculation(string $aggregateName, string $fieldName[, string|null $alias = null ]) : string

Parameters

$aggregateName : string
$fieldName : string
$alias : string|null = null

Return values

string

unquoteLiteral()

Unquote a string literal. Used to unquote values for internal platform adjustments.

protected unquoteLiteral(string $value) : string

Parameters

$value : string

The value to be unquoted

Return values

string —

The unquoted value

On this page