Class FunctionsBuilder
Contains methods related to generating FunctionExpression objects with most commonly used SQL functions.
This acts as a factory for FunctionExpression objects.
Supplying user-controlled data to parameters with the ExpressionInterface|string
type is unsafe. These parameters are included in the final query without escaping.
Method Summary
-
__call() public
Magic method dispatcher to create custom SQL function calls
-
aggregate() public
Helper method to create arbitrary SQL aggregate function calls.
-
avg() public
Returns a AggregateExpression representing a call to SQL AVG function.
-
cast() public
Returns a FunctionExpression representing a SQL CAST.
-
coalesce() public
Returns a FunctionExpression representing a call to SQL COALESCE function.
-
concat() public
Returns a FunctionExpression representing a string concatenation
-
count() public
Returns a AggregateExpression representing a call to SQL COUNT function.
-
dateAdd() public
Add the time unit to the date expression
-
dateDiff() public
Returns a FunctionExpression representing the difference in days between two dates.
-
datePart() public
Returns the specified date part from the SQL expression.
-
dayOfWeek() public
Returns a FunctionExpression representing a call to SQL WEEKDAY function. 1 - Sunday, 2 - Monday, 3 - Tuesday...
-
ensureSimpleString() protected
Ensures that string values are simple ascii values with no whitespace
-
extract() public
Returns the specified date part from the SQL expression.
-
jsonValue() public
Returns a FunctionExpression representing the Json Value
-
lag() public
Returns an AggregateExpression representing call to SQL LAG().
-
lead() public
Returns an AggregateExpression representing call to SQL LEAD().
-
max() public
Returns a AggregateExpression representing a call to SQL MAX function.
-
min() public
Returns a AggregateExpression representing a call to SQL MIN function.
-
now() public
Returns a FunctionExpression representing a call that will return the current date and time. By default it returns both date and time, but you can also make it generate only the date or only the time.
-
rand() public
Returns a FunctionExpression representing a call to SQL RAND function.
-
rowNumber() public
Returns an AggregateExpression representing call to SQL ROW_NUMBER().
-
stringAgg() public
Returns an AggregateExpression representing a portable string aggregation call.
-
sum() public
Returns a AggregateExpression representing a call to SQL SUM function.
-
toLiteralParam() protected
Creates function parameter array from expression or string literal.
-
weekday() public
Returns a FunctionExpression representing a call to SQL WEEKDAY function. 1 - Sunday, 2 - Monday, 3 - Tuesday...
Method Detail
__call() ¶ public
__call(string $name, array $args): Cake\Database\Expression\FunctionExpressionMagic method dispatcher to create custom SQL function calls
Parameters
-
string$name the SQL function name to construct
-
array$args list with up to 3 arguments, first one being an array with parameters for the SQL function, the second one a list of types to bind to those params, and the third one the return type of the function
Returns
Cake\Database\Expression\FunctionExpressionaggregate() ¶ public
aggregate(string $name, array $params = [], array $types = [], string $return = 'float'): Cake\Database\Expression\AggregateExpressionHelper method to create arbitrary SQL aggregate function calls.
Parameters
-
string$name The SQL aggregate function name
-
array$params optional Array of arguments to be passed to the function. Can be an associative array with the literal value or identifier:
['value' => 'literal']or['value' => 'identifier']-
array$types optional Array of types that match the names used in
$params:['name' => 'type']-
string$return optional Return type of the entire expression. Defaults to float.
Returns
Cake\Database\Expression\AggregateExpressionavg() ¶ public
avg(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\AggregateExpressionReturns a AggregateExpression representing a call to SQL AVG function.
Parameters
-
Cake\Database\ExpressionInterface|string$expression the expression for the avg() function.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\AggregateExpressioncast() ¶ public
cast(Cake\Database\ExpressionInterface|string $field, string $dataType): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a SQL CAST.
The $type parameter is a SQL type. The return type for the returned expression
is the default type name. Use setReturnType() to update it.
Parameters
-
Cake\Database\ExpressionInterface|string$field Field or expression to cast.
-
string$dataType The SQL data type. Must be a simple alphanumeric string.
Returns
Cake\Database\Expression\FunctionExpressioncoalesce() ¶ public
coalesce(array $args, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a call to SQL COALESCE function.
Parameters
-
array$args List of expressions to evaluate as function parameters
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressionconcat() ¶ public
concat(array $args, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a string concatenation
Parameters
-
array$args List of strings or expressions to concatenate
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressioncount() ¶ public
count(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\AggregateExpressionReturns a AggregateExpression representing a call to SQL COUNT function.
Parameters
-
Cake\Database\ExpressionInterface|string$expression the expression for the count() function.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\AggregateExpressiondateAdd() ¶ public
dateAdd(Cake\Database\ExpressionInterface|string $expression, string|int $value, string $unit, array $types = []): Cake\Database\Expression\FunctionExpressionAdd the time unit to the date expression
Parameters
-
Cake\Database\ExpressionInterface|string$expression Expression to obtain the date part from.
-
string|int$value Value to be added. Use negative to subtract.
-
string$unit Unit of the value e.g. hour or day. Must be a simple alphanumeric string.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressiondateDiff() ¶ public
dateDiff(array $args, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing the difference in days between two dates.
Parameters
-
array$args List of expressions to obtain the difference in days.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressiondatePart() ¶ public
datePart(string $part, Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\FunctionExpressionReturns the specified date part from the SQL expression.
Parameters
-
string$part Part of the date to return. Must be a simple alphanumeric string.
-
Cake\Database\ExpressionInterface|string$expression Expression to obtain the date part from.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressiondayOfWeek() ¶ public
dayOfWeek(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a call to SQL WEEKDAY function. 1 - Sunday, 2 - Monday, 3 - Tuesday...
Parameters
-
Cake\Database\ExpressionInterface|string$expression the function argument
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressionensureSimpleString() ¶ protected
ensureSimpleString(string $parameterName, string $value): voidEnsures that string values are simple ascii values with no whitespace
Parameters
-
string$parameterName The name of the parameter being checked.
-
string$value The value to check
Returns
voidextract() ¶ public
extract(string $part, Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\FunctionExpressionReturns the specified date part from the SQL expression.
Parameters
-
string$part Part of the date to return. Must be a simple alphanumeric string.
-
Cake\Database\ExpressionInterface|string$expression Expression to obtain the date part from.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressionjsonValue() ¶ public
jsonValue(Cake\Database\ExpressionInterface|string $expression, string $jsonPath, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing the Json Value
Parameters
-
Cake\Database\ExpressionInterface|string$expression The Json value or json field
-
string$jsonPath A valid JSON PATH Query
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpressionlag() ¶ public
lag(Cake\Database\ExpressionInterface|string $expression, int $offset, mixed $default = null, string|null $type = null): Cake\Database\Expression\AggregateExpressionReturns an AggregateExpression representing call to SQL LAG().
Parameters
-
Cake\Database\ExpressionInterface|string$expression The value evaluated at offset
-
int$offset The row offset
-
mixed$default optional The default value if offset doesn't exist
-
string|null$type optional The output type of the lag expression. Defaults to float.
Returns
Cake\Database\Expression\AggregateExpressionlead() ¶ public
lead(Cake\Database\ExpressionInterface|string $expression, int $offset, mixed $default = null, string|null $type = null): Cake\Database\Expression\AggregateExpressionReturns an AggregateExpression representing call to SQL LEAD().
Parameters
-
Cake\Database\ExpressionInterface|string$expression The value evaluated at offset
-
int$offset The row offset
-
mixed$default optional The default value if offset doesn't exist
-
string|null$type optional The output type of the lead expression. Defaults to float.
Returns
Cake\Database\Expression\AggregateExpressionmax() ¶ public
max(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\AggregateExpressionReturns a AggregateExpression representing a call to SQL MAX function.
Parameters
-
Cake\Database\ExpressionInterface|string$expression the expression for the max() function
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\AggregateExpressionmin() ¶ public
min(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\AggregateExpressionReturns a AggregateExpression representing a call to SQL MIN function.
Parameters
-
Cake\Database\ExpressionInterface|string$expression the expression for the min() function.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\AggregateExpressionnow() ¶ public
now(string $type = 'datetime'): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a call that will return the current date and time. By default it returns both date and time, but you can also make it generate only the date or only the time.
Parameters
-
string$type optional (datetime|date|time)
Returns
Cake\Database\Expression\FunctionExpressionrand() ¶ public
rand(): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a call to SQL RAND function.
Returns
Cake\Database\Expression\FunctionExpressionrowNumber() ¶ public
rowNumber(): Cake\Database\Expression\AggregateExpressionReturns an AggregateExpression representing call to SQL ROW_NUMBER().
Returns
Cake\Database\Expression\AggregateExpressionstringAgg() ¶ public
stringAgg(Cake\Database\ExpressionInterface|string $expression, string $separator, Cake\Database\ExpressionInterface|array|string|null $orderBy = null, array $types = []): Cake\Database\Expression\StringAggExpressionReturns an AggregateExpression representing a portable string aggregation call.
Parameters
-
Cake\Database\ExpressionInterface|string$expression The value to aggregate.
-
string$separator The separator inserted between values.
-
Cake\Database\ExpressionInterface|array|string|null$orderBy optional Aggregate-local ordering.
-
array$types optional List of types to bind to the arguments.
Returns
Cake\Database\Expression\StringAggExpressionsum() ¶ public
sum(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\AggregateExpressionReturns a AggregateExpression representing a call to SQL SUM function.
Parameters
-
Cake\Database\ExpressionInterface|string$expression the expression for the sum() function.
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\AggregateExpressiontoLiteralParam() ¶ protected
toLiteralParam(Cake\Database\ExpressionInterface|string $expression): array<Cake\Database\ExpressionInterface|string>Creates function parameter array from expression or string literal.
Parameters
-
Cake\Database\ExpressionInterface|string$expression function argument
Returns
array<Cake\Database\ExpressionInterface|string>weekday() ¶ public
weekday(Cake\Database\ExpressionInterface|string $expression, array $types = []): Cake\Database\Expression\FunctionExpressionReturns a FunctionExpression representing a call to SQL WEEKDAY function. 1 - Sunday, 2 - Monday, 3 - Tuesday...
Parameters
-
Cake\Database\ExpressionInterface|string$expression the function argument
-
array$types optional list of types to bind to the arguments
Returns
Cake\Database\Expression\FunctionExpression