Represents a SQL case statement with a fluid API
Cake\Database\TypeMap
The type map to use when using an array of conditions for the WHEN
value.
Cake\Database\ExpressionInterface|object|scalar|null
The else part result value.
string|null
The else part result type.
bool
Whether this is a simple case expression.
string|null
The return type.
array<string>
The names of the clauses that are valid for use with the clause()
method.
Cake\Database\ExpressionInterface|object|scalar|null
The case value.
string|null
The case value type.
arrayCake\Database\Expression\WhenThenExpression>
The WHEN ... THEN ...
expressions.
array|null
Buffer that holds values and types for use with then()
.
Clones the inner expression objects.
Constructor.
Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.
Returns an array with the types that require values to be casted to expressions, out of the list of type names passed as parameter.
Returns the available data for the given clause.
Compiles a nullable value to SQL.
Sets the ELSE
result value.
Gets default types of current type map.
Returns the abstract type that this expression will return.
Returns the existing type map.
Infers the abstract type for the given value.
Overwrite the default type mappings for fields in the implementing object.
Sets the abstract type that this expression will return.
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
Converts the Node into a SQL string fragment.
Sets the THEN
result value for the last WHEN ... THEN ...
statement that was opened using when()
.
Iterates over each part of the expression recursively for every level of the expressions tree and executes the $callback callable passing as first parameter the instance of the expression currently being iterated.
Sets the WHEN
value for a WHEN ... THEN ...
expression, or a self-contained expression that holds both the value for WHEN
and the value for THEN
.
__clone(): void
Clones the inner expression objects.
void
__construct(Cake\Database\ExpressionInterface|object|scalar|null $value = null, string|null $type = null)
Constructor.
When a value is set, the syntax generated is CASE case_value WHEN when_value ... END
(simple case), where the when_value
's are compared against the case_value
.
When no value is set, the syntax generated is CASE WHEN when_conditions ... END
(searched case), where the conditions hold the comparisons.
Note that null
is a valid case value, and thus should only be passed if you actually want to create the simple case expression variant!
Cake\Database\ExpressionInterface|object|scalar|null
$value optional The case value.
string|null
$type optional The case value type. If no type is provided, the type will be tried to be inferred from the value.
_castToExpression(mixed $value, string|null $type = null): mixed
Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.
mixed
$value The value to convert to ExpressionInterface
string|null
$type optional The type name
mixed
_requiresToExpressionCasting(array $types): array
Returns an array with the types that require values to be casted to expressions, out of the list of type names passed as parameter.
array
$types List of type names
array
clause(string $clause): Cake\Database\ExpressionInterface|object|arrayCake\Database\Expression\WhenThenExpression>|scalar|null
Returns the available data for the given clause.
The following clause names are available:
value
: The case value for a CASE case_value WHEN ...
expression.when
: An array of WHEN ... THEN ...
expressions.else
: The ELSE
result value.string
$clause The name of the clause to obtain.
Cake\Database\ExpressionInterface|object|arrayCake\Database\Expression\WhenThenExpression>|scalar|null
InvalidArgumentException
compileNullableValue(Cake\Database\ValueBinder $binder, Cake\Database\ExpressionInterface|object|scalar|null $value, string|null $type = null): string
Compiles a nullable value to SQL.
Cake\Database\ValueBinder
$binder The value binder to use.
Cake\Database\ExpressionInterface|object|scalar|null
$value The value to compile.
string|null
$type optional The value type.
string
else(Cake\Database\ExpressionInterface|object|scalar|null $result, string|null $type = null): $this
Sets the ELSE
result value.
Cake\Database\ExpressionInterface|object|scalar|null
$result The result value.
string|null
$type optional The result type. If no type is provided, the type will be tried to be inferred from the value.
$this
LogicException
InvalidArgumentException
getDefaultTypes(): array<int|string, string>
Gets default types of current type map.
array<int|string, string>
getReturnType(): string
Returns the abstract type that this expression will return.
If no type has been explicitly set via setReturnType()
, this method will try to obtain the type from the result types of the then()
and else()
calls. All types must be identical in order for this to work, otherwise the type will default to string
.
string
getTypeMap(): Cake\Database\TypeMap
Returns the existing type map.
Cake\Database\TypeMap
inferType(mixed $value): string|null
Infers the abstract type for the given value.
mixed
$value The value for which to infer the type.
string|null
setDefaultTypes(array<int|string, string> $types): $this
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()
array<int|string, string>
$types The array of types to set.
$this
setReturnType(string $type): $this
Sets the abstract type that this expression will return.
If no type is being explicitly set via this method, then the getReturnType()
method will try to infer the type from the result types of the then()
and else()
calls.
string
$type The type name to use.
$this
setTypeMap(Cake\Database\TypeMap|array $typeMap): $this
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
Cake\Database\TypeMap|array
$typeMap Creates a TypeMap if array, otherwise sets the given TypeMap
$this
sql(Cake\Database\ValueBinder $binder): string
Converts the Node into a SQL string fragment.
Cake\Database\ValueBinder
$binder string
then(Cake\Database\ExpressionInterface|object|scalar|null $result, string|null $type = null): $this
Sets the THEN
result value for the last WHEN ... THEN ...
statement that was opened using when()
.
This method can only be invoked in case when()
was previously used with a value other than a closure or an instance of \Cake\Database\Expression\WhenThenExpression
:
$case ->when(['Table.column' => true]) ->then('Yes') ->when(['Table.column' => false]) ->then('No') ->else('Maybe');
The following would all fail with an exception:
$case ->when(['Table.column' => true]) ->when(['Table.column' => false]) // ...
$case ->when(['Table.column' => true]) ->else('Maybe') // ...
$case ->then('Yes') // ...
$case ->when(['Table.column' => true]) ->then('Yes') ->then('No') // ...
Cake\Database\ExpressionInterface|object|scalar|null
$result The result value.
string|null
$type optional The result type. If no type is provided, the type will be tried to be inferred from the value.
$this
LogicException
traverse(Closure $callback): $this
Iterates over each part of the expression recursively for every level of the expressions tree and executes the $callback callable passing as first parameter the instance of the expression currently being iterated.
Closure
$callback $this
when(Cake\Database\ExpressionInterfaceClosure|object|array|scalar $when, array<string, string>|string|null $type = null): $this
Sets the WHEN
value for a WHEN ... THEN ...
expression, or a self-contained expression that holds both the value for WHEN
and the value for THEN
.
When passing a value other than a self-contained \Cake\Database\Expression\WhenThenExpression
, instance, the WHEN ... THEN ...
statement must be closed off with a call to then()
before invoking when()
again or else()
:
$queryExpression ->case($query->identifier('Table.column')) ->when(true) ->then('Yes') ->when(false) ->then('No') ->else('Maybe');
When passing an instance of \Cake\Database\Expression\WhenThenExpression
, being it directly, or via a callable, then there is no need to close using then()
on this object, instead the statement will be closed on the \Cake\Database\Expression\WhenThenExpression
object using \Cake\Database\Expression\WhenThenExpression::then()
.
Callables will receive an instance of \Cake\Database\Expression\WhenThenExpression
, and must return one, being it the same object, or a custom one:
$queryExpression ->case() ->when(function (\Cake\Database\Expression\WhenThenExpression $whenThen) { return $whenThen ->when(['Table.column' => true]) ->then('Yes'); }) ->when(function (\Cake\Database\Expression\WhenThenExpression $whenThen) { return $whenThen ->when(['Table.column' => false]) ->then('No'); }) ->else('Maybe');
The types provided via the $type
argument will be merged with the type map set for this expression. When using callables for $when
, the \Cake\Database\Expression\WhenThenExpression
instance received by the callables will inherit that type map, however the types passed here will not be merged in case of using callables, instead the types must be passed in \Cake\Database\Expression\WhenThenExpression::when()
:
$queryExpression ->case() ->when(function (\Cake\Database\Expression\WhenThenExpression $whenThen) { return $whenThen ->when(['unmapped_column' => true], ['unmapped_column' => 'bool']) ->then('Yes'); }) ->when(function (\Cake\Database\Expression\WhenThenExpression $whenThen) { return $whenThen ->when(['unmapped_column' => false], ['unmapped_column' => 'bool']) ->then('No'); }) ->else('Maybe');
When passing user data, be aware that allowing a user defined array to be passed, is a potential SQL injection vulnerability, as it allows for raw SQL to slip in!
The following is unsafe usage that must be avoided:
$case ->when($userData)
A safe variant for the above would be to define a single type for the value:
$case ->when($userData, 'integer')
This way an exception would be triggered when an array is passed for the value, thus preventing raw SQL from slipping in, and all other types of values would be forced to be bound as an integer.
Another way to safely pass user data is when using a conditions array, and passing user data only on the value side of the array entries, which will cause them to be bound:
$case ->when([ 'Table.column' => $userData, ])
Lastly, data can also be bound manually:
$query ->select([ 'val' => $query->newExpr() ->case() ->when($query->newExpr(':userData')) ->then(123) ]) ->bind(':userData', $userData, 'integer')
Cake\Database\ExpressionInterfaceClosure|object|array|scalar
$when The WHEN
value. When using an array of conditions, it must be compatible with \Cake\Database\Query::where()
. Note that this argument is not completely safe for use with user data, as a user supplied array would allow for raw SQL to slip in! If you plan to use user data, either pass a single type for the $type
argument (which forces the $when
value to be a non-array, and then always binds the data), use a conditions array where the user data is only passed on the value side of the array entries, or custom bindings!
array<string, string>|string|null
$type optional The when value type. Either an associative array when using array style conditions, or else a string. If no type is provided, the type will be tried to be inferred from the value.
$this
LogicException
LogicException
The type map to use when using an array of conditions for the WHEN
value.
Cake\Database\TypeMap
The else part result value.
Cake\Database\ExpressionInterface|object|scalar|null
The else part result type.
string|null
Whether this is a simple case expression.
bool
The return type.
string|null
The names of the clauses that are valid for use with the clause()
method.
array<string>
The case value.
Cake\Database\ExpressionInterface|object|scalar|null
The case value type.
string|null
The WHEN ... THEN ...
expressions.
arrayCake\Database\Expression\WhenThenExpression>
Buffer that holds values and types for use with then()
.
array|null
© 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.4/class-Cake.Database.Expression.CaseStatementExpression.html