Zeta Components - high quality PHP components

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.

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

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', $q->bindValue( 1 ), $q->bindValue( 5 ) ) );

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

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

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

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->eq( 'id', $q->bindValue( 1 ) ) );

If the alias does not exists in the list of aliases it is returned unchanged.

This method is similar to getIdentifier except that it works on an array.

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->gt( 'id', $q->bindValue( 1 ) ) );

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->gte( 'id', $q->bindValue( 1 ) ) );

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:

1.  $q->select( '*' )->from( 'table' )
2.                   ->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.

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->isNull( 'id' ) );

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', $q->bindValue( 1 ) ),
5.                                      $e->eq( 'id', $q->bindValue( 2 ) ) ) );

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

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', $q->bindValue( 1 ) ),
5.                                     $e->eq( 'id', $q->bindValue( 2 ) ) ) );

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->lt( 'id', $q->bindValue( 1 ) ) );

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->lte( 'id', $q->bindValue( 1 ) ) );

Note: Not SQL92, but common functionality

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

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select( '*' )->from( 'table' )
3.                   ->where( $q->expr->neq( 'id', $q->bindValue( 1 ) ) );

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $e = $q->expr;
3.  $q->select( '*' )->from( 'table' )
4.                   ->where( $e->eq( 'id', $e->not( 'null' ) ) );

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

Example:

1.  $q = ezcDbInstance::get()->createSelectQuery();
2.  $q->select(
3.       $q->expr->searchedCase(
4.             array( $q->expr->gte( 'column1', 20 ), 'column1' )
5.           , array( $q->expr->gte( 'column2', 50 ), 'column2' )
6.           , 'column3'
7.       )
8.   )
9.      ->from( 'table' );

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.

Quoting mode is set to ON by default. $q->expr->in( 'column1', 'Hello', 'world' ) will produce SQL "column1 IN ( 'Hello', 'world' )" ( note quotes in SQL ).

User must execute setValuesQuoting( false ) before call to function where quoting of parameters is not desirable. Example:

1.  $q->expr->setValuesQuoting( false );
2.  $q->expr->in( 'column1', 'SELECT * FROM table' )

Quoting mode will remain unchanged until next call to setValuesQuoting().

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

Note: Not SQL92, but common functionality.