TYPO3 main API: ExpressionBuilder

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.

Properties

$connection : Connection

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.
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 can find a value in a list field (CSV).
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 can find a value in a list field (CSV) but is negated.
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.

$connection read-only


    protected
        Connection
    $connection

__construct()


    public
                    __construct(Connection $connection) : mixed

Parameters

$connection : Connection

and()

Creates a conjunction of the given boolean expressions


    public
                    and(CompositeExpression|CompositeExpression|string|null ...$expressions) : CompositeExpression

Parameters

$expressions : CompositeExpression|CompositeExpression|string|null

Return values

CompositeExpression

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

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.

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

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<string|int, mixed> $value) : string

Parameters

$fieldName : string: The fieldname. Will be quoted according to database platform automatically.
$value : string|array<string|int, mixed>: 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 can find a value in a list field (CSV).


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

Parameters

$fieldName : string: The field name. Will be quoted according to database platform automatically.
$value : string: Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
$isColumn : bool = false: Set when the value to compare is a column on a table to activate casting

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<string|int, mixed> $value) : string

Parameters

$fieldName : string: The fieldname. Will be quoted according to database platform automatically.
$value : string|array<string|int, mixed>: 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 can find a value in a list field (CSV) but is negated.


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

Parameters

$fieldName : string: The field name. Will be quoted according to database platform automatically.
$value : string: Argument to be used in FIND_IN_SET() comparison. No automatic quoting/escaping is done.
$isColumn : bool = false: Set when the value to compare is a column on a table to activate casting

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

or()

Creates a disjunction of the given boolean expressions.


    public
                    or(CompositeExpression|CompositeExpression|string|null ...$expressions) : CompositeExpression

Parameters

$expressions : CompositeExpression|CompositeExpression|string|null

Return values

CompositeExpression

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

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

ExpressionBuilder extends ExpressionBuilder

Table of Contents

Properties

Methods

Properties

$connection read-only

Methods

__construct()

Parameters

and()

Parameters

Return values

as()

Parameters

Return values

avg()

Parameters

Return values

bitAnd()

Parameters

Return values

castInt()

Parameters

Return values

castText()

Parameters

Tags

Return values

castVarchar()

Parameters

Return values

comparison()

Parameters

Tags

Return values

concat()

Parameters

Return values

count()

Parameters

Return values

eq()

Parameters

Return values

gt()

Parameters

Return values

gte()

Parameters

Return values

if()

Parameters

Return values

in()

Parameters

Return values

inSet()

Parameters

Tags

Return values

isNotNull()

Parameters

Return values

isNull()

Parameters

Return values

left()

Parameters

Return values

leftPad()

Parameters

Return values

length()

Parameters

Return values

like()

Parameters

Return values

literal()

Parameters