TYPO3 11.5 API: ExpressionBuilder

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.

Constants

EQ = '='
GT = '>'
GTE = '>='
LT = '<'
LTE = '<='
NEQ = '<>'
QUOTE_IDENTIFIER = 1
QUOTE_NOTHING = 0
QUOTE_PARAMETER = 2

Properties

$connection : Connection: The DBAL Connection.

Methods

__construct() : mixed: Initializes a new ExpressionBuilder
and() : CompositeExpression: Creates a conjunction of the given boolean expressions
andX() : CompositeExpression: Creates a conjunction of the given boolean expressions
avg() : string: Creates an AVG expression for the given field/alias.
bitAnd() : string: Creates a bitwise AND expression with the given arguments.
comparison() : string: Creates a comparison expression.
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.
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.
length() : string: Creates a LENGTH expression for the given field/alias.
like() : string: Creates a LIKE() comparison expression with the given arguments.
literal() : mixed: 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.
orX() : CompositeExpression: Creates a disjunction of the given boolean expressions.
sum() : string: Creates a SUM expression for the given field/alias.
trim() : string: Creates a TRIM expression for the given field.
calculation() : string: Create a SQL aggregate function.
unquoteLiteral() : string: Unquote a string literal. Used to unquote values for internal platform adjustments.

EQ


    public
        mixed
    EQ
    = '='

GT


    public
        mixed
    GT
    = '>'

GTE


    public
        mixed
    GTE
    = '>='

LT


    public
        mixed
    LT
    = '<'

LTE


    public
        mixed
    LTE
    = '<='

NEQ


    public
        mixed
    NEQ
    = '<>'

QUOTE_IDENTIFIER


    public
        mixed
    QUOTE_IDENTIFIER
    = 1

QUOTE_NOTHING


    public
        mixed
    QUOTE_NOTHING
    = 0

QUOTE_PARAMETER


    public
        mixed
    QUOTE_PARAMETER
    = 2

$connection

The DBAL Connection.


    protected
        Connection
    $connection

__construct()

Initializes a new ExpressionBuilder


    public
                    __construct(Connection $connection) : mixed

Parameters

$connection : Connection

and()

Creates a conjunction of the given boolean expressions


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

Parameters

$expressions : CompositeExpression|string|null: Optional clause. Requires at least one defined when converting to string.

Return values

CompositeExpression

andX()

Creates a conjunction of the given boolean expressions


    public
                    andX(string ...$expressions) : CompositeExpression

Parameters

$expressions : string: Optional clause. Requires at least one defined when converting to string.

Return values

CompositeExpression

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

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

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

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

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

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.

Return values

string

literal()

Quotes a given input parameter.


    public
                    literal(mixed $input[, string|Connection::PARAM_*|null $type = Connection::PARAM_STR ]) : mixed

Parameters

$input : mixed: The parameter to be quoted.
$type : string|Connection::PARAM_*|null = Connection::PARAM_STR: The type of the parameter.

Return values

mixed —

Often string, but also int or float or similar depending on $input and platform

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

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.

Return values

string

or()

Creates a disjunction of the given boolean expressions.


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

Parameters

$expressions : CompositeExpression|string|null: Optional clause. Requires at least one defined when converting to string.

Return values

CompositeExpression

orX()

Creates a disjunction of the given boolean expressions.


    public
                    orX(string ...$expressions) : CompositeExpression

Parameters

$expressions : string: Optional clause. Requires at least one defined when converting to string.

Return values

CompositeExpression

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[, int $position = TrimMode::UNSPECIFIED ][, string|null $char = null ]) : string

Parameters

$fieldName : string: Field name to build expression for
$position : int = 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 a 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

Table of Contents

Constants

Properties

Methods

Constants

EQ

GT

GTE

LT

LTE

NEQ

QUOTE_IDENTIFIER

QUOTE_NOTHING

QUOTE_PARAMETER

Properties

$connection

Methods

__construct()

Parameters

and()

Parameters

Return values

andX()

Parameters

Tags

Return values

avg()

Parameters

Return values

bitAnd()

Parameters

Return values

comparison()

Parameters

Return values

count()

Parameters

Return values

eq()

Parameters

Return values

gt()

Parameters

Return values

gte()

Parameters

Return values

in()

Parameters

Return values

inSet()

Parameters

Tags

Return values

isNotNull()

Parameters

Return values

isNull()

Parameters

Return values

length()

Parameters

Return values

like()

Parameters

Return values

literal()

Parameters

Tags

Return values

lt()

Parameters

Return values

lte()

Parameters

Return values

max()

Parameters

Return values