Db/Select.php
Zend Framework
LICENSE
This source file is subject to the new BSD license that is bundled
with this package in the file LICENSE.txt.
It is also available through the world-wide-web at this URL:
http://framework.zend.com/license/new-bsd
If you did not receive a copy of the license and are unable to
obtain it through the world-wide-web, please send an email
to license@zend.com so we can send you a copy immediately.
- category
- Zend
- copyright
- Copyright (c) 2005-2011 Zend Technologies USA Inc. (http://www.zend.com)
- license
-
New BSD License
- package
- Zend_Db
- subpackage
- Select
- version
- $Id: Select.php 23775 2011-03-01 17:25:24Z ralph $
Class for SQL SELECT generation and results.
- category
- Zend
- copyright
- Copyright (c) 2005-2011 Zend Technologies USA Inc. (http://www.zend.com)
- license
-
New BSD License
- package
- Zend_Db
- subpackage
- Select
Constants
Properties



array
$_joinTypes= 'array'
staticSpecify legal join types.
Default valuearray
Details
- Type
- array



array
$_parts= 'array'
The component parts of a SELECT statement.
Initialized to the $_partsInit array in the constructor.
Default valuearray
Details
- Type
- array



array
$_partsInit= 'array'
staticThe initial values for the $_parts array.
NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure
meximum compatibility with database adapters.
Default valuearray
Details
- Type
- array



array
$_tableCols= 'array'
Tracks which columns are being select from each table and join.
Default valuearray
Details
- Type
- array



array
$_unionTypes= 'array'
staticSpecify legal union types.
Default valuearray
Details
- Type
- array
Methods



__call(
string $method, array $args
)
:
\Zend_Db_Select
Turn magic function calls into non-magic function calls
for joinUsing syntax
Parameters
Name |
Type |
Description |
$method |
string |
|
$args |
array |
OPTIONAL Zend_Db_Table_Select query modifier |
Returns
Throws



_getQuotedSchema(
string $schema
=
null
)
:
string|null
Return a quoted schema name
Parameters
Name |
Type |
Description |
$schema |
string |
The schema name OPTIONAL |
Returns
Type |
Description |
stringnull |
|



_getQuotedTable(
string $tableName, string $correlationName
=
null
)
:
string
Return a quoted table name
Parameters
Name |
Type |
Description |
$tableName |
string |
The table name |
$correlationName |
string |
The correlation name OPTIONAL |
Returns



_join(
null|string $type, array|string|\Zend_Db_Expr $name, string $cond, array|string $cols, string $schema
=
null
)
:
\Zend_Db_Select
Populate the {@link $_parts} 'join' key
Does the dirty work of populating the join key.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$type |
null|string |
Type of join; inner, left, and null are currently supported |
$name |
array|string|\Zend_Db_Expr
|
Table name |
$cond |
string |
Join on this condition |
$cols |
array|string |
The columns to select from the joined table |
$schema |
string |
The database name to specify, if any. |
Returns
Throws



_joinUsing(
$type, $name, $cond, $cols
=
*, $schema
=
null
)
:
\Zend_Db_Select
Handle JOIN... USING... syntax
This is functionality identical to the existing JOIN methods, however
the join condition can be passed as a single column name. This method
then completes the ON condition by using the same field for the FROM
table and the JOIN table.
$select = $db->select()->from('table1')
->joinUsing('table2', 'column1');
// SELECT * FROM table1 JOIN table2 ON table1.column1 = table2.column2
These joins are called by the developer simply by adding 'Using' to the
method name. E.g.
* joinUsing
* joinInnerUsing
* joinFullUsing
* joinRightUsing
* joinLeftUsing
Parameters
Name |
Type |
Description |
$type |
|
|
$name |
|
|
$cond |
|
|
$cols |
|
|
$schema |
|
|
Returns



_renderLimitoffset(
string $sql
)
:
string
Render LIMIT OFFSET clause
Parameters
Name |
Type |
Description |
$sql |
string |
SQL query |
Returns



_tableCols(
$correlationName, array|string $cols, bool|string $afterCorrelationName
=
null
)
:
void
Adds to the internal table-to-column mapping array.
Parameters
Name |
Type |
Description |
$correlationName |
|
|
$cols |
array|string |
The list of columns; preferably as an array, but possibly as a string containing one column. |
$afterCorrelationName |
bool|string |
True if it should be prepended, a correlation name if it should be inserted |



_uniqueCorrelation(
string|array $name
)
:
string
Generate a unique correlation name
Parameters
Name |
Type |
Description |
$name |
string|array |
A qualified identifier. |
Returns
Type |
Description |
string |
A unique correlation name. |



_where(
string $condition, mixed $value
=
null, string $type
=
null, boolean $bool
=
true
)
:
string
Internal function for creating the where clause
Parameters
Name |
Type |
Description |
$condition |
string |
|
$value |
mixed |
optional |
$type |
string |
optional |
$bool |
boolean |
true = AND, false = OR
|
Returns
Type |
Description |
string |
clause |



assemble(
)
:
string|null
Converts this object to an SQL SELECT string.
Returns
Type |
Description |
stringnull |
This object as a SELECT string. (or null if a string cannot be produced.) |



columns(
array|string|\Zend_Db_Expr $cols
=
*, string $correlationName
=
null
)
:
\Zend_Db_Select
Specifies the columns used in the FROM clause.
The parameter can be a single string or Zend_Db_Expr object,
or else an array of strings or Zend_Db_Expr objects.
Parameters
Name |
Type |
Description |
$cols |
array|string|\Zend_Db_Expr
|
The columns to select from this table. |
$correlationName |
string |
Correlation name of target table. OPTIONAL |
Returns



distinct(
bool $flag
=
true
)
:
\Zend_Db_Select
Makes the query SELECT DISTINCT.
Parameters
Name |
Type |
Description |
$flag |
bool |
Whether or not the SELECT is DISTINCT (default true).
|
Returns



forUpdate(
bool $flag
=
true
)
:
\Zend_Db_Select
Makes the query SELECT FOR UPDATE.
Parameters
Name |
Type |
Description |
$flag |
bool |
Whether or not the SELECT is FOR UPDATE (default true).
|
Returns



from(
array|string|\Zend_Db_Expr $name, array|string|\Zend_Db_Expr $cols
=
*, string $schema
=
null
)
:
\Zend_Db_Select
Adds a FROM table and optional columns to the query.
The first parameter $name can be a simple string, in which case the
correlation name is generated automatically. If you want to specify
the correlation name, the first parameter must be an associative
array in which the key is the correlation name, and the value is
the physical table name. For example, array('alias' => 'table').
The correlation name is prepended to all columns fetched for this
table.
The second parameter can be a single string or Zend_Db_Expr object,
or else an array of strings or Zend_Db_Expr objects.
The first parameter can be null or an empty string, in which case
no correlation name is generated or prepended to the columns named
in the second parameter.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name or an associative array relating correlation name to table name. |
$cols |
array|string|\Zend_Db_Expr
|
The columns to select from this table. |
$schema |
string |
The schema name to specify, if any. |
Returns



getPart(
string $part
)
:
mixed
Get part of the structured information for the currect query.
Parameters
Name |
Type |
Description |
$part |
string |
|
Returns
Throws



group(
array|string $spec
)
:
\Zend_Db_Select
Adds grouping to the query.
Parameters
Name |
Type |
Description |
$spec |
array|string |
The column(s) to group by.
|
Returns



having(
string $cond, mixed $value
=
null, int $type
=
null
)
:
\Zend_Db_Select
Adds a HAVING condition to the query by AND.
If a value is passed as the second param, it will be quoted
and replaced into the condition wherever a question-mark
appears. See {@link where()} for an example
Parameters
Name |
Type |
Description |
$cond |
string |
The HAVING condition. |
$value |
mixed |
OPTIONAL The value to quote into the condition. |
$type |
int |
OPTIONAL The type of the given value |
Returns



join(
array|string|\Zend_Db_Expr $name, string $cond, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Adds a JOIN table and columns to the query.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cond |
string |
Join on this condition. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinCross(
array|string|\Zend_Db_Expr $name, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add a CROSS JOIN table and colums to the query.
A cross join is a cartesian product; there is no join condition.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinFull(
array|string|\Zend_Db_Expr $name, string $cond, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add a FULL OUTER JOIN table and colums to the query.
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 NULLs in place of columns from the other table.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cond |
string |
Join on this condition. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinInner(
array|string|\Zend_Db_Expr $name, string $cond, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add an INNER JOIN table and colums to the query
Rows in both tables are matched according to the expression
in the $cond argument. The result set is comprised
of all cases where rows from the left table match
rows from the right table.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cond |
string |
Join on this condition. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinLeft(
array|string|\Zend_Db_Expr $name, string $cond, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add a LEFT OUTER JOIN table and colums to the query
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 NULLs if no row exists matching the left table.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cond |
string |
Join on this condition. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinNatural(
array|string|\Zend_Db_Expr $name, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add a NATURAL JOIN table and colums to the query.
A natural join assumes an equi-join across any column(s)
that appear with the same name in both tables.
Only natural inner joins are supported by this API,
even though SQL permits natural outer joins as well.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



joinRight(
array|string|\Zend_Db_Expr $name, string $cond, array|string $cols
=
self, string $schema
=
null
)
:
\Zend_Db_Select
Add a RIGHT OUTER JOIN table and colums to the query.
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 NULLs if no row exists matching the right table.
The $name and $cols parameters follow the same logic
as described in the from() method.
Parameters
Name |
Type |
Description |
$name |
array|string|\Zend_Db_Expr
|
The table name. |
$cond |
string |
Join on this condition. |
$cols |
array|string |
The columns to select from the joined table. |
$schema |
string |
The database name to specify, if any. |
Returns



limit(
int $count
=
null, int $offset
=
null
)
:
\Zend_Db_Select
Sets a limit count and offset to the query.
Parameters
Name |
Type |
Description |
$count |
int |
OPTIONAL The number of rows to return. |
$offset |
int |
OPTIONAL Start returning after this many rows. |
Returns



limitPage(
int $page, int $rowCount
)
:
\Zend_Db_Select
Sets the limit and count by page number.
Parameters
Name |
Type |
Description |
$page |
int |
Limit results to this page number. |
$rowCount |
int |
Use this many rows per page. |
Returns



orHaving(
string $cond, mixed $value
=
null, int $type
=
null
)
:
\Zend_Db_Select
Adds a HAVING condition to the query by OR.
Otherwise identical to orHaving().
Parameters
Name |
Type |
Description |
$cond |
string |
The HAVING condition. |
$value |
mixed |
OPTIONAL The value to quote into the condition. |
$type |
int |
OPTIONAL The type of the given value |
Returns
Details
- see
- \having()



orWhere(
string $cond, mixed $value
=
null, int $type
=
null
)
:
\Zend_Db_Select
Adds a WHERE condition to the query by OR.
Otherwise identical to where().
Parameters
Name |
Type |
Description |
$cond |
string |
The WHERE condition. |
$value |
mixed |
OPTIONAL The value to quote into the condition. |
$type |
int |
OPTIONAL The type of the given value |
Returns
Details
- see
- \where()



order(
mixed $spec
)
:
\Zend_Db_Select
Adds a row order to the query.
Parameters
Name |
Type |
Description |
$spec |
mixed |
The column(s) and direction to order by.
|
Returns



query(
integer $fetchMode
=
null, mixed $bind
=
array
)
:
\PDO_Statement|\Zend_Db_Statement
Executes the current select object and returns the result
Parameters
Name |
Type |
Description |
$fetchMode |
integer |
OPTIONAL |
$bind |
mixed |
An array of data to bind to the placeholders. |
Returns



reset(
string $part
=
null
)
:
\Zend_Db_Select
Clear parts of the Select object, or an individual part.
Parameters
Name |
Type |
Description |
$part |
string |
OPTIONAL |
Returns



union(
array $select
=
array, $type
=
self
)
:
\Zend_Db_Select
Adds a UNION clause to the query.
The first parameter has to be an array of Zend_Db_Select or
sql query strings.
$sql1 = $db->select();
$sql2 = "SELECT ...";
$select = $db->select()
->union(array($sql1, $sql2))
->order("id");
Parameters
Name |
Type |
Description |
$select |
array |
Array of select clauses for the union. |
$type |
|
|
Returns



where(
string $cond, mixed $value
=
null, int $type
=
null
)
:
\Zend_Db_Select
Adds a WHERE condition to the query by AND.
If a value is passed as the second param, it will be quoted
and replaced into the condition wherever a question-mark
appears. Array values are quoted and comma-separated.
// simplest but non-secure
$select->where("id = $id");
// secure (ID is quoted but matched anyway)
$select->where('id = ?', $id);
// alternatively, with named binding
$select->where('id = :id');
Note that it is more correct to use named bindings in your
queries for values other than strings. When you use named
bindings, don't forget to pass the values when actually
making a query:
$db->fetchAll($select, array('id' => 5));
Parameters
Name |
Type |
Description |
$cond |
string |
The WHERE condition. |
$value |
mixed |
OPTIONAL The value to quote into the condition. |
$type |
int |
OPTIONAL The type of the given value |
Returns