W3cubDocs

/CakePHP 4.1

Class AggregateExpression

This represents a SQL aggregate function expression in a SQL statement.

Calls can be constructed by passing the name of the function and a list of params. For security reasons, all params passed are quoted by default unless explicitly told otherwise.

Constants summary

  • string
    FOLLOWING
    'FOLLOWING'
  • string
    GROUPS
    'GROUPS'
  • string
    PRECEDING
    'PRECEDING'
  • string
    RANGE
    'RANGE'
  • string
    ROWS
    'ROWS'

Properties summary

  • $_conditions protected
    array

    A list of strings or other expression objects that represent the "branches" of the expression tree. For example one key of the array might look like "sum > :value"

  • $_conjunction protected
    string

    String to be used for joining each of the internal expressions this object internally stores for example "AND", "OR", etc.

  • $_name protected
    string

    The name of the function to be constructed when generating the SQL string

  • $_returnType protected
    string

    The type name this expression will return when executed

  • $_typeMap protected
    \Cake\Database\TypeMap
  • $filter protected
    \Cake\Database\Expression\QueryExpression
  • $window protected
    \Cake\Database\Expression\WindowExpression

Method Summary

  • __clone() public

    Clone this object and its subtree of expressions.

  • __construct() public

    Constructor. Takes a name for the function to be invoked and a list of params to be passed into the function. Optionally you can pass a list of types to be used for each bound param.

  • _addConditions() protected

    Auxiliary function used for decomposing a nested array of conditions and build a tree structure inside this object to represent the full SQL expression.

  • _calculateType() protected

    Returns the type name for the passed field if it was stored in the typeMap

  • _castToExpression() protected

    Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.

  • _parseCondition() protected

    Parses a string conditions by trying to extract the operator inside it if any and finally returning either an adequate QueryExpression object or a plain string representation of the condition. This function is responsible for generating the placeholders and replacing the values by them, while storing the value elsewhere for future binding.

  • _requiresToExpressionCasting() protected

    Returns an array with the types that require values to be casted to expressions, out of the list of type names passed as parameter.

  • add() public

    Adds one or more arguments for the function call.

  • addCase() public

    Adds a new case expression to the expression object

  • and() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

  • and_() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

  • between() public

    Adds a new condition to the expression object in the form "field BETWEEN from AND to".

  • count() public

    The name of the function is in itself an expression to generate, thus always adding 1 to the amount of expressions stored in this object.

  • eq() public

    Adds a new condition to the expression object in the form "field = value".

  • equalFields() public

    Builds equal condition or assignment with identifier wrapping.

  • excludeCurrent() public

    Adds current row frame exclusion.

  • excludeGroup() public

    Adds group frame exclusion.

  • excludeTies() public

    Adds ties frame exclusion.

  • exists() public

    Adds a new condition to the expression object in the form "EXISTS (...)".

  • filter() public

    Adds conditions to the FILTER clause. The conditions are the same format as Query::where().

  • frame() public

    Adds a frame to the window.

  • getConjunction() public

    Gets the currently configured conjunction for the conditions at this level of the expression tree.

  • getDefaultTypes() public

    Gets default types of current type map.

  • getName() public

    Gets the name of the SQL function to be invoke in this expression.

  • getReturnType() public

    Gets the type of the value this object will generate.

  • getTypeMap() public

    Returns the existing type map.

  • groups() public

    Adds a simple groups frame to the window.

  • gt() public

    Adds a new condition to the expression object in the form "field > value".

  • gte() public

    Adds a new condition to the expression object in the form "field >= value".

  • hasNestedExpression() public

    Returns true if this expression contains any other nested ExpressionInterface objects

  • in() public

    Adds a new condition to the expression object in the form "field IN (value1, value2)".

  • isCallable() public

    Check whether or not a callable is acceptable.

  • isNotNull() public

    Adds a new condition to the expression object in the form "field IS NOT NULL".

  • isNull() public

    Adds a new condition to the expression object in the form "field IS NULL".

  • iterateParts() public

    Executes a callable function for each of the parts that form this expression.

  • like() public

    Adds a new condition to the expression object in the form "field LIKE value".

  • lt() public

    Adds a new condition to the expression object in the form "field < value".

  • lte() public

    Adds a new condition to the expression object in the form "field <= value".

  • not() public

    Adds a new set of conditions to this level of the tree and negates the final result by prepending a NOT, it will look like "NOT ( (condition1) AND (conditions2) )" conjunction depends on the one currently configured for this object.

  • notEq() public

    Adds a new condition to the expression object in the form "field != value".

  • notExists() public

    Adds a new condition to the expression object in the form "NOT EXISTS (...)".

  • notIn() public

    Adds a new condition to the expression object in the form "field NOT IN (value1, value2)".

  • notLike() public

    Adds a new condition to the expression object in the form "field NOT LIKE value".

  • or() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

  • or_() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

  • order() public

    Adds one or more order clauses to the window.

  • over() public

    Adds an empty OVER() window expression or a named window epression.

  • partition() public

    Adds one or more partition expressions to the window.

  • range() public

    Adds a simple range frame to the window.

  • rows() public

    Adds a simple rows frame to the window.

  • setConjunction() public

    Changes the conjunction for the conditions at this level of the expression tree.

  • setDefaultTypes() public

    Overwrite the default type mappings for fields in the implementing object.

  • setName() public

    Sets the name of the SQL function to be invoke in this expression.

  • setReturnType() public

    Sets the type of the value this object will generate.

  • setTypeMap() public

    Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.

  • sql() public

    Returns the string representation of this object so that it can be used in a SQL query. Note that values condition values are not included in the string, in their place placeholders are put and can be replaced by the quoted values accordingly.

  • traverse() public

    Traverses the tree structure of this query expression by executing a callback function for each of the conditions that are included in this object.

Method Detail

__clone() public

__clone()

Clone this object and its subtree of expressions.

__construct() public

__construct(string $name, array $params, array $types, string $returnType)

Constructor. Takes a name for the function to be invoked and a list of params to be passed into the function. Optionally you can pass a list of types to be used for each bound param.

By default, all params that are passed will be quoted. If you wish to use literal arguments, you need to explicitly hint this function.

Examples:

$f = new FunctionExpression('CONCAT', ['CakePHP', ' rules']);

Previous line will generate CONCAT('CakePHP', ' rules')

$f = new FunctionExpression('CONCAT', ['name' => 'literal', ' rules']);

Will produce CONCAT(name, ' rules')

Parameters

string $name

the name of the function to be constructed

array $params optional

list of arguments to be passed to the function If associative the key would be used as argument when value is 'literal'

array $types optional

associative array of types to be associated with the passed arguments

string $returnType optional

The return type of this expression

_addConditions() protected

_addConditions(array $conditions, array $types)

Auxiliary function used for decomposing a nested array of conditions and build a tree structure inside this object to represent the full SQL expression.

String conditions are stored directly in the conditions, while any other representation is wrapped around an adequate instance or of this class.

Parameters

array $conditions

list of conditions to be stored in this object

array $types

list of types associated on fields referenced in $conditions

_calculateType() protected

_calculateType(mixed $field)

Returns the type name for the passed field if it was stored in the typeMap

Parameters

string|\Cake\Database\ExpressionInterface $field

The field name to get a type for.

Returns

string|null

The computed type or null, if the type is unknown.

_castToExpression() protected

_castToExpression(mixed $value, ?string $type)

Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.

Parameters

mixed $value

The value to converto to ExpressionInterface

string|null $type optional

The type name

Returns

mixed

_parseCondition() protected

_parseCondition(string $field, mixed $value)

Parses a string conditions by trying to extract the operator inside it if any and finally returning either an adequate QueryExpression object or a plain string representation of the condition. This function is responsible for generating the placeholders and replacing the values by them, while storing the value elsewhere for future binding.

Parameters

string $field

The value from which the actual field and operator will be extracted.

mixed $value

The value to be bound to a placeholder for the field

Returns

string|\Cake\Database\ExpressionInterface

Throws

InvalidArgumentException
If operator is invalid or missing on NULL usage.

_requiresToExpressionCasting() protected

_requiresToExpressionCasting(array $types)

Returns an array with the types that require values to be casted to expressions, out of the list of type names passed as parameter.

Parameters

array $types

List of type names

Returns

array

add() public

add(mixed $conditions, array $types, bool $prepend)

Adds one or more arguments for the function call.

Parameters

array $conditions

list of arguments to be passed to the function If associative the key would be used as argument when value is 'literal'

array $types optional

associative array of types to be associated with the passed arguments

bool $prepend optional

Whether to prepend or append to the list of arguments

Returns

$this

See Also

\Cake\Database\Expression\FunctionExpression::__construct()
for more details.

addCase() public

addCase(mixed $conditions, mixed $values, mixed $types)

Adds a new case expression to the expression object

Parameters

array|\Cake\Database\ExpressionInterface $conditions

The conditions to test. Must be a ExpressionInterface instance, or an array of ExpressionInterface instances.

array|\Cake\Database\ExpressionInterface $values optional

associative array of values to be associated with the conditions passed in $conditions. If there are more $values than $conditions, the last $value is used as the ELSE value.

array $types optional

associative array of types to be associated with the values passed in $values

Returns

$this

and() public

and(mixed $conditions, mixed $types)

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

Parameters

\Closure|string|array|\Cake\Database\ExpressionInterface $conditions

to be joined with AND

array $types optional

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

\Cake\Database\Expression\QueryExpression

and_() public

and_(mixed $conditions, mixed $types)

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

Parameters

\Closure|string|array|\Cake\Database\ExpressionInterface $conditions

to be joined with AND

array $types optional

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

\Cake\Database\Expression\QueryExpression

between() public

between(mixed $field, mixed $from, mixed $to, mixed $type)

Adds a new condition to the expression object in the form "field BETWEEN from AND to".

Parameters

string|\Cake\Database\ExpressionInterface $field

The field name to compare for values inbetween the range.

mixed $from

The initial value of the range.

mixed $to

The ending value in the comparison range.

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

count() public

count()

The name of the function is in itself an expression to generate, thus always adding 1 to the amount of expressions stored in this object.

Returns

int

eq() public

eq(mixed $field, mixed $value, ?string $type)

Adds a new condition to the expression object in the form "field = value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map. If it is suffixed with "[]" and the value is an array then multiple placeholders will be created, one per each value in the array.

Returns

$this

equalFields() public

equalFields(string $left, string $right)

Builds equal condition or assignment with identifier wrapping.

Parameters

string $left

Left join condition field name.

string $right

Right join condition field name.

Returns

$this

excludeCurrent() public

excludeCurrent()

Adds current row frame exclusion.

Returns

$this

excludeGroup() public

excludeGroup()

Adds group frame exclusion.

Returns

$this

excludeTies() public

excludeTies()

Adds ties frame exclusion.

Returns

$this

exists() public

exists(\Cake\Database\ExpressionInterface $query)

Adds a new condition to the expression object in the form "EXISTS (...)".

Parameters

\Cake\Database\ExpressionInterface $query

the inner query

Returns

$this

filter() public

filter(mixed $conditions, array $types)

Adds conditions to the FILTER clause. The conditions are the same format as Query::where().

Parameters

string|array|\Cake\Database\ExpressionInterface|\Closure $conditions

The conditions to filter on.

array $types optional

associative array of type names used to bind values to query

Returns

$this

See Also

\Cake\Database\Query::where()

frame() public

frame(string $type, mixed $startOffset, string $startDirection, mixed $endOffset, string $endDirection)

Adds a frame to the window.

Use the range(), rows() or groups() helpers if you need simple 'BETWEEN offset PRECEDING and offset FOLLOWING' frames.

You can specify any direction for both frame start and frame end.

With both $startOffset and $endOffset:

  • 0 - 'CURRENT ROW'
  • null - 'UNBOUNDED'

Parameters

string $type

Frame type

int|string|\Cake\Database\ExpressionInterface|null $startOffset

Frame start offset

string $startDirection

Frame start direction

int|string|\Cake\Database\ExpressionInterface|null $endOffset

Frame end offset

string $endDirection

Frame end direction

Returns

$this

Throws

InvalidArgumentException
WHen offsets are negative.

getConjunction() public

getConjunction()

Gets the currently configured conjunction for the conditions at this level of the expression tree.

Returns

string

getDefaultTypes() public

getDefaultTypes()

Gets default types of current type map.

Returns

array

getName() public

getName()

Gets the name of the SQL function to be invoke in this expression.

Returns

string

getReturnType() public

getReturnType()

Gets the type of the value this object will generate.

Returns

string

getTypeMap() public

getTypeMap()

Returns the existing type map.

Returns

\Cake\Database\TypeMap

groups() public

groups(?int $start, ?int $end)

Adds a simple groups frame to the window.

See range() for details.

Parameters

int|null $start

Frame start

int|null $end optional

Frame end If not passed in, only frame start SQL will be generated.

Returns

$this

gt() public

gt(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field > value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

gte() public

gte(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field >= value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

hasNestedExpression() public

hasNestedExpression()

Returns true if this expression contains any other nested ExpressionInterface objects

Returns

bool

in() public

in(mixed $field, mixed $values, mixed $type)

Adds a new condition to the expression object in the form "field IN (value1, value2)".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

string|array|\Cake\Database\ExpressionInterface $values

the value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

isCallable() public

isCallable(mixed $c)

Check whether or not a callable is acceptable.

We don't accept ['class', 'method'] style callbacks, as they often contain user input and arrays of strings are easy to sneak in.

Parameters

callable|string|array|\Cake\Database\ExpressionInterface $c

The callable to check.

Returns

bool

Valid callable.

isNotNull() public

isNotNull(mixed $field)

Adds a new condition to the expression object in the form "field IS NOT NULL".

Parameters

string|\Cake\Database\ExpressionInterface $field

database field to be tested for not null

Returns

$this

isNull() public

isNull(mixed $field)

Adds a new condition to the expression object in the form "field IS NULL".

Parameters

string|\Cake\Database\ExpressionInterface $field

database field to be tested for null

Returns

$this

iterateParts() public

iterateParts(callable $visitor)

Executes a callable function for each of the parts that form this expression.

The callable function is required to return a value with which the currently visited part will be replaced. If the callable function returns null then the part will be discarded completely from this expression.

The callback function will receive each of the conditions as first param and the key as second param. It is possible to declare the second parameter as passed by reference, this will enable you to change the key under which the modified part is stored.

Parameters

callable $visitor

The callable to apply to each part.

Returns

$this

like() public

like(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field LIKE value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

lt() public

lt(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field < value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

lte() public

lte(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field <= value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

not() public

not(mixed $conditions, mixed $types)

Adds a new set of conditions to this level of the tree and negates the final result by prepending a NOT, it will look like "NOT ( (condition1) AND (conditions2) )" conjunction depends on the one currently configured for this object.

Parameters

string|array|\Cake\Database\ExpressionInterface $conditions

to be added and negated

array $types optional

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

$this

notEq() public

notEq(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field != value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map. If it is suffixed with "[]" and the value is an array then multiple placeholders will be created, one per each value in the array.

Returns

$this

notExists() public

notExists(\Cake\Database\ExpressionInterface $query)

Adds a new condition to the expression object in the form "NOT EXISTS (...)".

Parameters

\Cake\Database\ExpressionInterface $query

the inner query

Returns

$this

notIn() public

notIn(mixed $field, mixed $values, mixed $type)

Adds a new condition to the expression object in the form "field NOT IN (value1, value2)".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

array|\Cake\Database\ExpressionInterface $values

the value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

notLike() public

notLike(mixed $field, mixed $value, mixed $type)

Adds a new condition to the expression object in the form "field NOT LIKE value".

Parameters

string|\Cake\Database\ExpressionInterface $field

Database field to be compared against value

mixed $value

The value to be bound to $field for comparison

string|null $type optional

the type name for $value as configured using the Type map.

Returns

$this

or() public

or(mixed $conditions, mixed $types)

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

Parameters

\Closure|string|array|\Cake\Database\ExpressionInterface $conditions

to be joined with OR

array $types optional

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

\Cake\Database\Expression\QueryExpression

or_() public

or_(mixed $conditions, mixed $types)

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

Parameters

\Closure|string|array|\Cake\Database\ExpressionInterface $conditions

to be joined with OR

array $types optional

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

\Cake\Database\Expression\QueryExpression

order() public

order(mixed $fields)

Adds one or more order clauses to the window.

Parameters

\Closure|(\Cake\Database\ExpressionInterface|string)[]|\Cake\Database\ExpressionInterface|string $fields

Order expressions

Returns

$this

over() public

over(?string $name)

Adds an empty OVER() window expression or a named window epression.

Parameters

string|null $name optional

Window name

Returns

$this

partition() public

partition(mixed $partitions)

Adds one or more partition expressions to the window.

Parameters

\Closure|(\Cake\Database\ExpressionInterface|string)[]|\Cake\Database\ExpressionInterface|string $partitions

Partition expressions

Returns

$this

range() public

range(mixed $start, mixed $end)

Adds a simple range frame to the window.

$start:

  • 0 - 'CURRENT ROW'
  • null - 'UNBOUNDED PRECEDING'
  • offset - 'offset PRECEDING'

$end:

  • 0 - 'CURRENT ROW'
  • null - 'UNBOUNDED FOLLOWING'
  • offset - 'offset FOLLOWING'

If you need to use 'FOLLOWING' with frame start or 'PRECEDING' with frame end, use frame() instead.

Parameters

int|string|\Cake\Database\ExpressionInterface|null $start

Frame start

int|string|\Cake\Database\ExpressionInterface|null $end optional

Frame end If not passed in, only frame start SQL will be generated.

Returns

$this

rows() public

rows(?int $start, ?int $end)

Adds a simple rows frame to the window.

See range() for details.

Parameters

int|null $start

Frame start

int|null $end optional

Frame end If not passed in, only frame start SQL will be generated.

Returns

$this

setConjunction() public

setConjunction(string $conjunction)

Changes the conjunction for the conditions at this level of the expression tree.

Parameters

string $conjunction

Value to be used for joining conditions

Returns

$this

setDefaultTypes() public

setDefaultTypes(array $types)

Overwrite the default type mappings for fields in the implementing object.

This method is useful if you need to set type mappings that are shared across multiple functions/expressions in a query.

To add a default without overwriting existing ones use getTypeMap()->addDefaults()

Parameters

array $types

The array of types to set.

Returns

$this

See Also

\Cake\Database\TypeMap::setDefaults()

setName() public

setName(string $name)

Sets the name of the SQL function to be invoke in this expression.

Parameters

string $name

The name of the function

Returns

$this

setReturnType() public

setReturnType(string $type)

Sets the type of the value this object will generate.

Parameters

string $type

The name of the type that is to be returned

Returns

$this

setTypeMap() public

setTypeMap(mixed $typeMap)

Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.

Parameters

array|\Cake\Database\TypeMap $typeMap

Creates a TypeMap if array, otherwise sets the given TypeMap

Returns

$this

sql() public

sql(\Cake\Database\ValueBinder $generator)

Returns the string representation of this object so that it can be used in a SQL query. Note that values condition values are not included in the string, in their place placeholders are put and can be replaced by the quoted values accordingly.

Parameters

\Cake\Database\ValueBinder $generator

Placeholder generator object

Returns

string

traverse() public

traverse(\Closure $visitor)

Traverses the tree structure of this query expression by executing a callback function for each of the conditions that are included in this object.

Useful for compiling the final expression, or doing introspection in the structure.

Callback function receives as only argument an instance of ExpressionInterface

Parameters

\Closure $visitor

The callable to apply to all sub-expressions.

Returns

$this

Property Detail

$_conditions protected

A list of strings or other expression objects that represent the "branches" of the expression tree. For example one key of the array might look like "sum > :value"

Type

array

$_conjunction protected

String to be used for joining each of the internal expressions this object internally stores for example "AND", "OR", etc.

Type

string

$_name protected

The name of the function to be constructed when generating the SQL string

Type

string

$_returnType protected

The type name this expression will return when executed

Type

string

$_typeMap protected

Type

\Cake\Database\TypeMap

$filter protected

Type

\Cake\Database\Expression\QueryExpression

$window protected

Type

\Cake\Database\Expression\WindowExpression

© 2005–present The Cake Software Foundation, Inc.
Licensed under the MIT License.
CakePHP is a registered trademark of Cake Software Foundation, Inc.
We are not endorsed by or affiliated with CakePHP.
https://api.cakephp.org/4.1/class-Cake.Database.Expression.AggregateExpression.html