Documentation

Zend_Db_Select

Introduction

The Zend_Db_Select object represents a SQLSELECT query statement. The class has methods for adding individual
parts to the query. You can specify some parts of the query using PHP
methods and data structures, and the class forms the correct SQL
syntax for you. After you build a query, you can execute the query as if you had written
it as a string.

The value offered by Zend_Db_Select includes:

Object-oriented methods for specifying SQL queries in a
piece-by-piece manner;

Database-independent abstraction of some parts of the SQL
query;

Automatic quoting of metadata identifiers in most cases, to support identifiers
containing SQL reserved words and special characters;

Quoting identifiers and values, to help reduce risk of SQL
injection attacks.

Using Zend_Db_Select is not mandatory. For very simple
SELECT queries, it is usually simpler to specify the entire
SQL query as a string and execute it using Adapter methods like
query() or fetchAll(). Using
Zend_Db_Select is helpful if you need to assemble a
SELECT query procedurally, or based on conditional logic in your
application.

Creating a Select Object

You can create an instance of a Zend_Db_Select object using the
select() method of a
Zend_Db_Adapter_Abstract object.

Example #1 Example of the database adapter's select() method

$db = Zend_Db::factory( ...options... );

$select = $db->select();

Another way to create a Zend_Db_Select object is with its
constructor, specifying the database adapter as an argument.

Example #2 Example of creating a new Select object

$db = Zend_Db::factory( ...options... );

$select = new Zend_Db_Select($db);

Building Select queries

When building the query, you can add clauses of the query one by one. There is a
separate method to add each clause to the Zend_Db_Select object.

Example #3 Example of the using methods to add clauses

// Create the Zend_Db_Select object

$select = $db->select();

// Add a FROM clause

$select->from( ...specify table and columns... )

// Add a WHERE clause

$select->where( ...specify search criteria... )

// Add an ORDER BY clause

$select->order( ...specify sorting criteria... );

You also can use most methods of the Zend_Db_Select object with a
convenient fluent interface. A fluent interface means that each method returns a
reference to the object on which it was called, so you can immediately call another
method.

Example #4 Example of the using the fluent interface

$select = $db->select()

->from( ...specify table and columns... )

->where( ...specify search criteria... )

->order( ...specify sorting criteria... );

The examples in this section show usage of the fluent interface, but you can use the
non-fluent interface in all cases. It is often necessary to use the non-fluent
interface, for example, if your application needs to perform some logic before adding a
clause to a query.

Adding a FROM clause

Specify the table for this query using the from() method.
You can specify the table name as a simple string.
Zend_Db_Select applies identifier quoting around the table
name, so you can use special characters.

Example #5 Example of the from() method

// Build this query:

// SELECT *

// FROM "products"

$select = $db->select()

->from('products');

You can also specify the correlation name (sometimes called the "table alias") for
a table. Instead of a simple string, use an associative array mapping the
correlation name to the table name. In other clauses of the SQL
query, use this correlation name. If your query joins more than one table,
Zend_Db_Select generates unique correlation names based on
the table names, for any tables for which you don't specify the correlation name.

Some RDBMS brands support a leading schema specifier for a table.
You can specify the table name as "schemaName.tableName", where
Zend_Db_Select quotes each part individually, or you may
specify the schema name separately. A schema name specified in the table name takes
precedence over a schema provided separately in the event that both are provided.

Example #7 Example of specifying a schema name

// Build this query:

// SELECT *

// FROM "myschema"."products"

$select = $db->select()

->from('myschema.products');

// or

$select = $db->select()

->from('products', '*', 'myschema');

Adding Columns

In the second argument of the from() method, you can
specify the columns to select from the respective table. If you specify no columns,
the default is "*", the SQL wildcard for
"all columns".

You can list the columns in a simple array of strings, or as an associative mapping
of column alias to column name. If you only have one column to query, and you don't
need to specify a column alias, you can list it as a plain string instead of an
array.

If you give an empty array as the columns argument, no columns from the respective
table are included in the result set. See a
code example
under the section on the join() method.

You can specify the column name as "correlationName.columnName".
Zend_Db_Select quotes each part individually. If you don't
specify a correlation name for a column, it uses the correlation name for the table
named in the current from() method.

Adding Expression Columns

Columns in SQL queries are sometimes expressions, not simply
column names from a table. Expressions should not have correlation names or quoting
applied. If your column string contains parentheses,
Zend_Db_Select recognizes it as an expression.

You also can create an object of type Zend_Db_Expr
explicitly, to prevent a string from being treated as a column name.
Zend_Db_Expr is a minimal class that contains a single
string. Zend_Db_Select recognizes objects of type
Zend_Db_Expr and converts them back to string, but does not
apply any alterations, such as quoting or correlation names.

Note:
Using Zend_Db_Expr for column names is not necessary if
your column expression contains parentheses;
Zend_Db_Select recognizes parentheses and treats the
string as an expression, skipping quoting and correlation names.

In the cases above, Zend_Db_Select does not alter the string
to apply correlation names or identifier quoting. If those changes are necessary to
resolve ambiguity, you must make the changes manually in the string.

If your column names are SQL keywords or contain special
characters, you should use the Adapter's quoteIdentifier()
method and interpolate the result into the string. The
quoteIdentifier() method uses SQL
quoting to delimit the identifier, which makes it clear that it is an identifier for
a table or a column, and not any other part of SQL syntax.

Your code is more database-independent if you use the
quoteIdentifier() method instead of typing quotes literally
in your string, because some RDBMS brands use nonstandard symbols
for quoting identifiers. The quoteIdentifier() method is
designed to use the appropriate quoting symbols based on the adapter type. The
quoteIdentifier() method also escapes any quote characters
that appear within the identifier name itself.

Adding columns to an existing FROM or JOIN table

There may be cases where you wish to add columns to an existing
FROM or JOIN table after those methods have
been called. The columns() method allows you to add
specific columns at any point before the query is executed. You can supply the
columns as either a string or Zend_Db_Expr or as an array of
these elements. The second argument to this method can be omitted, implying that the
columns are to be added to the FROM table, otherwise an existing
correlation name must be used.

Adding Another Table to the Query with JOIN

Many useful queries involve using a JOIN to combine rows from
multiple tables. You can add tables to a Zend_Db_Select query
using the join() method. Using this method is similar to
the from() method, except you can also specify a join
condition in most cases.

The second argument to join() is a string that is the join
condition. This is an expression that declares the criteria by which rows in one
table match rows in the other table. You can use correlation names in this
expression.

Note:
No quoting is applied to the expression you specify for the join condition; if
you have column names that need to be quoted, you must use
quoteIdentifier() as you form the string for the join
condition.

The third argument to join() is an array of column names,
like that used in the from() method. It defaults to
"*", supports correlation names, expressions, and
Zend_Db_Expr in the same way as the array of column names in
the from() method.

To select no columns from a table, use an empty array for the list of columns. This
usage works in the from() method too, but typically you
want some columns from the primary table in your queries, whereas you might want no
columns from a joined table.

This may be the most common type of join. Rows from each table are compared
using the join condition you specify. The result set includes only the rows
that satisfy the join condition. The result set can be empty if no rows
satisfy this condition.

All RDBMS brands support this join type.

LEFT JOIN with the
joinLeft(table, condition, [columns]) method.

All rows from the left operand table are included, matching rows from the
right operand table included, and the columns from the right operand table
are filled with NULL if no row exists matching the left
table.

All RDBMS brands support this join type.

RIGHT JOIN with the
joinRight(table, condition, [columns]) method.

Right outer join is the complement of left outer join. All rows from the
right operand table are included, matching rows from the left operand table
included, and the columns from the left operand table are filled with
NULL's if no row exists matching the right table.

Some RDBMS brands don't support this join type, but in
general any right join can be represented as a left join by reversing the
order of the tables.

FULL JOIN with the
joinFull(table, condition, [columns]) method.

A full outer join is like combining a left outer join and a right outer
join. All rows from both tables are included, paired with each other on the
same row of the result set if they satisfy the join condition, and
otherwise paired with NULL's in place of columns from
the other table.

Some RDBMS brands don't support this join type.

CROSS JOIN with the
joinCross(table, [columns]) method.

A cross join is a Cartesian product. Every row in the first table is
matched to every row in the second table. Therefore the number of rows in
the result set is equal to the product of the number of rows in each table.
You can filter the result set using conditions in a WHERE
clause; in this way a cross join is similar to the old
SQL-89 join syntax.

The joinCross() method has no parameter to specify
the join condition. Some RDBMS brands don't support this
join type.

NATURAL JOIN with the
joinNatural(table, [columns]) method.

A natural join compares any columns that appear with the same name in
both tables. The comparison is equality of all the columns; comparing the
columns using inequality is not a natural join. Only natural inner joins
are supported by this API, even though
SQL permits natural outer joins as well.

The joinNatural() method has no parameter to
specify the join condition.

In addition to these join methods, you can simplify your queries by using the
JoinUsing methods. Instead of supplying a full condition to your join, you simply
pass the column name on which to join and the Zend_Db_Select
object completes the condition for you.

Example #14 Example of the joinUsing() method

// Build this query:

// SELECT *

// FROM "table1"

// JOIN "table2"

// ON "table1".column1 = "table2".column1

// WHERE column2 = 'foo'

$select = $db->select()

->from('table1')

->joinUsing('table2', 'column1')

->where('column2 = ?', 'foo');

Each of the applicable join methods in the Zend_Db_Select
component has a corresponding 'using' method.

Adding a WHERE Clause

You can specify criteria for restricting rows of the result set using the
where() method. The first argument of this method is a
SQL expression, and this expression is used in a
SQLWHERE clause in the query.

Note:
No quoting is applied to expressions given to the
where() or orWhere() methods.
If you have column names that need to be quoted, you must use
quoteIdentifier() as you form the string for the
condition.

The second argument to the where() method is optional. It
is a value to substitute into the expression. Zend_Db_Select
quotes the value and substitutes it for a question-mark ("?")
symbol in the expression.

Zend_Db_Select automatically puts parentheses around each
expression you specify using the where() or
orWhere() methods. This helps to ensure that Boolean
operator precedence does not cause unexpected results.

In the example above, the results would be quite different without the parentheses,
because AND has higher precedence than OR.
Zend_Db_Select applies the parentheses so the effect is that
each expression in successive calls to the where() bind
more tightly than the AND that combines the expressions.

Adding a GROUP BY Clause

In SQL, the GROUP BY clause allows you to
reduce the rows of a query result set to one row per unique value found in the
columns named in the GROUP BY clause.

In Zend_Db_Select, you can specify the columns to use for
calculating the groups of rows using the group() method.
The argument to this method is a column or an array of columns to use in the
GROUP BY clause.

Like the columns array in the from() method, you can use
correlation names in the column name strings, and the column is quoted as an
identifier unless the string contains parentheses or is an object of type
Zend_Db_Expr.

Adding a HAVING Clause

In SQL, the HAVING clause applies a
restriction condition on groups of rows. This is similar to how a
WHERE clause applies a restriction condition on rows. But the
two clauses are different because WHERE conditions are applied
before groups are defined, whereas HAVING conditions are
applied after groups are defined.

In Zend_Db_Select, you can specify conditions for restricting
groups using the having() method. Its usage is similar to
that of the where() method. The first argument is a string
containing a SQL expression. The optional second argument is a
value that is used to replace a positional parameter placeholder in the
SQL expression. Expressions given in multiple invocations of the
having() method are combined using the Boolean
AND operator, or the OR operator if you
use the orHaving() method.

Note:
No quoting is applied to expressions given to the
having() or orHaving()
methods. If you have column names that need to be quoted, you must use
quoteIdentifier() as you form the string for the
condition.

Adding an ORDER BY Clause

In SQL, the ORDER BY clause specifies one or
more columns or expressions by which the result set of a query is sorted. If
multiple columns are listed, the secondary columns are used to resolve ties; the
sort order is determined by the secondary columns if the preceding columns contain
identical values. The default sorting is from least value to greatest value. You can
also sort by greatest value to least value for a given column in the list by
specifying the keyword DESC after that column.

In Zend_Db_Select, you can use the
order() method to specify a column or an array of columns
by which to sort. Each element of the array is a string naming a column. Optionally
with the ASCDESC keyword following it,
separated by a space.

Like in the from() and group()
methods, column names are quoted as identifiers, unless they contain parentheses or
are an object of type Zend_Db_Expr.

Adding a LIMIT Clause

Some RDBMS brands extend SQL with a query
clause known as the LIMIT clause. This clause reduces the
number of rows in the result set to at most a number you specify. You can also
specify to skip a number of rows before starting to output. This feature makes it
easy to take a subset of a result set, for example when displaying query results on
progressive pages of output.

In Zend_Db_Select, you can use the
limit() method to specify the count of rows and the number
of rows to skip. The first argument to this method is the
desired count of rows. The second argument is the number of
rows to skip.

Note:
The LIMIT syntax is not supported by all
RDBMS brands. Some RDBMS require different
syntax to support similar functionality. Each
Zend_Db_Adapter_Abstract class includes a method to
produce SQL appropriate for that RDBMS.

Use the limitPage() method for an alternative way to
specify row count and offset. This method allows you to limit the result set to one
of a series of fixed-length subsets of rows from the query's total result set. In
other words, you specify the length of a "page" of results, and the ordinal number
of the single page of results you want the query to return. The page number is the
first argument of the limitPage() method, and the page
length is the second argument. Both arguments are required; they have no default
values.

Building a UNION Query

You can build union queries with Zend_Db_Select by passing an
array of Zend_Db_Select or SQL Query
strings into the union() method. As second parameter you
can pass the Zend_Db_Select::SQL_UNION or
Zend_Db_Select::SQL_UNION_ALL constants to specify which type
of union you want to perform.

Executing Select Queries

This section describes how to execute the query represented by a
Zend_Db_Select object.

Executing Select Queries from the Db Adapter

You can execute the query represented by the Zend_Db_Select
object by passing it as the first argument to the query()
method of a Zend_Db_Adapter_Abstract object. Use the
Zend_Db_Select objects instead of a string query.

The query() method returns an object of type
Zend_Db_Statement or PDOStatement, depending on the adapter
type.

Example #29 Example using the Db adapter's query() method

$select = $db->select()

->from('products');

$stmt = $db->query($select);

$result = $stmt->fetchAll();

Executing Select Queries from the Object

As an alternative to using the query() method of the
adapter object, you can use the query() method of the
Zend_Db_Select object. Both methods return an object of type
Zend_Db_Statement or PDOStatement, depending on the adapter
type.

Example #30 Example using the Select object's query method

$select = $db->select()

->from('products');

$stmt = $select->query();

$result = $stmt->fetchAll();

Converting a Select Object to a SQL String

If you need access to a string representation of the SQL query
corresponding to the Zend_Db_Select object, use the
__toString() method.

Other methods

This section describes other methods of the Zend_Db_Select class
that are not covered above: getPart() and
reset().

Retrieving Parts of the Select Object

The getPart() method returns a representation of one part
of your SQL query. For example, you can use this method to return
the array of expressions for the WHERE clause, or the array of
columns (or column expressions) that are in the SELECT list, or
the values of the count and offset for the LIMIT clause.

The return value is not a string containing a fragment of SQL
syntax. The return value is an internal representation, which is typically an array
structure containing values and expressions. Each part of the query has a different
structure.

The single argument to the getPart() method is a string
that identifies which part of the Select query to return. For example, the string
'from' identifies the part of the Select object that stores
information about the tables in the FROM clause, including
joined tables.

The Zend_Db_Select class defines constants you can use for
parts of the SQL query. You can use these constant definitions,
or you can the literal strings.

Resetting Parts of the Select Object

The reset() method enables you to clear one specified part
of the SQL query, or else clear all parts of the
SQL query if you omit the argument.

The single argument is optional. You can specify the part of the query to clear,
using the same strings you used in the argument to the
getPart() method. The part of the query you specify is
reset to a default state.

If you omit the parameter, reset() changes all parts of the
query to their default state. This makes the Zend_Db_Select
object equivalent to a new object, as though you had just instantiated it.