Path

ez components / documentation / api reference / 1.1.2 / database

eZ Components 1.1.2

Database: ezcQueryExpression

[ Tutorial ] [ Class tree ] [ Element index ] [ ChangeLog ] [ Credits ]

Class: ezcQueryExpression

The ezcQueryExpression class is used to create database independent SQL expression. [source]
The QueryExpression class is usually used through the 'expr' variable in one of the Select, Insert, Update or Delete classes.
Note that the methods for logical or and and are named lOr and lAnd respectively. This is because and and or are reserved names in PHP and can not be used in method names.

Descendents

Child Class Description
ezcQueryExpressionOracle The ezcQueryExpressionPgsql class is used to create SQL expression for PostgreSQL.
ezcQueryExpressionSqlite The ezcQueryExpressionSqlite class is used to create SQL expression for SQLite.
ezcQueryExpressionPgsql The ezcQueryExpressionPgsql class is used to create SQL expression for PostgreSQL.

Method Summary

public ezcQueryExpression __construct( [$aliases = array()] )
Constructs an empty ezcQueryExpression
public string add( 0 )
Returns the SQL to add values or expressions together.
public string avg( $column )
Returns the average value of a column
public string between( $expression, $value1, $value2 )
Returns SQL that checks if an expression evaluates to a value between two values.
public void concat( 0 )
Returns a series of strings concatinated
public string count( $column )
Returns the number of rows (without a NULL value) of a column
public string div( 0 )
Returns the SQL to divide values or expressions by eachother.
public string eq( $value1, $value2 )
Returns the SQL to check if two values are equal.
protected string getIdentifier( $alias )
Returns the correct identifier for the alias $alias.
protected array(string) getIdentifiers( $aliasList, $alias )
Returns the correct identifiers for the aliases found in $aliases.
public string gt( $value1, $value2 )
Returns the SQL to check if one value is greater than another value.
public string gte( $value1, $value2 )
Returns the SQL to check if one value is greater than or equal to another value.
public bool hasAliases( )
Returns true if this object has aliases.
public string in( $column, 1 )
Returns the SQL to check if a value is one in a set of given values..
public string isNull( $expression )
Returns SQL that checks if a expression is null.
public string lAnd( )
Returns the SQL to bind logical expressions together using a logical and.
public string length( $column, $expression1, $expression2 )
Returns the length of a text field.
public void like( $expression, $pattern )
Match a partial string in a column.
public string lOr( )
Returns the SQL to bind logical expressions together using a logical or.
public string lt( $value1, $value2 )
Returns the SQL to check if one value is less than another value.
public string lte( $value1, $value2 )
Returns the SQL to check if one value is less than or equal to another value.
public string max( $column )
Returns the highest value of a column
public string md5( $column )
Returns the md5 sum of a field.
public string min( $column )
Returns the lowest value of a column
public string mod( $expression1, $expression2 )
Returns the remainder of the division operation $expression1 / $expression2.
public string mul( 0 )
Returns the SQL to multiply values or expressions by eachother.
public string neq( $value1, $value2 )
Returns the SQL to check if two values are unequal.
public string not( $expression )
Returns the SQL for a logical not.
public string now( )
Returns the current system date.
public string round( $column, $decimals, $expression1, $expression2 )
Rounds a numeric field to the number of decimals specified.
public void setAliases( $aliases )
Sets the aliases $aliases for this object.
public string sub( 0 )
Returns the SQL to subtract values or expressions from eachother.
public string subString( $value, $from, [$len = null] )
Returns part of a string.
public string sum( $column )
Returns the total sum of a column

Methods

__construct

ezcQueryExpression __construct( [ $aliases = array()] )
Constructs an empty ezcQueryExpression

Parameters

Name Type Description
$aliases array  

Redefined in descendants as

Method Description
ezcQueryExpressionOracle::__construct() Constructs an empty ezcQueryExpression
ezcQueryExpressionPgsql::__construct() Constructs an empty ezcQueryExpression with the versoin $version.

add

string add( string|array(string) 0 )
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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->add( 'id', 2 )  );

Parameters

Name Type Description
0 string|array(string)  

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

avg

string avg( string $column )
Returns the average value of a column

Parameters

Name Type Description
$column string the column to use

between

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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->between( 'id' , 1, 5 ) );

Parameters

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

concat

void concat( string|array(string) 0 )
Returns a series of strings concatinated
concat() accepts an arbitrary number of parameters. Each parameter must contain an expression or an array with expressions.

Parameters

Name Type Description
0 string|array(string) strings that will be concatinated.

Redefined in descendants as

Method Description
ezcQueryExpressionOracle::concat() Returns a series of strings concatinated
ezcQueryExpressionPgsql::concat() Returns a series of strings concatinated

count

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

Name Type Description
$column string the column to use

div

string div( string|array(string) 0 )
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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->divide( 'id', 2 )  );

Parameters

Name Type Description
0 string|array(string)  

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

eq

string eq( string $value1, string $value2 )
Returns the SQL to check if two values are equal.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->eq( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

getIdentifier

string getIdentifier( string $alias )
Returns the correct identifier for the alias $alias.
If the alias does not exists in the list of aliases it is returned unchanged.

Parameters

Name Type Description
$alias string  

getIdentifiers

array(string) getIdentifiers( $aliasList, array(string) $alias )
Returns the correct identifiers for the aliases found in $aliases.
This method is similar to getIdentifier except that it works on an array.

Parameters

Name Type Description
$alias array(string)  
$aliasList array  

gt

string gt( string $value1, string $value2 )
Returns the SQL to check if one value is greater than another value.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->gt( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

gte

string gte( string $value1, string $value2 )
Returns the SQL to check if one value is greater than or equal to another value.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->gte( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

hasAliases

bool hasAliases( )
Returns true if this object has aliases.

in

string in( string $column, string|array(string) 1 )
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 must contain a logical expression or an array with logical expressions. These expressions will be matched against the first parameter.
Example: 
1.  $q->select( '*' )->from( 'table' )
2.                   ->where( $q->expr->in( 'id', 1, 2, 3 ) );

Parameters

Name Type Description
$column string the value that should be matched against
1 string|array(string) values that will be matched against $column

Throws

ClassDescription
ezcDbAbstractionException if called with less than two parameters..

isNull

string isNull( string $expression )
Returns SQL that checks if a expression is null.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->isNull( 'id') );

Parameters

Name Type Description
$expression string the expression that should be compared to null

lAnd

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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $e = $q->expr;
3.  $q->select( '*' )->from( 'table' )
4.                   ->where( $e->lAnd( $e->eq( 'id', 1 ),
5.                                      $e->eq( 'id', 2 ) ) );

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

length

string length( $column, string $expression1, string $expression2 )
Returns the length of a text field.

Parameters

Name Type Description
$expression1 string  
$expression2 string  
$column  

like

void 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

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

lOr

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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $e = $q->expr;
3.  $q->select( '*' )->from( 'table' )
4.                   ->where( $e->lOr( $e->eq( 'id', 1 ),
5.                                     $e->eq( 'id', 2 ) ) );

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

lt

string lt( string $value1, string $value2 )
Returns the SQL to check if one value is less than another value.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->lt( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

lte

string lte( string $value1, string $value2 )
Returns the SQL to check if one value is less than or equal to another value.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->lte( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

max

string max( string $column )
Returns the highest value of a column

Parameters

Name Type Description
$column string the column to use

md5

string md5( $column )
Returns the md5 sum of a field.
Note: Not SQL92, but common functionality

Parameters

Name Type Description
$column  

Redefined in descendants as

Method Description
ezcQueryExpressionPgsql::md5() Returns the md5 sum of a field.

min

string min( string $column )
Returns the lowest value of a column

Parameters

Name Type Description
$column string the column to use

mod

string mod( string $expression1, string $expression2 )
Returns the remainder of the division operation $expression1 / $expression2.

Parameters

Name Type Description
$expression1 string  
$expression2 string  

mul

string mul( string|array(string) 0 )
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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->multiply( 'id', 2 )  );

Parameters

Name Type Description
0 string|array(string)  

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

neq

string neq( string $value1, string $value2 )
Returns the SQL to check if two values are unequal.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->neq( 'id', 1 ) );

Parameters

Name Type Description
$value1 string logical expression to compare
$value2 string logical expression to compare with

not

string not( $expression )
Returns the SQL for a logical not.
Example: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $e = $q->expr;
3.  $q->select( '*' )->from( 'table' )
4.                   ->where( $e->eq( 'id', $e->not( 'null' ) ) );

Parameters

Name Type Description
$expression  

now

string now( )
Returns the current system date.

round

string round( $column, $decimals, string $expression1, string $expression2 )
Rounds a numeric field to the number of decimals specified.

Parameters

Name Type Description
$expression1 string  
$expression2 string  
$column  
$decimals  

setAliases

void setAliases( $aliases )
Sets the aliases $aliases for this object.
The aliases can be used to substitute the column and table names with more friendly names. E.g PersistentObject uses it to allow using property and class names instead of column and table names.

Parameters

Name Type Description
$aliases array(string=>string)  

sub

string sub( string|array(string) 0 )
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: 
1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->subtract( 'id', 2 )  );

Parameters

Name Type Description
0 string|array(string)  

Throws

ClassDescription
ezcDbAbstractionException if called with no parameters.

subString

string subString( string $value, int $from, [int $len = null] )
Returns part of a string.
Note: Not SQL92, but common functionality.

Parameters

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

Redefined in descendants as

Method Description
ezcQueryExpressionSqlite::subString() Returns part of a string.
ezcQueryExpressionPgsql::subString() Returns part of a string.

sum

string sum( string $column )
Returns the total sum of a column

Parameters

Name Type Description
$column string the column to use

Last updated: Thu, 01 Nov 2007