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

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.

Properties

$connection read-only

protected Connection $connection

Methods

__construct()

public __construct(Connection $connection) : mixed
Parameters
$connection : Connection

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

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

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

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

Search results