Interface

eZ\Publish\Core\Persistence\Database\Expression

interface Expression

Interface for generation of all the expressions for database abstractions.

Methods

string lOr()

Returns the SQL to bind logical expressions together using a logical or.

string lAnd()

Returns the SQL to bind logical expressions together using a logical and.

string not(string $expression)

Returns the SQL for a logical not, negating the $expression.

string add()

Returns the SQL to add values or expressions together.

string sub()

Returns the SQL to subtract values or expressions from eachother.

string mul()

Returns the SQL to multiply values or expressions by eachother.

string div()

Returns the SQL to divide values or expressions by eachother.

string eq(string $value1, string $value2)

Returns the SQL to check if two values are equal.

string neq(string $value1, string $value2)

Returns the SQL to check if two values are unequal.

string gt(string $value1, string $value2)

Returns the SQL to check if one value is greater than another value.

string gte(string $value1, string $value2)

Returns the SQL to check if one value is greater than or equal to another value.

string lt(string $value1, string $value2)

Returns the SQL to check if one value is less than another value.

string lte(string $value1, string $value2)

Returns the SQL to check if one value is less than or equal to another value.

string in($column)

Returns the SQL to check if a value is one in a set of given values..

string isNull(string $expression)

Returns SQL that checks if a expression is null.

string between(string $expression, string $value1, string $value2)

Returns SQL that checks if an expression evaluates to a value between two values.

like(string $expression, string $pattern)

Match a partial string in a column.

string avg(string $column)

Returns the average value of a column.

string count(string $column)

Returns the number of rows (without a NULL value) of a column.

string max(string $column)

Returns the highest value of a column.

string min(string $column)

Returns the lowest value of a column.

string sum(string $column)

Returns the total sum of a column.

string length(string $column)

Returns the length of text field $column.

string round(string $column, int $decimals)

Rounds a numeric field to the number of decimals specified.

string mod(string $expression1, string $expression2)

Returns the remainder of the division operation $expression1 / $expression2.

string now()

Returns the current system date and time in the database internal format.

string subString(string $value, int $from, int $len = null)

Returns part of a string.

concat()

Returns a series of strings concatinated.

string position(string $substr, string $value)

Returns the SQL to locate the position of the first occurrence of a substring.

string lower(string $value)

Returns the SQL to change all characters to lowercase.

string upper(string $value)

Returns the SQL to change all characters to uppercase.

string bitAnd(string $value1, string $value2)

Returns the SQL that performs the bitwise AND on two values.

string bitOr(string $value1, string $value2)

Returns the SQL that performs the bitwise OR on two values.

string searchedCase()

Returns a searched CASE statement.

Details

at line 38
public string lOr()

Returns the SQL to bind logical expressions together using a logical or.

lOr() accepts an arbitrary number of parameters. Each parameter must contain a logical expression or an array with logical expressions.

Example: $q = $dbHandler->createSelectQuery(); $e = $q->expr; $q->select( '*' )->from( 'table' ) ->where( $e->lOr( $e->eq( 'id', $q->bindValue( 1 ) ), $e->eq( 'id', $q->bindValue( 2 ) ) ) );

Return Value

string a logical expression

Exceptions

QueryException if called with no parameters.

at line 59
public string lAnd()

Returns the SQL to bind logical expressions together using a logical and.

lAnd() accepts an arbitrary number of parameters. Each parameter must contain a logical expression or an array with logical expressions.

Example: $q = $dbHandler->createSelectQuery(); $e = $q->expr; $q->select( '*' )->from( 'table' ) ->where( $e->lAnd( $e->eq( 'id', $q->bindValue( 1 ) ), $e->eq( 'id', $q->bindValue( 2 ) ) ) );

Return Value

string a logical expression

Exceptions

QueryException if called with no parameters.

at line 76
public string not(string $expression)

Returns the SQL for a logical not, negating the $expression.

Example: $q = $dbHandler->createSelectQuery(); $e = $q->expr; $q->select( '*' )->from( 'table' ) ->where( $e->eq( 'id', $e->not( 'null' ) ) );

Parameters

string $expression

Return Value

string a logical expression

at line 98
public string add()

Returns the SQL to add values or expressions together.

add() accepts an arbitrary number of parameters. Each parameter must contain a value or an expression or an array with values or expressions.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->add( 'id', 2 ) );

Return Value

string an expression

Exceptions

QueryException if called with no parameters.

at line 120
public string sub()

Returns the SQL to subtract values or expressions from eachother.

subtract() accepts an arbitrary number of parameters. Each parameter must contain a value or an expression or an array with values or expressions.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->subtract( 'id', 2 ) );

Return Value

string an expression

Exceptions

QueryException if called with no parameters.

at line 142
public string mul()

Returns the SQL to multiply values or expressions by eachother.

multiply() accepts an arbitrary number of parameters. Each parameter must contain a value or an expression or an array with values or expressions.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->multiply( 'id', 2 ) );

Return Value

string an expression

Exceptions

QueryException if called with no parameters.

at line 164
public string div()

Returns the SQL to divide values or expressions by eachother.

divide() accepts an arbitrary number of parameters. Each parameter must contain a value or an expression or an array with values or expressions.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->divide( 'id', 2 ) );

Return Value

string an expression

Exceptions

QueryException if called with no parameters.

at line 181
public string eq(string $value1, string $value2)

Returns the SQL to check if two values are equal.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->eq( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 198
public string neq(string $value1, string $value2)

Returns the SQL to check if two values are unequal.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->neq( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 215
public string gt(string $value1, string $value2)

Returns the SQL to check if one value is greater than another value.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->gt( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 233
public string gte(string $value1, string $value2)

Returns the SQL to check if one value is greater than or equal to another value.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->gte( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 250
public string lt(string $value1, string $value2)

Returns the SQL to check if one value is less than another value.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->lt( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 268
public string lte(string $value1, string $value2)

Returns the SQL to check if one value is less than or equal to another value.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->lte( 'id', $q->bindValue( 1 ) ) );

Parameters

string $value1 logical expression to compare
string $value2 logical expression to compare with

Return Value

string logical expression

at line 301
public string in($column)

Returns the SQL to check if a value is one in a set of given values..

in() accepts an arbitrary number of parameters. The first parameter must always specify the value that should be matched against. Successive parameters must contain a logical expression or an array with logical expressions. These expressions will be matched against the first parameter.

Example: $q->select( '*' )->from( 'table' ) ->where( $q->expr->in( 'id', 1, 2, 3 ) );

Optimization note: Call setQuotingValues( false ) before using in() with big lists of numeric parameters. This avoid redundant quoting of numbers in resulting SQL query and saves time of converting strings to numbers inside RDBMS.

Parameters

$column

Return Value

string logical expression

Exceptions

QueryException if called with less than two parameters.
QueryException if the 2nd parameter is an empty array.

at line 317
public string isNull(string $expression)

Returns SQL that checks if a expression is null.

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->isNull( 'id' ) );

Parameters

string $expression the expression that should be compared to null

Return Value

string logical expression

at line 342
public string between(string $expression, string $value1, string $value2)

Returns SQL that checks if an expression evaluates to a value between two values.

The parameter $expression is checked if it is between $value1 and $value2.

Note: There is a slight difference in the way BETWEEN works on some databases. http://www.w3schools.com/sql/sql_between.asp. If you want complete database independence you should avoid using between().

Example: $q = $dbHandler->createSelectQuery(); $q->select( '*' )->from( 'table' ) ->where( $q->expr->between( 'id', $q->bindValue( 1 ), $q->bindValue( 5 ) ) );

Parameters

string $expression the value to compare to
string $value1 the lower value to compare with
string $value2 the higher value to compare with

Return Value

string logical expression

at line 354
public like(string $expression, string $pattern)

Match a partial string in a column.

Like will look for the pattern in the column given. Like accepts the wildcards '_' matching a single character and '%' matching any number of characters.

Parameters

string $expression the name of the expression to match on
string $pattern the pattern to match with.

at line 363
public string avg(string $column)

Returns the average value of a column.

Parameters

string $column the column to use

Return Value

string

at line 375
public string count(string $column)

Returns the number of rows (without a NULL value) of a column.

If a '*' is used instead of a column the number of selected rows is returned.

Parameters

string $column the column to use

Return Value

string

at line 384
public string max(string $column)

Returns the highest value of a column.

Parameters

string $column the column to use

Return Value

string

at line 393
public string min(string $column)

Returns the lowest value of a column.

Parameters

string $column the column to use

Return Value

string

at line 402
public string sum(string $column)

Returns the total sum of a column.

Parameters

string $column the column to use

Return Value

string

at line 411
public string length(string $column)

Returns the length of text field $column.

Parameters

string $column

Return Value

string

at line 421
public string round(string $column, int $decimals)

Rounds a numeric field to the number of decimals specified.

Parameters

string $column
int $decimals

Return Value

string

at line 432
public string mod(string $expression1, string $expression2)

Returns the remainder of the division operation $expression1 / $expression2.

Parameters

string $expression1
string $expression2

Return Value

string

at line 440
public string now()

Returns the current system date and time in the database internal format.

Return Value

string

at line 455
public string subString(string $value, int $from, int $len = null)

Returns part of a string.

Note: Not SQL92, but common functionality.

Parameters

string $value the target $value the string or the string column.
int $from extract from this characeter.
int $len extract this amount of characters.

Return Value

string sql that extracts part of a string.

at line 465
public concat()

Returns a series of strings concatinated.

concat() accepts an arbitrary number of parameters. Each parameter must contain an expression or an array with expressions.

at line 475
public string position(string $substr, string $value)

Returns the SQL to locate the position of the first occurrence of a substring.

Parameters

string $substr
string $value

Return Value

string

at line 484
public string lower(string $value)

Returns the SQL to change all characters to lowercase.

Parameters

string $value

Return Value

string

at line 493
public string upper(string $value)

Returns the SQL to change all characters to uppercase.

Parameters

string $value

Return Value

string

at line 503
public string bitAnd(string $value1, string $value2)

Returns the SQL that performs the bitwise AND on two values.

Parameters

string $value1
string $value2

Return Value

string

at line 513
public string bitOr(string $value1, string $value2)

Returns the SQL that performs the bitwise OR on two values.

Parameters

string $value1
string $value2

Return Value

string

at line 539
public string searchedCase()

Returns a searched CASE statement.

Accepts an arbitrary number of parameters. The first parameter (array) must always be specified, the last parameter (string) specifies the ELSE result.

Example: $q = $dbHandler->createSelectQuery(); $q->select( $q->expr->searchedCase( array( $q->expr->gte( 'column1', 20 ), 'column1' ) , array( $q->expr->gte( 'column2', 50 ), 'column2' ) , 'column3' ) ) ->from( 'table' );

Return Value

string

Exceptions

QueryException