Class Zend_Db_Select

Description

Class for SQL SELECT generation and results.

  • copyright: Copyright (c) 2005-2008 Zend Technologies USA Inc. (http://www.zend.com)
  • license: New BSD License

Located in /Db/Select.php (line 44)


	
			
Direct descendents
Class Description
 class Zend_Db_Table_Select Class for SQL SELECT query manipulation for the Zend_Db_Table component.
Class Constant Summary
 COLUMNS = 'columns'
 CROSS_JOIN = 'cross join'
 DISTINCT = 'distinct'
 FOR_UPDATE = 'forupdate'
 FROM = 'from'
 FULL_JOIN = 'full join'
 GROUP = 'group'
 HAVING = 'having'
 INNER_JOIN = 'inner join'
 LEFT_JOIN = 'left join'
 LIMIT_COUNT = 'limitcount'
 LIMIT_OFFSET = 'limitoffset'
 NATURAL_JOIN = 'natural join'
 ORDER = 'order'
 RIGHT_JOIN = 'right join'
 SQL_AND = 'AND'
 SQL_AS = 'AS'
 SQL_ASC = 'ASC'
 SQL_DESC = 'DESC'
 SQL_DISTINCT = 'DISTINCT'
 SQL_FOR_UPDATE = 'FOR UPDATE'
 SQL_FROM = 'FROM'
 SQL_GROUP_BY = 'GROUP BY'
 SQL_HAVING = 'HAVING'
 SQL_ON = 'ON'
 SQL_OR = 'OR'
 SQL_ORDER_BY = 'ORDER BY'
 SQL_SELECT = 'SELECT'
 SQL_UNION = 'UNION'
 SQL_UNION_ALL = 'UNION ALL'
 SQL_WHERE = 'WHERE'
 SQL_WILDCARD = '*'
 UNION = 'union'
 WHERE = 'where'
Variable Summary
 static array $_joinTypes
 static array $_partsInit
 static array $_unionTypes
 array $_parts
 array $_tableCols
Method Summary
 string assemble ()
 Zend_Db_Select columns ([array|string|Zend_Db_Expr $cols = '*'], [string $correlationName = null])
 Zend_Db_Select distinct ([bool $flag = true])
 Zend_Db_Select forUpdate ([bool $flag = true])
 Zend_Db_Select from (array|string|Zend_Db_Expr $name, [array|string|Zend_Db_Expr $cols = '*'], [string $schema = null])
 mixed getPart (string $part)
 Zend_Db_Select group (array|string $spec)
 Zend_Db_Select having (string $cond, string|Zend_Db_Expr $val)
 Zend_Db_Select join (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinCross (array|string|Zend_Db_Expr $name, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinFull (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinInner (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinLeft (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinNatural (array|string|Zend_Db_Expr $name, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select joinRight (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
 Zend_Db_Select limit ([int $count = null], [int $offset = null])
 Zend_Db_Select limitPage (int $page, int $rowCount)
 Zend_Db_Select order (mixed $spec)
 Zend_Db_Select orHaving (string $cond, string $val)
 Zend_Db_Select orWhere (string $cond, [string $value = null], [constant $type = null])
 PDO_Statement|Zend_Db_Statement query ([integer $fetchMode = null])
 Zend_Db_Select reset ([string $part = null])
 Zend_Db_Select __construct (Zend_Db_Adapter_Abstract $adapter)
 Zend_Db_Select union ([array|string|Zend_Db_Select $select = array()], [ $type = self::SQL_UNION])
 Zend_Db_Select where (string $cond, [string $value = null], [constant $type = null])
 array _getDummyTable ()
 string|null _getQuotedSchema ([string $schema = null])
 string _getQuotedTable (string $tableName, [string $correlationName = null])
 Zend_Db_Select _join (null|string $type, array|string|Zend_Db_Expr $name, string $cond, array|string $cols, [string $schema = null])
 Zend_Db_Select _joinUsing ( $type,  $name,  $cond, [ $cols = '*'], [ $schema = null])
 string _renderColumns (string $sql)
 string _renderDistinct (string $sql)
 string _renderForupdate (string $sql)
 string _renderFrom (string $sql)
 string _renderGroup (string $sql)
 string _renderHaving (string $sql)
 string _renderLimitoffset (string $sql)
 string _renderOrder (string $sql)
 string _renderUnion (string $sql)
 string _renderWhere (string $sql)
 void _tableCols ( $correlationName, array|string $cols, string $tbl)
 string _where (string $condition, [string $value = null], [string $type = null], [boolean $bool = true])
 Zend_Db_Select __call (string $method,  $args)
 string __toString ()
Variables
static array $_joinTypes = array(
self::INNER_JOIN,
self::LEFT_JOIN,
self::RIGHT_JOIN,
self::FULL_JOIN,
self::CROSS_JOIN,
self::NATURAL_JOIN,
)
(line 117)

Specify legal join types.

  • access: protected
static array $_partsInit = array(
self::DISTINCT => false,
self::COLUMNS => array(),self::UNION=>array(),self::FROM=>array(),self::WHERE=>array(),self::GROUP=>array(),self::HAVING=>array(),self::ORDER=>array(),self::LIMIT_COUNT=>null,self::LIMIT_OFFSET=>null,self::FOR_UPDATE=>false)
(line 98)

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

  • access: protected
static array $_unionTypes = array(
self::SQL_UNION,
self::SQL_UNION_ALL
)
(line 131)

Specify legal union types.

  • access: protected
Zend_Db_Adapter_Abstract $_adapter (line 89)

Zend_Db_Adapter_Abstract object.

  • access: protected
array $_parts = array() (line 142)

The component parts of a SELECT statement.

Initialized to the $_partsInit array in the constructor.

  • access: protected
array $_tableCols = array() (line 149)

Tracks which columns are being select from each table and join.

  • access: protected
Methods
assemble (line 652)

Converts this object to an SQL SELECT string.

  • return: This object as a SELECT string.
  • access: public
string assemble ()

Redefined in descendants as:
columns (line 213)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select columns ([array|string|Zend_Db_Expr $cols = '*'], [string $correlationName = null])
  • array|string|Zend_Db_Expr $cols: The columns to select from this table.
  • string $correlationName: Correlation name of target table. OPTIONAL
distinct (line 168)

Makes the query SELECT DISTINCT.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select distinct ([bool $flag = true])
  • bool $flag: Whether or not the SELECT is DISTINCT (default true).
forUpdate (line 608)

Makes the query SELECT FOR UPDATE.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select forUpdate ([bool $flag = true])
  • bool $flag: Whether or not the SELECT is FOR UPDATE (default true).
from (line 198)

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 physical table name, and the value is the correlation name. For example, array('table' => 'alias'). 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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select from (array|string|Zend_Db_Expr $name, [array|string|Zend_Db_Expr $cols = '*'], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name or an associative array relating table name to correlation name.
  • array|string|Zend_Db_Expr $cols: The columns to select from this table.
  • string $schema: The schema name to specify, if any.

Redefined in descendants as:
getAdapter (line 686)

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

  • access: public
Zend_Db_Adapter_Abstract getAdapter ()
getPart (line 621)

Get part of the structured information for the currect query.

  • throws: Zend_Db_Select_Exception
  • access: public
mixed getPart (string $part)
  • string $part
group (line 463)

Adds grouping to the query.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select group (array|string $spec)
  • array|string $spec: The column(s) to group by.
having (line 490)

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 where() for an example

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select having (string $cond, string|Zend_Db_Expr $val)
  • string $cond: The HAVING condition.
  • string|Zend_Db_Expr $val: A single value to quote into the condition.
join (line 271)

Adds a JOIN table and columns to the query.

The $name and $cols parameters follow the same logic as described in the from() method.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select join (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • string $cond: Join on this condition.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinCross (line 374)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinCross (array|string|Zend_Db_Expr $name, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinFull (line 357)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinFull (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • string $cond: Join on this condition.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinInner (line 292)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinInner (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • string $cond: Join on this condition.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinLeft (line 313)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinLeft (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • string $cond: Join on this condition.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinNatural (line 394)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinNatural (array|string|Zend_Db_Expr $name, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
joinRight (line 335)

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.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select joinRight (array|string|Zend_Db_Expr $name, string $cond, [array|string $cols = self::SQL_WILDCARD], [string $schema = null])
  • array|string|Zend_Db_Expr $name: The table name.
  • string $cond: Join on this condition.
  • array|string $cols: The columns to select from the joined table.
  • string $schema: The database name to specify, if any.
limit (line 579)

Sets a limit count and offset to the query.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select limit ([int $count = null], [int $offset = null])
  • int $count: OPTIONAL The number of rows to return.
  • int $offset: OPTIONAL Start returning after this many rows.
limitPage (line 593)

Sets the limit and count by page number.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select limitPage (int $page, int $rowCount)
  • int $page: Limit results to this page number.
  • int $rowCount: Use this many rows per page.
order (line 539)

Adds a row order to the query.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select order (mixed $spec)
  • mixed $spec: The column(s) and direction to order by.
orHaving (line 517)

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().

Zend_Db_Select orHaving (string $cond, string $val)
  • string $cond: The HAVING condition.
  • string $val: A single value to quote into the condition.
orWhere (line 450)

Adds a WHERE condition to the query by OR.

Otherwise identical to where().

Zend_Db_Select orWhere (string $cond, [string $value = null], [constant $type = null])
  • string $cond: The WHERE condition.
  • string $value: OPTIONAL A single value to quote into the condition.
  • constant $type: OPTIONAL The type of the given value
query (line 637)

Executes the current select object and returns the result

  • access: public
PDO_Statement|Zend_Db_Statement query ([integer $fetchMode = null])
  • integer $fetchMode: OPTIONAL
reset (line 670)

Clear parts of the Select object, or an individual part.

  • access: public
Zend_Db_Select reset ([string $part = null])
  • string $part: OPTIONAL
Constructor __construct (line 156)

Class constructor

  • access: public
Zend_Db_Select __construct (Zend_Db_Adapter_Abstract $adapter)

Redefined in descendants as:
union (line 241)

Adds a UNION clause to the query.

The first parameter $select can be a string, an existing Zend_Db_Select object or an array of either of these types.

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select union ([array|string|Zend_Db_Select $select = array()], [ $type = self::SQL_UNION])
  • array|string|Zend_Db_Select $select: One or more select clauses for the UNION.
  • $type
where (line 431)

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.

  1.  // simplest but non-secure
  2.  $select->where("id = $id");
  3.  
  4.  // secure (ID is quoted but matched anyway)
  5.  $select->where('id = ?'$id);
  6.  
  7.  // alternatively, with named binding
  8.  $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:

  1.  $db->fetchAll($selectarray('id' => 5));

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select where (string $cond, [string $value = null], [constant $type = null])
  • string $cond: The WHERE condition.
  • string $value: OPTIONAL A single value to quote into the condition.
  • constant $type: OPTIONAL The type of the given value
_getDummyTable (line 913)
  • access: protected
array _getDummyTable ()
_getQuotedSchema (line 924)

Return a quoted schema name

  • access: protected
string|null _getQuotedSchema ([string $schema = null])
  • string $schema: The schema name OPTIONAL
_getQuotedTable (line 939)

Return a quoted table name

  • access: protected
string _getQuotedTable (string $tableName, [string $correlationName = null])
  • string $tableName: The table name
  • string $correlationName: The correlation name OPTIONAL
_join (line 707)

Populate the $_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.

  • return: This Zend_Db_Select object
  • throws: Zend_Db_Select_Exception
  • access: protected
Zend_Db_Select _join (null|string $type, array|string|Zend_Db_Expr $name, string $cond, array|string $cols, [string $schema = null])
  • null|string $type: Type of join; inner, left, and null are currently supported
  • array|string|Zend_Db_Expr $name: Table name
  • string $cond: Join on this condition
  • array|string $cols: The columns to select from the joined table
  • string $schema: The database name to specify, if any.
_joinUsing (line 802)

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.

  1.  $select $db->select()->from('table1')
  2.                         ->joinUsing('table2''column1');
  3.  
  4.  // 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

  • return: This Zend_Db_Select object.
  • access: public
Zend_Db_Select _joinUsing ( $type,  $name,  $cond, [ $cols = '*'], [ $schema = null])
  • $type
  • $name
  • $cond
  • $cols
  • $schema
_renderColumns (line 965)

Render DISTINCT clause

  • access: protected
string _renderColumns (string $sql)
  • string $sql: SQL query
_renderDistinct (line 950)

Render DISTINCT clause

  • access: protected
string _renderDistinct (string $sql)
  • string $sql: SQL query
_renderForupdate (line 1172)

Render FOR UPDATE clause

  • access: protected
string _renderForupdate (string $sql)
  • string $sql: SQL query
_renderFrom (line 998)

Render FROM clause

  • access: protected
string _renderFrom (string $sql)
  • string $sql: SQL query
_renderGroup (line 1084)

Render GROUP clause

  • access: protected
string _renderGroup (string $sql)
  • string $sql: SQL query
_renderHaving (line 1103)

Render HAVING clause

  • access: protected
string _renderHaving (string $sql)
  • string $sql: SQL query
_renderLimitoffset (line 1141)

Render LIMIT OFFSET clause

  • access: protected
string _renderLimitoffset (string $sql)
  • string $sql: SQL query
_renderOrder (line 1118)

Render ORDER clause

  • access: protected
string _renderOrder (string $sql)
  • string $sql: SQL query
_renderUnion (line 1044)

Render UNION query

  • access: protected
string _renderUnion (string $sql)
  • string $sql: SQL query
_renderWhere (line 1069)

Render WHERE clause

  • access: protected
string _renderWhere (string $sql)
  • string $sql: SQL query
_tableCols (line 848)

Adds to the internal table-to-column mapping array.

  • access: protected
void _tableCols ( $correlationName, array|string $cols, string $tbl)
  • string $tbl: The table/join the columns come from.
  • array|string $cols: The list of columns; preferably as an array, but possibly as a string containing one column.
  • $correlationName
_where (line 887)

Internal function for creating the where clause

  • return: clause
  • access: protected
string _where (string $condition, [string $value = null], [string $type = null], [boolean $bool = true])
  • string $condition
  • string $value: optional
  • string $type: optional
  • boolean $bool: true = AND, false = OR
__call (line 1190)

Turn magic function calls into non-magic function calls for joinUsing syntax

  • throws: Zend_Db_Select_Exception If an invalid method is called.
  • access: public
Zend_Db_Select __call (string $method,  $args)
  • string $method
  • array $args: OPTIONAL Zend_Db_Table_Select query modifier
__toString (line 1228)

Implements magic method.

  • return: This object as a SELECT string.
  • access: public
string __toString ()
Class Constants
COLUMNS = 'columns' (line 48)
CROSS_JOIN = 'cross join' (line 63)
DISTINCT = 'distinct' (line 47)
FOR_UPDATE = 'forupdate' (line 57)
FROM = 'from' (line 49)
FULL_JOIN = 'full join' (line 62)
GROUP = 'group' (line 52)
HAVING = 'having' (line 53)
INNER_JOIN = 'inner join' (line 59)
LEFT_JOIN = 'left join' (line 60)
LIMIT_COUNT = 'limitcount' (line 55)
LIMIT_OFFSET = 'limitoffset' (line 56)
NATURAL_JOIN = 'natural join' (line 64)
ORDER = 'order' (line 54)
RIGHT_JOIN = 'right join' (line 61)
SQL_AND = 'AND' (line 77)
SQL_AS = 'AS' (line 78)
SQL_ASC = 'ASC' (line 81)
SQL_DESC = 'DESC' (line 82)
SQL_DISTINCT = 'DISTINCT' (line 72)
SQL_FOR_UPDATE = 'FOR UPDATE' (line 76)
SQL_FROM = 'FROM' (line 70)
SQL_GROUP_BY = 'GROUP BY' (line 73)
SQL_HAVING = 'HAVING' (line 75)
SQL_ON = 'ON' (line 80)
SQL_OR = 'OR' (line 79)
SQL_ORDER_BY = 'ORDER BY' (line 74)
SQL_SELECT = 'SELECT' (line 67)
SQL_UNION = 'UNION' (line 68)
SQL_UNION_ALL = 'UNION ALL' (line 69)
SQL_WHERE = 'WHERE' (line 71)
SQL_WILDCARD = '*' (line 66)
UNION = 'union' (line 50)
WHERE = 'where' (line 51)

Documentation generated on Fri, 08 Jan 2010 17:30:03 -0500 by phpDocumentor 1.4.3