Fr3nch13/CakePHP Utilities

CaseStatementExpression
in package
implements ExpressionInterface, TypedResultInterface uses CaseExpressionTrait, ExpressionTypeCasterTrait, TypeMapTrait

Represents a SQL case statement with a fluid API

Table of Contents

Interfaces

ExpressionInterface
An interface used by Expression objects.
TypedResultInterface
Represents an expression that is known to return a specific type

Properties

$_typeMap  : TypeMap|null
$else  : ExpressionInterface|object|scalar|null
The else part result value.
$elseType  : string|null
The else part result type.
$isSimpleVariant  : bool
Whether this is a simple case expression.
$returnType  : string|null
The return type.
$validClauseNames  : array<string|int, string>
The names of the clauses that are valid for use with the `clause()` method.
$value  : ExpressionInterface|object|scalar|null
The case value.
$valueType  : string|null
The case value type.
$when  : array<string|int, WhenThenExpression>
The `WHEN ... THEN ...` expressions.
$whenBuffer  : array<string|int, mixed>|null
Buffer that holds values and types for use with `then()`.

Methods

__clone()  : void
Clones the inner expression objects.
__construct()  : mixed
Constructor.
clause()  : ExpressionInterface|object|array<string|int, WhenThenExpression>|scalar|null
Returns the available data for the given clause.
else()  : $this
Sets the `ELSE` result value.
getDefaultTypes()  : array<int|string, string>
Gets default types of current type map.
getReturnType()  : string
Returns the abstract type that this expression will return.
getTypeMap()  : TypeMap
Returns the existing type map.
setDefaultTypes()  : $this
Overwrite the default type mappings for fields in the implementing object.
setReturnType()  : $this
Sets the abstract type that this expression will return.
setTypeMap()  : $this
Creates a new TypeMap if $typeMap is an array, otherwise exchanges it for the given one.
sql()  : string
Converts the Node into a SQL string fragment.
then()  : $this
Sets the `THEN` result value for the last `WHEN ... THEN ...` statement that was opened using `when()`.
traverse()  : $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.
when()  : $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`.
_castToExpression()  : mixed
Conditionally converts the passed value to an ExpressionInterface object if the type class implements the ExpressionTypeInterface. Otherwise, returns the value unmodified.
_requiresToExpressionCasting()  : array<string|int, mixed>
Returns an array with the types that require values to be casted to expressions, out of the list of type names passed as parameter.

Properties

$validClauseNames

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

protected array<string|int, string> $validClauseNames = ['value', 'when', 'else']

$whenBuffer

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

protected array<string|int, mixed>|null $whenBuffer = null

Methods

__construct()

Constructor.

public __construct([ExpressionInterface|object|scalar|null $value = null ][, string|null $type = null ]) : mixed

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
$value : ExpressionInterface|object|scalar|null = null

The case value.

$type : string|null = null

The case value type. If no type is provided, the type will be tried to be inferred from the value.

clause()

Returns the available data for the given clause.

public clause(string $clause) : ExpressionInterface|object|array<string|int, WhenThenExpression>|scalar|null

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
$clause : string

The name of the clause to obtain.

Tags
throws
InvalidArgumentException

In case the given clause name is invalid.

Return values
ExpressionInterface|object|array<string|int, WhenThenExpression>|scalar|null

else()

Sets the `ELSE` result value.

public else(ExpressionInterface|object|scalar|null $result[, string|null $type = null ]) : $this
Parameters
$result : ExpressionInterface|object|scalar|null

The result value.

$type : string|null = null

The result type. If no type is provided, the type will be tried to be inferred from the value.

Tags
throws
LogicException

In case a closing then() call is required before calling this method.

throws
InvalidArgumentException

In case the $result argument is neither a scalar value, nor an object, an instance of \Cake\Database\ExpressionInterface, or null.

Return values
$this

getDefaultTypes()

Gets default types of current type map.

public getDefaultTypes() : array<int|string, string>
Return values
array<int|string, string>

getReturnType()

Returns the abstract type that this expression will return.

public getReturnType() : string

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.

Tags
see
CaseStatementExpression::then()
Return values
string

setDefaultTypes()

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

public setDefaultTypes(array<int|string, string> $types) : $this

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
$types : array<int|string, string>

The array of types to set.

Tags
see
TypeMap::setDefaults()
Return values
$this

setReturnType()

Sets the abstract type that this expression will return.

public setReturnType(string $type) : $this

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
$type : string

The type name to use.

Return values
$this

setTypeMap()

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

public setTypeMap(TypeMap|array<string|int, mixed> $typeMap) : $this
Parameters
$typeMap : TypeMap|array<string|int, mixed>

Creates a TypeMap if array, otherwise sets the given TypeMap

Return values
$this

sql()

Converts the Node into a SQL string fragment.

public sql(ValueBinder $binder) : string
Parameters
$binder : ValueBinder

Parameter binder

Tags
inheritDoc
Return values
string

then()

Sets the `THEN` result value for the last `WHEN ... THEN ...` statement that was opened using `when()`.

public then(ExpressionInterface|object|scalar|null $result[, string|null $type = null ]) : $this

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
$result : ExpressionInterface|object|scalar|null

The result value.

$type : string|null = null

The result type. If no type is provided, the type will be tried to be inferred from the value.

Tags
throws
LogicException

In case when() wasn't previously called with a value other than a closure or an instance of \Cake\Database\Expression\WhenThenExpression.

Return values
$this

traverse()

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.

public traverse(Closure $callback) : $this
Parameters
$callback : Closure

The callable to apply to all nodes.

Tags
inheritDoc
Return values
$this

when()

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

public when(ExpressionInterface|Closure|object|array<string|int, mixed>|scalar $when[, array<string, string>|string|null $type = null ]) : $this

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
$when : ExpressionInterface|Closure|object|array<string|int, mixed>|scalar

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!

$type : array<string, string>|string|null = null

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.

Tags
throws
LogicException

In case this a closing then() call is required before calling this method.

throws
LogicException

In case the callable doesn't return an instance of \Cake\Database\Expression\WhenThenExpression.

Return values
$this

_castToExpression()

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

protected _castToExpression(mixed $value[, string|null $type = null ]) : mixed
Parameters
$value : mixed

The value to convert to ExpressionInterface

$type : string|null = null

The type name

_requiresToExpressionCasting()

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

protected _requiresToExpressionCasting(array<string|int, mixed> $types) : array<string|int, mixed>
Parameters
$types : array<string|int, mixed>

List of type names

Return values
array<string|int, mixed> 

        

        
    




    

    

                                

                

                                
    

        On this page

        
    


                

                            

            

    

        

            
Search results