Db/Select.php

Show:

inherited

Classes
- \Zend_Db_Select

Table of Contents

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-2014 Zend Technologies USA Inc. (http://www.zend.com)
License: New BSD License
Package: Zend_Db
Subpackage: Select
Version: $Id$

\Zend_Db_Select
jump to top

Package: Zend_Db\Select

Class for SQL SELECT generation and results.

Children: \Zend_Db_Table_Select
Category: Zend
Copyright: Copyright (c) 2005-2014 Zend Technologies USA Inc. (http://www.zend.com)
License: New BSD License

Constants

 
                DISTINCT
                = 'distinct'

 
                COLUMNS
                = 'columns'

 
                FROM
                = 'from'

 
                UNION
                = 'union'

 
                WHERE
                = 'where'

 
                GROUP
                = 'group'

 
                HAVING
                = 'having'

 
                ORDER
                = 'order'

 
                LIMIT_COUNT
                = 'limitcount'

 
                LIMIT_OFFSET
                = 'limitoffset'

 
                FOR_UPDATE
                = 'forupdate'

 
                INNER_JOIN
                = 'inner join'

 
                LEFT_JOIN
                = 'left join'

 
                RIGHT_JOIN
                = 'right join'

 
                FULL_JOIN
                = 'full join'

 
                CROSS_JOIN
                = 'cross join'

 
                NATURAL_JOIN
                = 'natural join'

 
                SQL_WILDCARD
                = '*'

 
                SQL_SELECT
                = 'SELECT'

 
                SQL_UNION
                = 'UNION'

 
                SQL_UNION_ALL
                = 'UNION ALL'

 
                SQL_FROM
                = 'FROM'

 
                SQL_WHERE
                = 'WHERE'

 
                SQL_DISTINCT
                = 'DISTINCT'

 
                SQL_GROUP_BY
                = 'GROUP BY'

 
                SQL_ORDER_BY
                = 'ORDER BY'

 
                SQL_HAVING
                = 'HAVING'

 
                SQL_FOR_UPDATE
                = 'FOR UPDATE'

 
                SQL_AND
                = 'AND'

 
                SQL_AS
                = 'AS'

 
                SQL_OR
                = 'OR'

 
                SQL_ON
                = 'ON'

 
                SQL_ASC
                = 'ASC'

 
                SQL_DESC
                = 'DESC'

Properties

\Zend_Db_Adapter_Abstract $_adapter

Zend_Db_Adapter_Abstract object.

Details

Type: \Zend_Db_Adapter_Abstract

array $_bind = array()

Bind variables for query

Default valuearray()Details

Type: array

array $_joinTypes = array(self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN)

static

Specify legal join types.

Default valuearray(self::INNER_JOIN, self::LEFT_JOIN, self::RIGHT_JOIN, self::FULL_JOIN, self::CROSS_JOIN, self::NATURAL_JOIN)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(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)

static

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.

Default value

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)

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(self::SQL_UNION, self::SQL_UNION_ALL)

static

Specify legal union types.

Default valuearray(self::SQL_UNION, self::SQL_UNION_ALL)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

Type	Description
\Zend_Db_Select

Throws

Exception	Description
\Zend_Db_Select_Exception	If an invalid method is called.

__construct(\Zend_Db_Adapter_Abstract $adapter) : void

Class constructor

Parameters

Name	Type	Description
$adapter	\Zend_Db_Adapter_Abstract

__toString() : string

Implements magic method.

Returns

Type	Description
string	This object as a SELECT string.

_getDummyTable() : array

Returns

Type	Description
array

_getQuotedSchema(string $schema = null) : string | null

Return a quoted schema name

Parameters

Name	Type	Description
$schema	string	The schema name OPTIONAL

Returns

Type	Description
string \| null

_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

Type	Description
string

_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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object

Throws

Exception	Description
\Zend_Db_Select_Exception

_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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

_renderColumns(string $sql) : string | null

Render DISTINCT clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string \| null

_renderDistinct(string $sql) : string

Render DISTINCT clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderForupdate(string $sql) : string

Render FOR UPDATE clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderFrom(string $sql) : string

Render FROM clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderGroup(string $sql) : string

Render GROUP clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderHaving(string $sql) : string

Render HAVING clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderLimitoffset(string $sql) : string

Render LIMIT OFFSET clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderOrder(string $sql) : string

Render ORDER clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderUnion(string $sql) : string

Render UNION query

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_renderWhere(string $sql) : string

Render WHERE clause

Parameters

Name	Type	Description
$sql	string	SQL query

Returns

Type	Description
string

_tableCols( $correlationName, array | string $cols, $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

_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
string \| null	This object as a SELECT string. (or null if a string cannot be produced.)

bind(mixed $bind) : \Zend_Db_Select

Set bind variables

Parameters

Name	Type	Description
$bind	mixed

Returns

Type	Description
\Zend_Db_Select

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

getAdapter() : \Zend_Db_Adapter_Abstract

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

Returns

Type	Description
\Zend_Db_Adapter_Abstract

getBind() : array

Get bind variables

Returns

Type	Description
array

getPart(string $part) : mixed

Get part of the structured information for the current query.

Parameters

Name	Type	Description
$part	string

Returns

Type	Description
mixed

Throws

Exception	Description
\Zend_Db_Select_Exception

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

join(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinCross(array | string | \Zend_Db_Expr $name, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinFull(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinInner(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinLeft(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinNatural(array | string | \Zend_Db_Expr $name, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

joinRight(array | string | \Zend_Db_Expr $name, string $cond, array | string $cols = self::SQL_WILDCARD, 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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

Details

See

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

Details

See

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\PDO_Statement \| \Zend_Db_Statement

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

Type	Description
\Zend_Db_Select

union(array $select = array(), $type = self::SQL_UNION) : \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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

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

Type	Description
\Zend_Db_Select	This Zend_Db_Select object.

Db/Select.php

\Zend_Db_Selectjump to top

Constants

Properties

Methods

\Zend_Db_Select
jump to top