W3cubDocs

/CakePHP 3.4

Class QueryExpression

Represents a SQL Query expression. Internally it stores a tree of expressions that can be compiled by converting this object to string and will contain a correctly parenthesized and nested expression.

Cake\Database\Expression\QueryExpression implements Cake\Database\ExpressionInterface, Countable uses Cake\Database\TypeMapTrait

Properties summary

  • $_conditions protected
    array

    A list of strings or other expression objects that represent the "branches" of the expression tree. For example one key of the array might look like "sum > :value"

  • $_conjunction protected
    string

    String to be used for joining each of the internal expressions this object internally stores for example "AND", "OR", etc.

Inherited Properties

Method Summary

  • __call() public
    Helps calling the and() and or() methods transparently.
  • __clone() public
    Clone this object and its subtree of expressions.
  • __construct() public

    Constructor. A new expression object can be created without any params and be built dynamically. Otherwise it is possible to pass an array of conditions containing either a tree-like array structure to be parsed and/or other expression objects. Optionally, you can set the conjunction keyword to be used for joining each part of this level of the expression tree.

  • _addConditions() protected

    Auxiliary function used for decomposing a nested array of conditions and build a tree structure inside this object to represent the full SQL expression. String conditions are stored directly in the conditions, while any other representation is wrapped around an adequate instance or of this class.

  • _calculateType() protected
    Returns the type name for the passed field if it was stored in the typeMap
  • _parseCondition() protected

    Parses a string conditions by trying to extract the operator inside it if any and finally returning either an adequate QueryExpression object or a plain string representation of the condition. This function is responsible for generating the placeholders and replacing the values by them, while storing the value elsewhere for future binding.

  • add() public

    Adds one or more conditions to this expression object. Conditions can be expressed in a one dimensional array, that will cause all conditions to be added directly at this level of the tree or they can be nested arbitrarily making it create more expression objects that will be nested inside and configured to use the specified conjunction.

  • addCase() public
    Adds a new case expression to the expression object
  • and_() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

  • between() public

    Adds a new condition to the expression object in the form "field BETWEEN from AND to".

  • count() public

    Returns the number of internal conditions that are stored in this expression. Useful to determine if this expression object is void or it will generate a non-empty string when compiled

  • eq() public
    Adds a new condition to the expression object in the form "field = value".
  • equalFields() public
    Builds equal condition or assignment with identifier wrapping.
  • exists() public
    Adds a new condition to the expression object in the form "EXISTS (...)".
  • Gets the currently configured conjunction for the conditions at this level of the expression tree.
  • gt() public
    Adds a new condition to the expression object in the form "field > value".
  • gte() public
    Adds a new condition to the expression object in the form "field >= value".
  • Returns true if this expression contains any other nested ExpressionInterface objects

  • in() public

    Adds a new condition to the expression object in the form "field IN (value1, value2)".

  • isCallable() public
    Check whether or not a callable is acceptable.
  • isNotNull() public
    Adds a new condition to the expression object in the form "field IS NOT NULL".
  • isNull() public
    Adds a new condition to the expression object in the form "field IS NULL".
  • Executes a callable function for each of the parts that form this expression.
  • like() public
    Adds a new condition to the expression object in the form "field LIKE value".
  • lt() public
    Adds a new condition to the expression object in the form "field < value".
  • lte() public
    Adds a new condition to the expression object in the form "field <= value".
  • not() public

    Adds a new set of conditions to this level of the tree and negates the final result by prepending a NOT, it will look like "NOT ( (condition1) AND (conditions2) )" conjunction depends on the one currently configured for this object.

  • notEq() public
    Adds a new condition to the expression object in the form "field != value".
  • notExists() public
    Adds a new condition to the expression object in the form "NOT EXISTS (...)".
  • notIn() public

    Adds a new condition to the expression object in the form "field NOT IN (value1, value2)".

  • notLike() public
    Adds a new condition to the expression object in the form "field NOT LIKE value".
  • or_() public

    Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

  • Changes the conjunction for the conditions at this level of the expression tree. If called with no arguments it will return the currently configured value.

  • sql() public

    Returns the string representation of this object so that it can be used in a SQL query. Note that values condition values are not included in the string, in their place placeholders are put and can be replaced by the quoted values accordingly.

  • tieWith() public

    Changes the conjunction for the conditions at this level of the expression tree. If called with no arguments it will return the currently configured value.

  • traverse() public

    Traverses the tree structure of this query expression by executing a callback function for each of the conditions that are included in this object. Useful for compiling the final expression, or doing introspection in the structure.

  • type() public
    Backwards compatible wrapper for tieWith()

Method Detail

__call()source public

__call( string $method , array $args )

Helps calling the and() and or() methods transparently.

Parameters

string $method
The method name.
array $args
The arguments to pass to the method.

Returns

Cake\Database\Expression\QueryExpression

Throws

BadMethodCallException

__clone()source public

__clone( )

Clone this object and its subtree of expressions.

__construct()source public

__construct( string|array|Cake\Database\ExpressionInterface $conditions [] , array|Cake\Database\TypeMap $types [] , string $conjunction 'AND' )

Constructor. A new expression object can be created without any params and be built dynamically. Otherwise it is possible to pass an array of conditions containing either a tree-like array structure to be parsed and/or other expression objects. Optionally, you can set the conjunction keyword to be used for joining each part of this level of the expression tree.

Parameters

string|array|Cake\Database\ExpressionInterface $conditions optional []

tree-like array structure containing all the conditions to be added or nested inside this expression object.

array|Cake\Database\TypeMap $types optional []

associative array of types to be associated with the values passed in $conditions.

string $conjunction optional 'AND'

the glue that will join all the string conditions at this level of the expression tree. For example "AND", "OR", "XOR"...

See

\Cake\Database\Expression\QueryExpression::add() for more details on $conditions and $types

_addConditions()source protected

_addConditions( array $conditions , array $types )

Auxiliary function used for decomposing a nested array of conditions and build a tree structure inside this object to represent the full SQL expression. String conditions are stored directly in the conditions, while any other representation is wrapped around an adequate instance or of this class.

Parameters

array $conditions
list of conditions to be stored in this object
array $types
list of types associated on fields referenced in $conditions

_calculateType()source protected

_calculateType( string|Cake\Database\Expression\IdentifierExpression $field )

Returns the type name for the passed field if it was stored in the typeMap

Parameters

string|Cake\Database\Expression\IdentifierExpression $field
The field name to get a type for.

Returns

string|null
The computed type or null, if the type is unknown.

_parseCondition()source protected

_parseCondition( string $field , mixed $value )

Parses a string conditions by trying to extract the operator inside it if any and finally returning either an adequate QueryExpression object or a plain string representation of the condition. This function is responsible for generating the placeholders and replacing the values by them, while storing the value elsewhere for future binding.

Parameters

string $field

The value from with the actual field and operator will be extracted.

mixed $value
The value to be bound to a placeholder for the field

Returns

string|Cake\Database\ExpressionInterface

add()source public

add( string|array|Cake\Database\ExpressionInterface $conditions , array $types [] )

Adds one or more conditions to this expression object. Conditions can be expressed in a one dimensional array, that will cause all conditions to be added directly at this level of the tree or they can be nested arbitrarily making it create more expression objects that will be nested inside and configured to use the specified conjunction.

If the type passed for any of the fields is expressed "type[]" (note braces) then it will cause the placeholder to be re-written dynamically so if the value is an array, it will create as many placeholders as values are in it.

Parameters

string|array|Cake\Database\ExpressionInterface $conditions

single or multiple conditions to be added. When using an array and the key is 'OR' or 'AND' a new expression object will be created with that conjunction and internal array value passed as conditions.

array $types optional []

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns


$this

See

\Cake\Database\Query::where() for examples on conditions

addCase()source public

addCase( array|Cake\Database\ExpressionInterface $conditions , array|Cake\Database\ExpressionInterface $values [] , array $types [] )

Adds a new case expression to the expression object

Parameters

array|Cake\Database\ExpressionInterface $conditions

The conditions to test. Must be a ExpressionInterface instance, or an array of ExpressionInterface instances.

array|Cake\Database\ExpressionInterface $values optional []

associative array of values to be associated with the conditions passed in $conditions. If there are more $values than $conditions, the last $value is used as the ELSE value

array $types optional []

associative array of types to be associated with the values passed in $values

Returns


$this

and_()source public

and_( string|array|Cake\Database\ExpressionInterface $conditions , array $types [] )

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "AND"

Parameters

string|array|Cake\Database\ExpressionInterface $conditions
to be joined with AND
array $types optional []

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

Cake\Database\Expression\QueryExpression

between()source public

between( string|Cake\Database\ExpressionInterface $field , mixed $from , mixed $to , string|null $type null )

Adds a new condition to the expression object in the form "field BETWEEN from AND to".

Parameters

string|Cake\Database\ExpressionInterface $field
The field name to compare for values in between the range.
mixed $from
The initial value of the range.
mixed $to
The ending value in the comparison range.
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

count()source public

count( )

Returns the number of internal conditions that are stored in this expression. Useful to determine if this expression object is void or it will generate a non-empty string when compiled

Returns

integer

Implementation of

Countable::count()

eq()source public

eq( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field = value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null

the type name for $value as configured using the Type map. If it is suffixed with "[]" and the value is an array then multiple placeholders will be created, one per each value in the array.

Returns


$this

equalFields()source public

equalFields( string $left , string $right )

Builds equal condition or assignment with identifier wrapping.

Parameters

string $left
Left join condition field name.
string $right
Right join condition field name.

Returns


$this

exists()source public

exists( Cake\Database\ExpressionInterface $query )

Adds a new condition to the expression object in the form "EXISTS (...)".

Parameters

Cake\Database\ExpressionInterface $query
the inner query

Returns


$this

getConjunction()source public

getConjunction( )

Gets the currently configured conjunction for the conditions at this level of the expression tree.

Returns

string

gt()source public

gt( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field > value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

gte()source public

gte( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field >= value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

hasNestedExpression()source public

hasNestedExpression( )

Returns true if this expression contains any other nested ExpressionInterface objects

Returns

boolean

in()source public

in( string|Cake\Database\ExpressionInterface $field , string|array $values , string|null $type null )

Adds a new condition to the expression object in the form "field IN (value1, value2)".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
string|array $values
the value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

isCallable()source public

isCallable( callable $c )

Check whether or not a callable is acceptable.

We don't accept ['class', 'method'] style callbacks, as they often contain user input and arrays of strings are easy to sneak in.

Parameters

callable $c
The callable to check.

Returns

boolean
Valid callable.

isNotNull()source public

isNotNull( string|Cake\Database\ExpressionInterface $field )

Adds a new condition to the expression object in the form "field IS NOT NULL".

Parameters

string|Cake\Database\ExpressionInterface $field

database field to be tested for not null

Returns


$this

isNull()source public

isNull( string|Cake\Database\ExpressionInterface $field )

Adds a new condition to the expression object in the form "field IS NULL".

Parameters

string|Cake\Database\ExpressionInterface $field

database field to be tested for null

Returns


$this

iterateParts()source public

iterateParts( callable $callable )

Executes a callable function for each of the parts that form this expression.

The callable function is required to return a value with which the currently visited part will be replaced. If the callable function returns null then the part will be discarded completely from this expression.

The callback function will receive each of the conditions as first param and the key as second param. It is possible to declare the second parameter as passed by reference, this will enable you to change the key under which the modified part is stored.

Parameters

callable $callable
The callable to apply to each part.

Returns


$this

like()source public

like( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field LIKE value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

lt()source public

lt( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field < value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

lte()source public

lte( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field <= value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

not()source public

not( string|array|Cake\Database\ExpressionInterface $conditions , array $types [] )

Adds a new set of conditions to this level of the tree and negates the final result by prepending a NOT, it will look like "NOT ( (condition1) AND (conditions2) )" conjunction depends on the one currently configured for this object.

Parameters

string|array|Cake\Database\ExpressionInterface $conditions
to be added and negated
array $types optional []

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns


$this

notEq()source public

notEq( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field != value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null

the type name for $value as configured using the Type map. If it is suffixed with "[]" and the value is an array then multiple placeholders will be created, one per each value in the array.

Returns


$this

notExists()source public

notExists( Cake\Database\ExpressionInterface $query )

Adds a new condition to the expression object in the form "NOT EXISTS (...)".

Parameters

Cake\Database\ExpressionInterface $query
the inner query

Returns


$this

notIn()source public

notIn( string|Cake\Database\ExpressionInterface $field , array $values , string|null $type null )

Adds a new condition to the expression object in the form "field NOT IN (value1, value2)".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
array $values
the value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

notLike()source public

notLike( string|Cake\Database\ExpressionInterface $field , mixed $value , string|null $type null )

Adds a new condition to the expression object in the form "field NOT LIKE value".

Parameters

string|Cake\Database\ExpressionInterface $field
Database field to be compared against value
mixed $value
The value to be bound to $field for comparison
string|null $type optional null
the type name for $value as configured using the Type map.

Returns


$this

or_()source public

or_( string|array|Cake\Database\ExpressionInterface $conditions , array $types [] )

Returns a new QueryExpression object containing all the conditions passed and set up the conjunction to be "OR"

Parameters

string|array|Cake\Database\ExpressionInterface $conditions
to be joined with OR
array $types optional []

associative array of fields pointing to the type of the values that are being passed. Used for correctly binding values to statements.

Returns

Cake\Database\Expression\QueryExpression

setConjunction()source public

setConjunction( string $conjunction )

Changes the conjunction for the conditions at this level of the expression tree. If called with no arguments it will return the currently configured value.

Parameters

string $conjunction

Value to be used for joining conditions. If null it will not set any value, but return the currently stored one

Returns


$this

sql()source public

sql( Cake\Database\ValueBinder $generator )

Returns the string representation of this object so that it can be used in a SQL query. Note that values condition values are not included in the string, in their place placeholders are put and can be replaced by the quoted values accordingly.

Parameters

Cake\Database\ValueBinder $generator
Placeholder generator object

Returns

string

Implementation of

Cake\Database\ExpressionInterface::sql()

tieWith()source public

tieWith( string|null $conjunction null )

Changes the conjunction for the conditions at this level of the expression tree. If called with no arguments it will return the currently configured value.

Deprecated

3.4.0 Use setConjunction()/getConjunction() instead.

Parameters

string|null $conjunction optional null

value to be used for joining conditions. If null it will not set any value, but return the currently stored one

Returns

string|Cake\Database\Expression\QueryExpression
$this

traverse()source public

traverse( callable $callable )

Traverses the tree structure of this query expression by executing a callback function for each of the conditions that are included in this object. Useful for compiling the final expression, or doing introspection in the structure.

Callback function receives as only argument an instance of a QueryExpression

Parameters

callable $callable
The callable to apply to all sub-expressions.

Implementation of

Cake\Database\ExpressionInterface::traverse()

type()source public

type( string|null $conjunction null )

Backwards compatible wrapper for tieWith()

Deprecated

3.2.0 Use tieWith() instead

Parameters

string|null $conjunction optional null

value to be used for joining conditions. If null it will not set any value, but return the currently stored one

Returns

string|Cake\Database\Expression\QueryExpression
$this

Methods used from Cake\Database\TypeMapTrait

defaultTypes()source public

defaultTypes( array $types null )

Allows setting default types when chaining query

Deprecated

3.4.0 Use setDefaultTypes()/getDefaultTypes() instead.

Parameters

array $types optional null
The array of types to set.

Returns


$this|array

getDefaultTypes()source public

getDefaultTypes( )

Gets default types of current type map.

Returns

array

getTypeMap()source public

getTypeMap( )

Returns the existing type map.

Returns

Cake\Database\TypeMap

setDefaultTypes()source public

setDefaultTypes( array $types )

Allows setting default types when chaining query.

Parameters

array $types
The array of types to set.

Returns


$this

setTypeMap()source public

setTypeMap( array|Cake\Database\TypeMap $typeMap )

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

Parameters

array|Cake\Database\TypeMap $typeMap
Creates a TypeMap if array, otherwise sets the given TypeMap

Returns


$this

typeMap()source public

typeMap( array|Cake\Database\TypeMap|null $typeMap null )

Creates a new TypeMap if $typeMap is an array, otherwise returns the existing type map or exchanges it for the given one.

Deprecated

3.4.0 Use setTypeMap()/getTypeMap() instead.

Parameters

array|Cake\Database\TypeMap|null $typeMap optional null
Creates a TypeMap if array, otherwise sets the given TypeMap

Returns


$this|\Cake\Database\TypeMap

Properties detail

$_conditionssource

protected array

A list of strings or other expression objects that represent the "branches" of the expression tree. For example one key of the array might look like "sum > :value"

[]

$_conjunctionsource

protected string

String to be used for joining each of the internal expressions this object internally stores for example "AND", "OR", etc.

© 2005–2017 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/3.4/class-Cake.Database.Expression.QueryExpression.html