eZ Components 1.1

Getting Started

Components

Archive (1.1)
Base (1.1)
Cache (1.1)
Configuration (1.0.2)
ConsoleTools (1.1)
Database (1.1)
DatabaseSchema (1.0)
Debug (1.0)
EventLog (1.0.1)
EventLogDatabaseTiein (1.0)
Execution (1.0)
File (1.0)
ImageAnalysis (1.0.1)
ImageConversion (1.1)
Mail (1.1)
PersistentObject (1.1)
PersistentObjectDatabaseSchemaTiein (1.0)
PhpGenerator (1.0)
SystemInformation (1.0)
Template (1.0)
Translation (1.1)
TranslationCacheTiein (1.1)
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
ezcQuerySubSelect	Class to create subselects within queries.
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	`$lastInvokedMethod = null` Stores the name of last invoked SQL clause method.
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 string	`alias( $name, $alias )` Returns SQL to create an alias
public ezcQuerySelect	`__construct( $db, [$aliases = array()] )` Constructs a new ezcQuery object.
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 string.
public ezcQuery	`groupBy( $column )` Returns SQL that groups the result set by a given column.
public string	`innerJoin( $table1, $table2, $column1, $column2, $condition )` Returns the SQL for a inner join or prepares $fromString for a inner join.
public string	`leftJoin( $table1, $table2, $column1, $column2, $condition )` Returns the SQL for a left join or prepares $fromString 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, $condition )` Returns the SQL for a right join or prepares $fromString for a right join.
public ezcQuery	`select( 0 )` Opens the query and selects which columns you want to return with the query.
public ezcQuerySubSelect	`subSelect( )` Returns the ezcQuerySubSelect query object.
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 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.
public void	`ezcQuery::setAliases()` Sets the aliases $aliases for this object.

Methods

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.

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

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.. from() could be invoked several times. All provided arguments added to the end of $fromString.

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
`ezcQueryVariableParameterException`	if called with no parameters.

Redefined in descendants as

Method	Description
`ezcQuerySelectSqlite::from()`	Select which tables you want to select from.

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

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
`ezcQuerySubSelect::getQuery()`	Return string with SQL query for subselect.
`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, string $condition )

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

This method could be used in three forms:

innerJoin( 't1', 't2', 't1.id', 't2.id' ) takes 4 string arguments and return SQL string

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join. The name of table to join with should be set in previous call to from().
`$column1`	string	the column to join with
`$column2`	string	the column to join on
`$condition`	string	the string with join condition returned by ezcQueryExpression.

leftJoin

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

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

This method could be used in three forms:

leftJoin( 't1', 't2', 't1.id', 't2.id' ) takes 4 string arguments and return SQL string

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join. The name of table to join with should be set in previous call to from().
`$column1`	string	the column to join with
`$column2`	string	the column to join on
`$condition`	string	the string with join condition returned by ezcQueryExpression.

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.
`ezcQuerySelectSqlite::reset()`	Resets the query object for reuse.

rightJoin

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

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

This method could be used in three forms:

rightJoin( 't1', 't2', 't1.id', 't2.id' ) takes 4 string arguments and return SQL string

Parameters

Name	Type	Description
`$table1`	string	the name of the table to join with
`$table2`	string	the name of the table to join. The name of table to join with should be set in previous call to from().
`$column1`	string	the column to join with
`$column2`	string	the column to join on
`$condition`	string	the string with join condition returned by ezcQueryExpression.

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) 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. select() could be invoked several times. All provided arguments added to the end of $selectString and form final SELECT clause.

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

Throws

Class	Description
`ezcQueryVariableParameterException`	if called with no parameters..

subSelect

ezcQuerySubSelect subSelect( )

Returns the ezcQuerySubSelect query object.

This method creates new ezcQuerySubSelect object that could be used for building correct subselect inside query.

Redefined in descendants as

Method	Description
`ezcQuerySubSelect::subSelect()`	Returns ezcQuerySubSelect of deeper level.

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. where() could be invoked several times. All provided arguments added to the end of $whereString and form final WHERE clause of the query.

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
`ezcQueryVariableParameterException`	if called with no parameters.

Last updated: Wed, 28 Nov 2007

eZ Components

User menu

Path

eZ Components 1.1

Getting Started

Components

Database: ezcQuerySelect

Class: ezcQuerySelect

Parents

Descendents

Constants

Member Variables

Inherited Member Variables

Method Summary

Inherited Methods

Methods

alias

Parameters

Redefined in descendants as

__construct

Parameters

Redefinition of

Redefined in descendants as

from

Parameters

Throws

Redefined in descendants as

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

subSelect

Redefined in descendants as

where

Parameters

Throws