Zeta Components - high quality PHP components

Zeta Components Manual :: Docs For Class ezcQuerySelect

Database::ezcQuerySelect

Class ezcQuerySelect

Class to create select database independent SELECT queries.

Note that this class creates queries that are syntactically independent of database. Semantically the queries still differ and so the same query may produce different results on different databases. Such differences are noted throughout the documentation of this class.

This class implements SQL92. If your database differs from the SQL92 implementation extend this class and reimplement the methods that produce different results. Some methods implemented in ezcQuery are not defined by SQL92. These methods are marked and ezcQuery will return MySQL syntax for these cases.

The examples show the SQL generated by this class. Database specific implementations may produce different results.

Example:

  1.  $q = ezcDbInstance::get()->createSelectQuery();
  2.  $q->select( '*' )->from( 'Greetings' )
  3.      ->where( $q->expr->gt( 'age', 10 ),
  4.               $q->expr->eq( 'greeting', $q->bindValue( 'Hello world' ) ) )
  5.      ->orderBy( 'owner' )
  6.      ->limit( 10 );
  7.  $stmt = $q->prepare(); // $stmt is a normal PDOStatement
  8.  $stmt->execute();

Database independence: TRUE/FALSE, MySQL accepts 0 and 1 as boolean values. PostgreSQL does not, but accepts TRUE/FALSE.

Source for this file: /Database/src/sqlabstraction/query_select.php

ezcQuery
   |
   --ezcQuerySelect
Version:   //autogentag//
Todo:   introduction needs examples with clone(), reusing a query and advanced binding.

Descendants

Child Class Description
ezcQuerySelectSqlite SQLite specific implementation of ezcQuery.
ezcQuerySelectMssql SQL Server specific implementation of ezcQuery.
ezcQuerySelectOracle Oracle specific implementation of ezcQuery.
ezcQuerySubSelect This class is used to contain subselects

Constants

ASC = 'ASC' Sort the result ascending.
DESC = 'DESC' Sort the result descending.

Member Variables

protected string $fromString = null
Stores the FROM part of the SQL.

Everything from 'FROM' until 'WHERE' is stored.

protected string $groupString = null
Stores the GROUP BY part of the SQL.
protected string $havingString = null
Stores the HAVING part of SQL
protected string $lastInvokedMethod = null
Stores the name of last invoked SQL clause method.

Could be 'select', 'from', 'where', 'group', 'having', 'order', 'limit'

protected string $limitString = null
Stores the LIMIT part of the SQL.
protected string $orderString = null
Stores the ORDER BY part of the SQL.
protected string $selectString = null
Stores the SELECT part of the SQL.

Everything from 'SELECT' until 'FROM' is stored.

protected string $whereString = null
Stores the WHERE part of the SQL.

Everything from 'WHERE' until 'GROUP', 'LIMIT', 'ORDER' or 'SORT' is stored.

Inherited Member Variables

From ezcQuery
protected ezcQuery::$db
public ezcQuery::$expr

Method Summary

public static bool|string getDummyTableName( )
Returns dummy table name.
public ezcQuerySelect __construct( $db , [ $aliases = array()] )
Constructs a new ezcQuery object.
public string alias( $name , $alias )
Returns SQL to create an alias
protected ezcQuery doJoin( $type , $table2,... )
Returns the SQL for a join or prepares $fromString for a join.
public ezcQuery from( $... )
Select which tables you want to select from.
public string getQuery( )
Returns the complete select query string.
public ezcQuery groupBy( $column )
Returns SQL that groups the result set by a given column.
public ezcQuery having( $... )
Returns SQL that set having by a given expression.
public ezcQuery innerJoin( $table2,... )
Returns the SQL for an inner join or prepares $fromString for an inner join.
public ezcQuery leftJoin( $table2,... )
Returns the SQL for a left join or prepares $fromString for a left join.
public ezcQuerySelect limit( $limit , [ $offset = ''] )
Returns SQL that limits the result set.
public ezcQuery orderBy( $column , [ $type = self::ASC] )
Returns SQL that orders the result set by a given column.
public void reset( )
Resets the query object for reuse.
public ezcQuery rightJoin( $table2,... )
Returns the SQL for a right join or prepares $fromString for a right join.
public ezcQuery select( $... )
Opens the query and selects which columns you want to return with the query.
public ezcQuery selectDistinct( $... )
Opens the query and uses a distinct select on the columns you want to return with the query.
public ezcQuerySelect where( $... )
Adds a where clause with logical expressions to the query.

Inherited Methods

From ezcQuery
public ezcQuery ezcQuery::__construct()
Constructs a new ezcQuery that works on the database $db and with the aliases $aliases.
public static array ezcQuery::arrayFlatten()
Returns all the elements in $array as one large single dimensional array.
public string ezcQuery::bindParam()
Binds the parameter $param to the specified variable name $placeHolder..
public string ezcQuery::bindValue()
Binds the value $value to the specified variable name $placeHolder.
public void ezcQuery::doBind()
Performs binding of variables bound with bindValue and bindParam on the statement $stmt.
protected string ezcQuery::getIdentifier()
Returns the correct identifier for the alias $alias.
protected array(string) ezcQuery::getIdentifiers()
Returns the correct identifiers for the aliases found in $aliases.
public abstract string ezcQuery::getQuery()
Returns the query string for this query object.
public bool ezcQuery::hasAliases()
Returns true if this object has aliases.
public PDOStatement ezcQuery::prepare()
Returns a prepared statement from this query which can be used for execution.
protected void ezcQuery::resetBinds()
Resets the bound values and parameters to empty.
public void ezcQuery::setAliases()
Sets the aliases $aliases for this object.
public ezcQuerySubSelect ezcQuery::subSelect()
Returns the ezcQuerySubSelect query object.
public string ezcQuery::__toString()
Return SQL string for query.

Methods

getDummyTableName

static bool|string getDummyTableName( )

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.

Redefined in descendants as:
Method Description
ezcQuerySelectOracle::getDummyTableName() Returns dummy table name 'dual'. 

__construct

ezcQuerySelect __construct( $db , [ $aliases = array()] )

Constructs a new ezcQuery object.

For an introduction to aliases see ezcQuery::__construct().

Parameters:
Name Type Description
$db PDO a pointer to the database object.
$aliases array(string=>string)
Redefinition of:
Method Description
ezcQuery::__construct() Constructs a new ezcQuery that works on the database $db and with the aliases $aliases.
Redefined in descendants as:
Method Description
ezcQuerySelectSqlite::__construct() Constructs a new ezcQuerySelectSqlite object. 
ezcQuerySelectOracle::__construct() Constructs a new ezcQueryOracle object working on the database $db. 
ezcQuerySubSelect::__construct() Constructs a new ezcQuerySubSelect object. 

alias

string alias( string $name , string $alias )

Returns SQL to create an alias

This method can be used to create an alias for either a table or a column. Example:

  1.  // this will make the table users have the alias employees
  2.  // and the column user_id the alias employee_id
  3.  $q->select( $q->alias( 'user_id', 'employee_id' )
  4.    ->from( $q->alias( 'users', 'employees' ) );
Parameters:
Name Type Description
$name string
$alias string
Redefined in descendants as:
Method Description
ezcQuerySelectOracle::alias() Returns SQL to create an alias. 

doJoin

ezcQuery doJoin( string $type , string $table2,... )

Returns the SQL for a join or prepares $fromString for a join.

This method could be used in two forms:

doJoin( $joinType, 't2', $joinCondition )

Takes the join type and two string arguments and returns ezcQuery.

The second parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The third parameter should be a string containing a join condition that is returned by an ezcQueryExpression.

doJoin( $joinType, 't2', 't1.id', 't2.id' )

Takes the join type and three string arguments and returns ezcQuery. This is a simplified form of the three parameter version. doJoin( 'inner', 't2', 't1.id', 't2.id' ) is equal to doJoin( 'inner', 't2', $this->expr->eq('t1.id', 't2.id' ) );

The second parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The third parameter is the name of the column on the table set previously with the from() method and the fourth parameter the name of the column to join with on the table that was specified in the first parameter.

Parameters:
Name Type Description
$type string The join type: inner, right or left.
$table2,... string The table to join with, followed by either the two join columns, or a join condition.
Exceptions:
Type Description
ezcQueryInvalidException if called with inconsistent parameters or if invoked without preceding call to from().

from

ezcQuery from( string|array(string) $... )

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.. Each call to from() appends tables to the list of tables that will be used in the query.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM table_name
  3.  $q->select( 'id' )->from( 'table_name' );
Parameters:
Name Type Description
$... string|array(string) Either a string with a table name or an array of table names.
Exceptions:
Type Description
ezcQueryVariableParameterException if called with no parameters.
Redefined in descendants as:
Method Description
ezcQuerySelectSqlite::from() Select which tables you want to select from. 

getQuery

string getQuery( )

Returns the complete select query string.

This method uses the build methods to build the various parts of the select query.

Exceptions:
Type Description
ezcQueryInvalidException if it was not possible to build a valid query.
Redefinition of:
Method Description
ezcQuery::getQuery() Returns the query string for this query object.
Redefined in descendants as:
Method Description
ezcQuerySelectMssql::getQuery() Transforms the query from the parent to provide LIMIT functionality. 
ezcQuerySelectOracle::getQuery() Transforms the query from the parent to provide LIMIT functionality. 
ezcQuerySubSelect::getQuery() Returns the SQL string for the subselect. 

groupBy

ezcQuery groupBy( string $column )

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:

  1.  $q->select( '*' )->from( 'table' )
  2.                   ->groupBy( 'id' );
Parameters:
Name Type Description
$column string a column name in the result set
Exceptions:
Type Description
ezcQueryVariableParameterException if called with no parameters.

having

ezcQuery having( string|array(string) $... )

Returns SQL that set having by a given expression.

You can call having multiple times. Each call will add an expression with a logical and.

Example:

  1.  $q->select( '*' )->from( 'table' )->groupBy( 'id' )
  2.                   ->having( $q->expr->eq('id',1) );
Parameters:
Name Type Description
$... string|array(string) Either a string with a logical expression name or an array with logical expressions.
Exceptions:
Type Description
ezcQueryVariableParameterException if called with no parameters.
ezcQueryInvalidException if invoked without preceding call to groupBy().

innerJoin

ezcQuery innerJoin( string $table2,... )

Returns the SQL for an inner join or prepares $fromString for an inner join.

This method could be used in two forms:

innerJoin( 't2', $joinCondition )

Takes 2 string arguments and returns ezcQuery.

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter should be a string containing a join condition that is returned by an ezcQueryExpression.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 INNER JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->innerJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );

innerJoin( 't2', 't1.id', 't2.id' )

Takes 3 string arguments and returns ezcQuery. This is a simplified form of the 2 parameter version. innerJoin( 't2', 't1.id', 't2.id' ) is equal to innerJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter is the name of the column on the table set previously with the from() method and the third parameter the name of the column to join with on the table that was specified in the first parameter.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 INNER JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->innerJoin( 't2', 't1.id', 't2.id' );
Parameters:
Name Type Description
$table2,... string The table to join with, followed by either the two join columns, or a join condition.
Exceptions:
Type Description
ezcQueryInvalidException if called with inconsistent parameters or if invoked without preceding call to from().

leftJoin

ezcQuery leftJoin( string $table2,... )

Returns the SQL for a left join or prepares $fromString for a left join.

This method could be used in two forms:

leftJoin( 't2', $joinCondition )

Takes 2 string arguments and returns ezcQuery.

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter should be a string containing a join condition that is returned by an ezcQueryExpression.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->leftJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );

leftJoin( 't2', 't1.id', 't2.id' )

Takes 3 string arguments and returns ezcQuery. This is a simplified form of the 2 parameter version. leftJoin( 't2', 't1.id', 't2.id' ) is equal to leftJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter is the name of the column on the table set previously with the from() method and the third parameter the name of the column to join with on the table that was specified in the first parameter.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->leftJoin( 't2', 't1.id', 't2.id' );
Parameters:
Name Type Description
$table2,... string The table to join with, followed by either the two join columns, or a join condition.
Exceptions:
Type Description
ezcQueryInvalidException if called with inconsistent parameters or if invoked without preceding call to from().

limit

ezcQuerySelect limit( string $limit , [string $offset = ''] )

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:

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

Parameters:
Name Type Description
$limit string integer expression
$offset string integer expression
Redefined in descendants as:
Method Description
ezcQuerySelectMssql::limit() Returns SQL that limits the result set. 
ezcQuerySelectOracle::limit() Returns SQL that limits the result set. 

orderBy

ezcQuery orderBy( string $column , [string $type = self::ASC] )

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:

  1.  $q->select( '*' )->from( 'table' )
  2.                   ->orderBy( 'id' );
Parameters:
Name Type Description
$column string a column name in the result set
$type string if the column should be sorted ascending or descending. you can specify this using ezcQuerySelect::ASC or ezcQuerySelect::DESC
Redefined in descendants as:
Method Description
ezcQuerySelectMssql::orderBy() Saves the ordered columns in an internal array so we can invert that order if we need to in the limit() workaround 

reset

void reset( )

Resets the query object for reuse.

Redefined in descendants as:
Method Description
ezcQuerySelectSqlite::reset() Resets the query object for reuse. 
ezcQuerySelectMssql::reset() Resets the query object for reuse. 
ezcQuerySelectOracle::reset() Resets the query object for reuse. 

rightJoin

ezcQuery rightJoin( string $table2,... )

Returns the SQL for a right join or prepares $fromString for a right join.

This method could be used in two forms:

rightJoin( 't2', $joinCondition )

Takes 2 string arguments and returns ezcQuery.

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter should be a string containing a join condition that is returned by an ezcQueryExpression.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->rightJoin( 't2', $q->expr->eq('t1.id', 't2.id' ) );

rightJoin( 't2', 't1.id', 't2.id' )

Takes 3 string arguments and returns ezcQuery. This is a simplified form of the 2 parameter version. rightJoin( 't2', 't1.id', 't2.id' ) is equal to rightJoin( 't2', $this->expr->eq('t1.id', 't2.id' ) );

The first parameter is the name of the table to join with. The table to which is joined should have been previously set with the from() method.

The second parameter is the name of the column on the table set previously with the from() method and the third parameter the name of the column to join with on the table that was specified in the first parameter.

Example:

  1.  // the following code will produce the SQL
  2.  // SELECT id FROM t1 LEFT JOIN t2 ON t1.id = t2.id
  3.  $q->select( 'id' )->from( 't1' )->rightJoin( 't2', 't1.id', 't2.id' );
Parameters:
Name Type Description
$table2,... string The table to join with, followed by either the two join columns, or a join condition.
Exceptions:
Type Description
ezcQueryInvalidException if called with inconsistent parameters or if invoked without preceding call to from().
Redefined in descendants as:
Method Description
ezcQuerySelectSqlite::rightJoin() Returns the SQL for a right join or prepares $fromString for a right join. 

select

ezcQuery select( string|array(string) $... )

Opens 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. Each call to select() appends columns to the list of columns that will be used in the query.

Example:

  1.  $q->select( 'column1', 'column2' );
The same could also be written
  1.  $columns[] = 'column1';
  2.  $columns[] = 'column2;
  3.  $q->select( $columns );
or using several calls
  1.  $q->select( 'column1' )->select( 'column2' );

Each of above code produce SQL clause 'SELECT column1, column2' for the query.

Parameters:
Name Type Description
$... string|array(string) Either a string with a column name or an array of column names.
Exceptions:
Type Description
ezcQueryVariableParameterException if called with no parameters..

selectDistinct

ezcQuery selectDistinct( string|array(string) $... )

Opens the query and uses a distinct select on the columns you want to return with the query.

selectDistinct() 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. Each call to selectDistinct() appends columns to the list of columns that will be used in the query.

Example:

  1.  $q->selectDistinct( 'column1', 'column2' );
The same could also be written
  1.  $columns[] = 'column1';
  2.  $columns[] = 'column2;
  3.  $q->selectDistinct( $columns );
or using several calls
  1.  $q->selectDistinct( 'column1' )->select( 'column2' );

Each of above code produce SQL clause 'SELECT DISTINCT column1, column2' for the query.

You may call select() after calling selectDistinct() which will result in the additional columns beein added. A call of selectDistinct() after select() will result in an ezcQueryInvalidException.

Parameters:
Name Type Description
$... string|array(string) Either a string with a column name or an array of column names.
Exceptions:
Type Description
ezcQueryInvalidException if called after select()
ezcQueryVariableParameterException if called with no parameters..

where

ezcQuerySelect where( string|array(string) $... )

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.

Multiple calls to where() will join the expressions using a logical and.

Example:

  1.  $q->select( '*' )->from( 'table' )->where( $q->expr->eq( 'id', 1 ) );
Parameters:
Name Type Description
$... string|array(string) Either a string with a logical expression name or an array with logical expressions.
Exceptions:
Type Description
ezcQueryVariableParameterException if called with no parameters.
Documentation generated by phpDocumentor 1.4.3