eZ Components 1.0.1

Getting Started

Components

Archive (1.0.1)
Base (1.0)
Cache (1.0.1)
Configuration (1.0.1)
ConsoleTools (1.0)
Database (1.0.1)
Debug (1.0)
EventLog (1.0)
EventLogDatabaseTiein (1.0)
Execution (1.0)
ImageAnalysis (1.0.1)
ImageConversion (1.0.1)
Mail (1.0.1)
PersistentObject (1.0.1)
PhpGenerator (1.0)
Translation (1.0)
TranslationCacheTiein (1.0)
UserInput (1.0)

Database: ezcQuerySelect

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

Class: ezcQuerySelect

Class to create select database independent SELECT queries. [source]

Note that this class creates queries that are syntactically independant 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. Postgres does not, but accepts TRUE/FALSE.

Parents

ezcQuery
   |
   --ezcQuerySelect

Descendents

Child Class	Description
ezcQuerySelectOracle	Oracle specific implementation of ezcQuery.
ezcQuerySelectSqlite	SQLite specific implementation of ezcQuery.

Constants

`ASC = 'ASC'`	Sort the result ascendingly.
`DESC = 'DESC'`	Sort the result descendingly.

Member Variables

protected string	`$fromString = null` Stores the FROM part of the SQL.
protected string	`$groupString = null` Stores the GROUP BY part of the SQL.
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.
protected string	`$whereString = null` Stores the WHERE part of the SQL.

Inherited Member Variables

From ezcQuery:

protected	`ezcQuery::$db`
public	`ezcQuery::$expr`

Method Summary

public ezcQuerySelect	`__construct( $db, [$aliases = array()] )` Constructs a new ezcQuery object.
public string	`alias( $name, $alias )` Returns SQL to create an alias
public a	`from( 0 )` Select which tables you want to select from.
public static bool\|string	`getDummyTableName( )` Returns dummy table name.
public string	`getQuery( )` Returns the complete select query.
public ezcQuery	`groupBy( $column )` Returns SQL that groups the result set by a given column.
public string	`innerJoin( $table1, $table2, $column1, $column2 )` Returns the SQL for an inner join.
public string	`leftJoin( $table1, $table2, $column1, $column2 )` Returns the SQL for a left join.
public string	`limit( $limit, [$offset = 0] )` 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 string	`rightJoin( $table1, $table2, $column1, $column2 )` Returns the SQL for a right join.
public ezcQuery	`select( 0 )` Opens the query and selects which columns you want to return with the query.
public ezcQuerySelect	`where( 0 )` 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 &mixed	`ezcQuery::bindParam()` Binds the parameter $param to the specified variable name $placeHolder..
public &mixed	`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.
public void	`ezcQuery::setAliases()` Sets the aliases $aliases for this object.

Methods

__construct

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

Constructs a new ezcQuery object.

Parameters

Name	Type	Description
`$db`	PDO	a pointer to the database object.
`$aliases`	array

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
`ezcQuerySelectOracle::__construct()`	Constructs a new ezcQueryOracle object.
`ezcQuerySelectSqlite::__construct()`	Constructs a new ezcQueryOracle object.

alias

string alias( $name, $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`
`$alias`

Redefined in descendants as

Method	Description
`ezcQuerySelectOracle::alias()`	Returns SQL to create an alias.

from

a from( string|array(string) 0 )

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:

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
`0`	string\|array(string)	Either a string with a table name or an array of table names.

Throws

Class	Description
`ezcQueryException`	if called more than once.
`ezcQueryVariableParameterException`	if called with no parameters.

getDummyTableName

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'.

getQuery

string getQuery( )

Returns the complete select query.

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

Throws

Class	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
`ezcQuerySelectOracle::getQuery()`	Transforms the query from the parent to provide LIMIT functionality.

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

Throws

Class	Description
`ezcQueryVariableParameterException`	if called with no parameters.

innerJoin

string innerJoin( string $table1, string $table2, string $column1, string $column2 )

Returns the SQL for an inner join.

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( $q->innerJoin( 't1', 't2', 't1.id', 't2.id' ) );

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join
`$column1`	string	the column to join with
`$column2`	string	the column to join on

leftJoin

string leftJoin( string $table1, string $table2, string $column1, string $column2 )

Returns the SQL for a left join.

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( $q->leftJoin( 't1', 't2', 't1.id', 't2.id' ) );

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join
`$column1`	string	the column to join with
`$column2`	string	the column to join on

limit

string limit( mixed $limit, [mixed $offset = 0] )

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.

Redefined in descendants as

Method	Description
`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 ezcQuery::ASC or ezcQuery::DESC

reset

void reset( )

Resets the query object for reuse.

Redefined in descendants as

Method	Description
`ezcQuerySelectOracle::reset()`	Resets the query object for reuse.

rightJoin

string rightJoin( string $table1, string $table2, string $column1, string $column2 )

Returns the SQL for a right join.

Example:

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

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join
`$column1`	string	the column to join with
`$column2`	string	the column to join on

Redefined in descendants as

Method	Description
`ezcQuerySelectSqlite::rightJoin()`	Returns the SQL for a right join.

select

ezcQuery select( string|array(string) 0 )

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.

Example:

1.  $q->select( 'column1', 'column2' );

The same could also be written

1.  $columns[] = 'column1';
2.  $columns[] = 'column2;
3.  $q->select( $columns );

Parameters

Name	Type	Description
`0`	string\|array(string)	Either a string with a column name or an array of column names.

Throws

Class	Description
`ezcQueryException`	if called more than once.
`ezcQueryVariableParameterException`	if called with no parameters..

where

ezcQuerySelect where( string|array(string) 0 )

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:

1.  $q->select( '*' )->from( 'table' )->where( $q->expr->eq( 'id', 1 ) );

Parameters

Name	Type	Description
`0`	string\|array(string)	Either a string with a logical expression name or an array with logical expressions.

Throws

Class	Description
`ezcQueryException`	if called more than once.
`ezcQueryVariableParameterException`	if called with no parameters.

Last updated: Fri, 02 Nov 2007

eZ Components

User menu

Path

eZ Components 1.0.1

Getting Started

Components

Database: ezcQuerySelect

Class: ezcQuerySelect

Parents

Descendents

Constants

Member Variables

Inherited Member Variables

Method Summary

Inherited Methods

Methods

__construct

Parameters

Redefinition of

Redefined in descendants as

alias

Parameters

Redefined in descendants as

from

Parameters

Throws

getDummyTableName

Redefined in descendants as

getQuery

Throws

Redefinition of

Redefined in descendants as

groupBy

Parameters

Throws

innerJoin

Parameters

leftJoin

Parameters

limit

Redefined in descendants as

orderBy

Parameters

reset

Redefined in descendants as

rightJoin

Parameters

Redefined in descendants as

select

Parameters

Throws

where

Parameters

Throws