Class CaseStatementExpression
Represents a SQL case statement with a fluid API
Property Summary
-
$_typeMap public @property
Cake\Database\TypeMap
The type map to use when using an array of conditions for the
WHEN
value. -
$else protected
Cake\Database\ExpressionInterface|object|scalar|null
The else part result value.
-
$elseType protected
string|null
The else part result type.
-
$isSimpleVariant protected
bool
Whether this is a simple case expression.
-
$returnType protected
string|null
The return type.
-
$validClauseNames protected
array<string>
The names of the clauses that are valid for use with the
clause()
method. -
$value protected
Cake\Database\ExpressionInterface|object|scalar|null
The case value.
-
$valueType protected
string|null
The case value type.
-
$when protected
array<Cake\Database\Expression\WhenThenExpression>
The
WHEN ... THEN ...
expressions. -
$whenBuffer protected
array|null
Buffer that holds values and types for use with
then()
.
Method Summary
-
__clone() public
Clones the inner expression objects.
-
__construct() public
Constructor.
-
_castToExpression() protected
Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.
-
_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.
-
clause() public
Returns the available data for the given clause.
-
compileNullableValue() protected
Compiles a nullable value to SQL.
-
else() public
Sets the
ELSE
result value. -
getDefaultTypes() public
Gets default types of current type map.
-
getReturnType() public
Returns the abstract type that this expression will return.
-
getTypeMap() public
Returns the existing type map.
-
inferType() protected
Infers the abstract type for the given value.
-
setDefaultTypes() public
Overwrite the default type mappings for fields in the implementing object.
-
setReturnType() public
Sets the abstract type that this expression will return.
-
setTypeMap() public
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
-
sql() public
Converts the Node into a SQL string fragment.
-
then() public
Sets the
THEN
result value for the lastWHEN ... THEN ...
statement that was opened usingwhen()
. -
traverse() public
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.
-
when() public
Sets the
WHEN
value for aWHEN ... THEN ...
expression, or a self-contained expression that holds both the value forWHEN
and the value forTHEN
.
Method Detail
__construct() ¶ public
__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!
Parameters
-
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() ¶ protected
_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.
Parameters
-
mixed
$value The value to convert to ExpressionInterface
-
string|null
$type optional The type name
Returns
mixed
_requiresToExpressionCasting() ¶ protected
_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.
Parameters
-
array
$types List of type names
Returns
array
clause() ¶ public
clause(string $clause): Cake\Database\ExpressionInterface|object|array<Cake\Database\Expression\WhenThenExpression>|scalar|null
Returns the available data for the given clause.
Available clauses
The following clause names are available:
value
: The case value for aCASE case_value WHEN ...
expression.when
: An array ofWHEN ... THEN ...
expressions.else
: TheELSE
result value.
Parameters
-
string
$clause The name of the clause to obtain.
Returns
Cake\Database\ExpressionInterface|object|array<Cake\Database\Expression\WhenThenExpression>|scalar|null
Throws
InvalidArgumentException
In case the given clause name is invalid.
compileNullableValue() ¶ protected
compileNullableValue(Cake\Database\ValueBinder $binder, Cake\Database\ExpressionInterface|object|scalar|null $value, string|null $type = null): string
Compiles a nullable value to SQL.
Parameters
-
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.
Returns
string
else() ¶ public
else(Cake\Database\ExpressionInterface|object|scalar|null $result, string|null $type = null): $this
Sets the ELSE
result value.
Parameters
-
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.
Returns
$this
Throws
LogicException
In case a closing `then()` call is required before calling this method.
InvalidArgumentException
In case the `$result` argument is neither a scalar value, nor an object, an instance of `\Cake\Database\ExpressionInterface`, or `null`.
getDefaultTypes() ¶ public
getDefaultTypes(): array<int|string, string>
Gets default types of current type map.
Returns
array<int|string, string>
getReturnType() ¶ public
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
.
Returns
string
See Also
getTypeMap() ¶ public
getTypeMap(): Cake\Database\TypeMap
Returns the existing type map.
Returns
Cake\Database\TypeMap
inferType() ¶ protected
inferType(mixed $value): string|null
Infers the abstract type for the given value.
Parameters
-
mixed
$value The value for which to infer the type.
Returns
string|null
setDefaultTypes() ¶ public
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()
Parameters
-
array<int|string, string>
$types The array of types to set.
Returns
$this
See Also
setReturnType() ¶ public
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.
Parameters
-
string
$type The type name to use.
Returns
$this
setTypeMap() ¶ public
setTypeMap(Cake\Database\TypeMap|array $typeMap): $this
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
Parameters
-
Cake\Database\TypeMap|array
$typeMap Creates a TypeMap if array, otherwise sets the given TypeMap
Returns
$this
sql() ¶ public
sql(Cake\Database\ValueBinder $binder): string
Converts the Node into a SQL string fragment.
Parameters
-
Cake\Database\ValueBinder
$binder
Returns
string
then() ¶ public
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()
.
Order based syntax
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')
// ...
Parameters
-
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.
Returns
$this
Throws
LogicException
In case `when()` wasn't previously called with a value other than a closure or an instance of `\Cake\Database\Expression\WhenThenExpression`.
traverse() ¶ public
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.
Parameters
-
Closure
$callback
Returns
$this
when() ¶ public
when(Cake\Database\ExpressionInterface|Closure|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
.
Order based syntax
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');
Self-contained expressions
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');
Type handling
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');
User data safety
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')
Parameters
-
Cake\Database\ExpressionInterface|Closure|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.
Returns
$this
Throws
LogicException
In case this a closing `then()` call is required before calling this method.
LogicException
In case the callable doesn't return an instance of `\Cake\Database\Expression\WhenThenExpression`.
Property Detail
$_typeMap ¶ public @property
The type map to use when using an array of conditions for the WHEN
value.
Type
Cake\Database\TypeMap
$else ¶ protected
The else part result value.
Type
Cake\Database\ExpressionInterface|object|scalar|null
$validClauseNames ¶ protected
The names of the clauses that are valid for use with the
clause()
method.
Type
array<string>
$when ¶ protected
The WHEN ... THEN ...
expressions.
Type
array<Cake\Database\Expression\WhenThenExpression>