## Apache 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