db = $db;
}
/**
* Resets the query object for reuse.
*
* @returns void
*/
public function reset()
{
$this->selectString = '';
$this->fromString = '';
$this->whereString = '';
$this->this->orderString = '';
$this->groupString = '';
$this->limitString = '';
$this->boundCounter = 0;
$this->boundValues = array();
}
/**
* This method provides a shortcut for parameter binding when using
* prepared statements.
*
* The parameter $value specifies the variable that you want to bind. If
* $placeholder is not provided bind() will automatically create a
* placeholder for you. An automatic placeholder will be of the name
* 'ezcValue1', 'ezcValue2' etc.
*
* @see http://no.php.net/manual/en/function.pdostatement-bindparam.php
* @see doBind()
* Example:
*
* $value = 2;
* $q->eq( 'id', $q->bind( $value ) );
*
*
* @param &mixed the variable to bind.
* @param string the name to bind with. The string must start with a colon ':'.
* @returns &mixed the placeholder name used.
*/
public function bind( &$value, $placeholder = '' )
{
if ( $placeholder == '' )
{
$this->boundCounter++;
$placeholder = ":ezcValue{$this->boundCounter}";
}
$this->boundValues[$placeholder] =& $value;
// $x = 0;
// $x =& $this->boundValues['a'];
// foreach ($this->bindings as $key => &$value)
return $placeholder;
}
// automatic binding
/**
* Performs binding of variables bound with bind()
*
* This method must be called if you have used bind()
* to specify $values in your query.
*
* @see bind
* @param PDOStatement $stmt the statement to bind the values to.
* @returns void
*/
public function doBind( PDOStatement $stmt )
{
foreach ( $this->boundValues as $key => &$value )
{
$stmt->bindParam( $key, $value );
}
}
/**
* TODO: not decided if we should actually do it like this.
*/
public function prepare()
{
$stmt = $this->db->prepare( $this->build() );
$this->doBind( $stmt );
return $stmt;
}
/**
* Open the query and selects which columns you want to return with
* the query.
*
* select() accepts an arbitrary number of parameters. Each parameter
* must contain either the name of a column or an array containing
* the names of the columns.
*
* Example:
*
* $q->select( 'column1', 'column2' );
*
* The same could also be written
*
* $columns[] = 'column1';
* $columns[] = 'column2;
* $q->select( $columns );
*
*
* @throws ezcDbAbstractionException if called more than once.
* @throws ezcDbAbstractionException if called with no parameters..
* @param string|array(string) Either a string with a column name or an array of column names.
* @returns ezcQuery returns a pointer to $this.
*/
public function select()
{
if ( $this->selectString != '' )
{
throw new ezcDbAbstractionException( ezcDbAbstractionException::INVALID_CALL,
'Invalid second call to select(). select() can be called only once.' );
}
$args = func_get_args();
$cols = self::arrayFlatten( $args );
if ( count( $cols ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$this->selectString = "SELECT " . join( ', ', $cols );
return $this;
}
/**
* Returns SQL to create an alias
*
* This method can be used to create an alias for either a
* table or a column.
* Example:
*
* // this will make the table users have the alias employees
* // and the column user_id the alias employee_id
* $q->select( $q->aliAs( 'user_id', 'employee_id' )
* ->from( $q->aliAs( 'users', 'employees' ) );
*
*
* @returns string the query string "columnname as targetname
* @todo rename?
*/
public function aliAs( $name, $alias )
{
return "{$name} AS {$alias}";
}
/**
* Select which tables you want to select from.
*
* from() accepts an arbitrary number of parameters. Each parameter
* must contain either the name of a table or an array containing
* the names of tables..
*
* Example:
*
* // the following code will produce the SQL
* // SELECT id FROM table_name
* $q->select( 'id' )->from( 'table_name' );
*
*
* @throws ezcDbAbstractionException if called more than once.
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string) Either a string with a table name or an array of table names.
* @returns a pointer to $this
*/
public function from()
{
if ( $this->fromString != '' )
{
throw new ezcDbAbstractionException( ezcDbAbstractionException::INVALID_CALL,
'Invalid second call to from(). from() can be called only once.' );
}
$args = func_get_args();
$tables = self::arrayFlatten( $args );
if ( count( $tables ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$this->fromString = "FROM " . join( ', ', $tables );
return $this;
}
/**
* Returns the SQL for an inner join.
*
* Example:
*
* // the following code will produce the SQL
* // SELECT id FROM t1 INNER JOIN t2 ON t1.id = t2.id
* $q->select( 'id' )->from( $q->innerJoin( 't1', 't2', 't1.id', 't2.id' ) );
*
*
* @param string $table1 the name of the table to join with
* @param string $table2 the name of the table to join
* @param string $column1 the column to join with
* @param string $column2 the column to join on
* @returns string the SQL call for an inner join.
*/
public function innerJoin( $table1, $table2, $column1, $column2 )
{
return "{$table1} INNER JOIN {$table2} ON {$column1} = {$column2}";
}
/**
* Returns the SQL for a left join.
*
* Example:
*
* // the following code will produce the SQL
* // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
* $q->select( 'id' )->from( $q->leftJoin( 't1', 't2', 't1.id', 't2.id' ) );
*
*
* @param string $table1 the name of the table to join with
* @param string $table2 the name of the table to join
* @param string $column1 the column to join with
* @param string $column2 the column to join on
* @returns string the SQL call for a left join.
*/
public function leftJoin( $table1, $table2, $column1, $column2 )
{
return "{$table1} LEFT JOIN {$table2} ON {$column1} = {$column2}";
}
/**
* Returns the SQL for a right join.
*
* Example:
*
* // the following code will produce the SQL
* // SELECT id FROM t1 RIGHT JOIN t2 ON t1.id = t2.id
* $q->select( 'id' )->from( $q->rightJoin( 't1', 't2', 't1.id', 't2.id' ) );
*
*
* @param string $table1 the name of the table to join with
* @param string $table2 the name of the table to join
* @param string $column1 the column to join with
* @param string $column2 the column to join on
* @returns string the SQL call for a right join.
*/
public function rightJoin( $table1, $table2, $column1, $column2 )
{
return "{$table1} RIGHT JOIN {$table2} ON {$column1} = {$column2}";
}
// logic
/**
* Adds a where clause with logical expressions to the query.
*
* where() accepts an arbitrary number of parameters. Each parameter
* must contain a logical expression or an array with logical expressions.
* If you specify multiple logical expression they are connected using
* a logical and.
*
* Example:
*
* $q->select( '*' )->from( 'table' )->where( $q->eq( 'id', 1 ) );
*
*
* @throws ezcDbAbstractionException if called more than once.
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string) Either a string with a logical expression name
* or an array with logical expressions.
* @returns a pointer to $this
*/
public function where()
{
if ( $this->whereString != '' )
{
throw new ezcDbAbstractionException( ezcDbAbstractionException::INVALID_CALL,
'Invalid second call to where(). where() can be called only once.' );
}
$args = func_get_args();
$expressions = self::arrayFlatten( $args );
if ( count( $expressions ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$this->whereString = 'WHERE ' . join( ' AND ', $expressions );
return $this;
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->lOr( $q->eq( 'id', 1 ),
* $q->eq( 'id', 2 ) ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @returns string a logical expression
*/
public function lOr()
{
$args = func_get_args();
if ( count( $args ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$elements = self::arrayFlatten( $args );
if ( count( $elements ) == 1 )
{
return $elements[0];
}
else
{
return '( ' . join( ' OR ', $elements ) . ' )';
}
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->lAnd( $q->eq( 'id', 1 ),
* $q->eq( 'id', 2 ) ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @returns string a logical expression
*/
public function lAnd()
{
$args = func_get_args();
if ( count( $args ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$elements = self::arrayFlatten( $args );
if ( count( $elements ) == 1 )
{
return $elements[0];
}
else
{
return '( ' . join( ' AND ', $elements ) . ' )';
}
}
/**
* Returns the SQL for a logical not.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->eq( 'id', $q->not( 'null' ) ) );
*
*
* @returns string a logical expression
*/
public function not( $expression )
{
return "NOT ( {$expression} )";
}
// math
/**
* Returns the SQL to perform the same mathematical operation over an array
* of values or expressions.
*
* basicMath() accepts an arbitrary number of parameters. Each parameter
* must contain a value or an expression or an array with values or
* expressions.
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string $type the type of operation, can be '+', '-', '*' or '/'.
* @param string|array(string)
* @returns string an expression
*/
private function basicMath( $type )
{
$args = func_get_args();
$elements = self::arrayFlatten( array_slice( $args, 1 ) );
if ( count( $elements ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
if ( count( $elements ) == 1 )
{
return $elements[0];
}
else
{
return '( ' . join( " $type ", $elements ) . ' )';
}
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->add( 'id', 2 ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string)
* @returns string an expression
*/
public function add()
{
$args = func_get_args();
return $this->basicMath( '+', $args );
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->subtract( 'id', 2 ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string)
* @returns string an expression
*/
public function subtract()
{
$args = func_get_args();
return $this->basicMath( '-', $args );
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->multiply( 'id', 2 ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string)
* @returns string an expression
*/
public function multiply()
{
$args = func_get_args();
return $this->basicMath( '*', $args );
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->divide( 'id', 2 ) );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string|array(string)
* @returns string an expression
*/
public function divide()
{
$args = func_get_args();
return $this->basicMath( '/', $args );
}
/**
* Returns the SQL to check if two values are equal.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->eq( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function eq( $value1, $value2 )
{
return "{$value1} = {$value2}";
}
/**
* Returns the SQL to check if two values are unequal.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->neq( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function neq( $value1, $value2 )
{
return "{$value1} <> {$value2}";
}
/**
* Returns the SQL to check if one value is greater than another value.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->gt( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function gt( $value1, $value2 )
{
return "{$value1} > {$value2}";
}
/**
* Returns the SQL to check if one value is greater than or equal to
* another value.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->gte( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function gte( $value1, $value2 )
{
return "{$value1} >= {$value2}";
}
/**
* Returns the SQL to check if one value is less than another value.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->lt( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function lt( $value1, $value2 )
{
return "{$value1} < {$value2}";
}
/**
* Returns the SQL to check if one value is less than or equal to
* another value.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->lte( 'id', 1 ) );
*
*
* @param string $value1 logical expression to compare
* @param string $value2 logical expression to compare with
* @returns string logical expression
*/
public function lte( $value1, $value2 )
{
return "{$value1} <= {$value2}";
}
/**
* 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:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->in( 'id', 1, 2, 3 ) );
*
*
* @throws ezcDbAbstractionException if called with less than two parameters..
* @param string $column the value that should be matched against
* @param string|array(string) values that will be matched against $column
* @returns string logical expression
*/
public function in( $value )
{
$args = func_get_args();
if ( count( $args ) < 2 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$values = self::arrayFlatten( array_slice( $args, 1 ) );
// todo, throw if not at least one value
return "{$value} IN ( " . join( ', ', $values ) . ' )';
}
/**
* Returns SQL that checks if a expression is null.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->where( $q->isNull( 'id') );
*
*
* @param string $expression the expression that should be compared to null
* @returns string logical expression
*/
public function isNull( $expression )
{
return "{$expression} IS NULL";
}
/**
* 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->select( '*' )->from( 'table' )
* ->where( $q->between( 'id' , 1, 5 ) );
*
*
* @param string $expression the value to compare to
* @param string $value1 the lower value to compare with
* @param string $value2 the higher value to compare with
* @returns string logical expression
*/
public function between( $expression, $value1, $value2 )
{
return "{$expression} BETWEEN {$value1} AND {$value2}";
}
/**
* 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.
*
* @param string $column the name of the column to match on
* @param string $pattern the pattern to match with.
*/
public function like( $column, $pattern )
{
return "{$column} LIKE {$pattern}";
}
// limit, order and group
/**
* Returns SQL that limits the result set.
*
* $limit controls the maximum number of rows that will be returned.
* $offset controls which row that will be the first in the result
* set from the total amount of matching rows.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->limit( 10, 0 );
*
*
* LIMIT is not part of SQL92. It is implemented here anyway since all
* databases support it one way or the other and because it is
* essential.
*
* @param $limit integer expression
* @param $offset integer expression
* @returns string logical expression
*/
public function limit( $limit, $offset = 0 )
{
if ( $offset == 0 )
{
$this->limitString = "LIMIT {$limit}";
}
else
{
$this->limitString = "LIMIT {$limit}, {$offset}";
}
return $this;
}
/**
* Returns SQL that orders the result set by a given column.
*
* You can call orderBy multiple times. Each call will add a
* column to order by.
*
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->orderBy( 'id' );
*
*
* @param string $column a column name in the result set
* @param string $type if the column should be sorted ascending or descending.
* you can specify this using ezcQuery::ASC or ezcQuery::DESC
* @returns ezcQuery a pointer to $this
*/
public function orderBy( $column, $type = self::ASC )
{
$string = $column;
if ( $type == self::DESC )
{
$string .= ' DESC';
}
if ( $this->orderString == '' )
{
$this->orderString = "ORDER BY {$string}";
}
else
{
$this->orderString .= ", {$string}";
}
return $this;
}
/**
* Returns SQL that groups the result set by a given column.
*
* You can call groupBy multiple times. Each call will add a
* column to group by.
* Example:
*
* $q->select( '*' )->from( 'table' )
* ->groupBy( 'id' );
*
*
* @throws ezcDbAbstractionException if called with no parameters.
* @param string $column a column name in the result set
* @returns ezcQuery a pointer to $this
*/
public function groupBy()
{
$args = func_get_args();
$columns = self::arrayFlatten( $args );
if ( count( $columns ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
$string = join( ', ', $columns );
if ( $this->groupString == '' )
{
$this->groupString = "GROUP BY {$string}" ;
}
else
{
$this->groupString .= ", {$string}";
}
return $this;
}
// aggregate functions
/**
* Returns the average value of a column
*
* @param string $column the column to use
* @returns string
*/
public function avg( $column )
{
return "AVG( {$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.
*
* @param string $column the column to use
* @returns string
*/
public function count( $column )
{
return "COUNT( {$column} )";
}
/**
* Returns the highest value of a column
*
* @param string $column the column to use
* @returns string
*/
public function max( $column )
{
return "MAX( {$column} )";
}
/**
* Returns the lowest value of a column
*
* @param string $column the column to use
* @returns string
*/
public function min( $column )
{
return "MIN( {$column} )";
}
/**
* Returns the total sum of a column
*
* @param string $column the column to use
* @returns string
*/
public function sum( $column )
{
return "SUM( {$column} )";
}
// scalar functions
/**
* Returns the md5 sum of a field.
*
* Note: Not SQL92, but common functionality
*
* @return string
*/
public function md5( $column )
{
return "MD5( {$column} )";
}
/**
* Returns the length of a text field.
*
* @param string $expression1
* @param string $expression2
* @returns string
*/
public function length( $column )
{
return "LENGTH( {$column} )";
}
/**
* Rounds a numeric field to the number of decimals specified.
*
* @param string $expression1
* @param string $expression2
* @returns string
*/
public function round( $column, $decimals )
{
return "ROUND( {$column}, {$decimals} )";
}
/**
* Returns the remainder of the division operation
* $expression1 / $expression2.
*
* @param string $expression1
* @param string $expression2
* @returns string
*/
public function mod( $expression1, $expression2 )
{
return "MOD( {$expression1}, {$expression2} )";
}
/**
* Returns the current system date.
*
* @returns string
*/
public function now()
{
return "NOW()";
}
// string functions
/**
* Returns part of a string.
*
* Note: Not SQL92, but common functionality.
*
* @param string $value the target $value the string or the string column.
* @param integer $from extract from this characeter.
* @param integer $len extract this amount of characters.
* @return string sql that extracts part of a string.
*/
public function subString( $value, $from, $len = null )
{
if ( $len === null )
{
return "substring( {$value} from {$from} )";
}
else
{
return "substring( {$value} from {$from} for {$len} )";
}
}
/**
* Returns a series of strings concatinated
*
* concat() accepts an arbitrary number of parameters. Each parameter
* must contain an expression or an array with expressions.
*
* @param string|array(string) strings that will be concatinated.
*/
public function concat()
{
$args = func_get_args();
$cols = self::arrayFlatten( $args );
if ( count( $cols ) < 1 )
{
throw new ezcDbAbstractionException( ezcDbAbstractoinException::INVALID_ARGUMENT_NUM );
}
return "CONCAT( " . join( ', ', $cols ) . ' )';
}
/**
* Returns dummy table name.
*
* If your select query just evaluates an expression
* without fetching table data (e.g. 'SELECT 1+1')
* some databases require you to specify a dummy table in FROM clause.
* (Oracle: 'SELECT 1+1 FROM dual').
*
* This methods returns name of such a dummy table.
* For DBMSs that don't require that, the method returns false.
* Otherwise the dummy table name is returned.
*
* @returns boolean|string a dummy table name or false if not needed
*/
static public function getDummyTableName()
{
return false;
}
// generate
// @todo fix exception
// @todo prepare for implementation of insert into etc.
public function build()
{
if ( $this->selectString == '' )
{
throw new exception();
}
$query = "{$this->selectString}";
if( $this->fromString != '' )
{
$query = "{$query} {$this->fromString}";
}
if ( $this->whereString != '' )
{
$query = "{$query} {$this->whereString}";
}
if ( $this->groupString != '' )
{
$query = "{$query} {$this->groupString}";
}
if ( $this->orderString != '' )
{
$query = "{$query} {$this->orderString}";
}
if ( $this->limitString != '' )
{
$query = "{$query} {$this->limitString}";
}
return $query;
}
public function buildSelect()
{
return $this->selectString;
}
public function buildFrom()
{
return $this->fromString;
}
public function buildWhere()
{
return $this->whereString;
}
public function buildOrder()
{
return $this->orderString;
}
public function buildGroup()
{
return $this->groupString;
}
public function buildLimit()
{
return $this->limitString;
}
/**
* Returns all the elements in $array as one large single dimensional array.
*
* @returns array
*/
static protected function arrayFlatten( array $array )
{
$flat = array();
foreach( $array as $arg )
{
switch ( gettype( $arg ) )
{
case 'array':
$flat = array_merge( $flat, $arg );
break;
default:
$flat[] = $arg;
break;
}
}
return $flat;
}
}
?>