Docs4dev
Docs
cakephp / 4.4 / class-cake.database.expression.casestatementexpression.html

Class CaseStatementExpression

Represents a SQL case statement with a fluid API

Namespace: Cake\Database\Expression

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
    arrayCake\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 last WHEN ... THEN ... statement that was opened using when().

  • 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 a WHEN ... THEN ... expression, or a self-contained expression that holds both the value for WHEN and the value for THEN.

Method Detail

__clone() public

__clone(): void

Clones the inner expression objects.

Returns

void

__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|arrayCake\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 a CASE case_value WHEN ... expression.
  • when: An array of WHEN ... THEN ... expressions.
  • else: The ELSE result value.

Parameters

string $clause

The name of the clause to obtain.

Returns

Cake\Database\ExpressionInterface|object|arrayCake\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

CaseStatementExpression::then()

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

\Cake\Database\TypeMap::setDefaults()

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\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.

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\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.

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

$elseType protected

The else part result type.

Type

string|null

$isSimpleVariant protected

Whether this is a simple case expression.

Type

bool

$returnType protected

The return type.

Type

string|null

$validClauseNames protected

The names of the clauses that are valid for use with the clause() method.

Type

array<string>

$value protected

The case value.

Type

Cake\Database\ExpressionInterface|object|scalar|null

$valueType protected

The case value type.

Type

string|null

$when protected

The WHEN ... THEN ... expressions.

Type

arrayCake\Database\Expression\WhenThenExpression>

$whenBuffer protected

Buffer that holds values and types for use with then().

Type

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