Apache Zeta Components - high quality PHP components

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.

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.

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

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' ) );

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.

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' );

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

You can call groupBy multiple times. Each call will add a column to group by.

Example:

1.  $q->select( '*' )->from( 'table' )
2.                   ->groupBy( 'id' );

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) );

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' );

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' );

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

You can call orderBy multiple times. Each call will add a column to order by.

Example:

1.  $q->select( '*' )->from( 'table' )
2.                   ->orderBy( 'id' );

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' );

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' );

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

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

Each of above code produce SQL clause 'SELECT column1, column2' for 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' );

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

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.

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 ) );