PHP Interface eZ\Publish\Core\Persistence\Database\Expression

Datei anzeigen Open project: ezsystems/ezpublish-kernel

Public Methods

Method	Description
add ( ) : string	Returns the SQL to add values or expressions together.
avg ( string $column ) : string	Returns the average value of a column.
between ( string $expression, string $value1, string $value2 ) : string	Returns SQL that checks if an expression evaluates to a value between two values.
bitAnd ( string $value1, string $value2 ) : string	Returns the SQL that performs the bitwise AND on two values.
bitOr ( string $value1, string $value2 ) : string	Returns the SQL that performs the bitwise OR on two values.
concat ( )	Returns a series of strings concatinated.
count ( string $column ) : string	Returns the number of rows (without a NULL value) of a column.
div ( ) : string	Returns the SQL to divide values or expressions by eachother.
eq ( string $value1, string $value2 ) : string	Returns the SQL to check if two values are equal.
gt ( string $value1, string $value2 ) : string	Returns the SQL to check if one value is greater than another value.
gte ( string $value1, string $value2 ) : string	Returns the SQL to check if one value is greater than or equal to another value.
in ( string $column ) : string	Returns the SQL to check if a value is one in a set of given values.
isNull ( string $expression ) : string	Returns SQL that checks if a expression is null.
lAnd ( ) : string	Returns the SQL to bind logical expressions together using a logical and.
lOr ( ) : string	Returns the SQL to bind logical expressions together using a logical or.
length ( string $column ) : string	Returns the length of text field $column.
like ( string $expression, string $pattern )	Match a partial string in a column.
lower ( string $value ) : string	Returns the SQL to change all characters to lowercase.
lt ( string $value1, string $value2 ) : string	Returns the SQL to check if one value is less than another value.
lte ( string $value1, string $value2 ) : string	Returns the SQL to check if one value is less than or equal to another value.
max ( string $column ) : string	Returns the highest value of a column.
min ( string $column ) : string	Returns the lowest value of a column.
mod ( string $expression1, string $expression2 ) : string	Returns the remainder of the division operation $expression1 / $expression2.
mul ( ) : string	Returns the SQL to multiply values or expressions by eachother.
neq ( string $value1, string $value2 ) : string	Returns the SQL to check if two values are unequal.
not ( string $expression ) : string	Returns the SQL for a logical not, negating the $expression.
now ( ) : string	Returns the current system date and time in the database internal format.
position ( string $substr, string $value ) : string	Returns the SQL to locate the position of the first occurrence of a substring.
round ( string $column, integer $decimals ) : string	Rounds a numeric field to the number of decimals specified.
searchedCase ( ) : string	Returns a searched CASE statement.
sub ( ) : string	Returns the SQL to subtract values or expressions from eachother.
subString ( string $value, integer $from, integer $len = null ) : string	Returns part of a string.
sum ( string $column ) : string	Returns the total sum of a column.
upper ( string $value ) : string	Returns the SQL to change all characters to uppercase.

Method Details

add() public method

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 )  );

public add ( ) : string
return	string	an expression

avg() public method

Returns the average value of a column.

public avg ( string $column ) : string
$column	string	the column to use
return	string

between() public method

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 ) ) );

public between ( string $expression, string $value1, string $value2 ) : string
$expression	string	the value to compare to
$value1	string	the lower value to compare with
$value2	string	the higher value to compare with
return	string	logical expression

bitAnd() public method

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

public bitAnd ( string $value1, string $value2 ) : string
$value1	string
$value2	string
return	string

bitOr() public method

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

public bitOr ( string $value1, string $value2 ) : string
$value1	string
$value2	string
return	string

concat() public method

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

public concat ( )

count() public method

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

public count ( string $column ) : string
$column	string	the column to use
return	string

div() public method

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 )  );

public div ( ) : string
return	string	an expression

eq() public method

Example:


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

public eq ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

gt() public method

Example:


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

public gt ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

gte() public method

Example:


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

public gte ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

in() public method

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

public in ( string $column ) : string
$column	string	the value that should be matched against
return	string	logical expression

isNull() public method

Example:


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

public isNull ( string $expression ) : string
$expression	string	the expression that should be compared to null
return	string	logical expression

lAnd() public method

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 ) ) ) );

public lAnd ( ) : string
return	string	a logical expression

lOr() public method

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 ) ) ) );

public lOr ( ) : string
return	string	a logical expression

length() public method

Returns the length of text field $column.

public length ( string $column ) : string
$column	string
return	string

like() public method

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

public like ( string $expression, string $pattern )
$expression	string	the name of the expression to match on
$pattern	string	the pattern to match with.

lower() public method

Returns the SQL to change all characters to lowercase.

public lower ( string $value ) : string
$value	string
return	string

lt() public method

Example:


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

public lt ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

lte() public method

Example:


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

public lte ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

max() public method

Returns the highest value of a column.

public max ( string $column ) : string
$column	string	the column to use
return	string

min() public method

Returns the lowest value of a column.

public min ( string $column ) : string
$column	string	the column to use
return	string

mod() public method

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

public mod ( string $expression1, string $expression2 ) : string
$expression1	string
$expression2	string
return	string

mul() public method

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 )  );

public mul ( ) : string
return	string	an expression

neq() public method

Example:


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

public neq ( string $value1, string $value2 ) : string
$value1	string	logical expression to compare
$value2	string	logical expression to compare with
return	string	logical expression

not() public method

Example:


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

public not ( string $expression ) : string
$expression	string
return	string	a logical expression

now() public method

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

public now ( ) : string
return	string

position() public method

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

public position ( string $substr, string $value ) : string
$substr	string
$value	string
return	string

round() public method

Rounds a numeric field to the number of decimals specified.

public round ( string $column, integer $decimals ) : string
$column	string
$decimals	integer
return	string

searchedCase() public method

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' );

public searchedCase ( ) : string
return	string

sub() public method

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 )  );

public sub ( ) : string
return	string	an expression

subString() public method

Note: Not SQL92, but common functionality.

public subString ( string $value, integer $from, integer $len = null ) : string
$value	string	the target $value the string or the string column.
$from	integer	extract from this characeter.
$len	integer	extract this amount of characters.
return	string	sql that extracts part of a string.

sum() public method

Returns the total sum of a column.

public sum ( string $column ) : string
$column	string	the column to use
return	string

upper() public method

Returns the SQL to change all characters to uppercase.

public upper ( string $value ) : string
$value	string
return	string