Public Member Functions | Data Fields | Protected Member Functions | Protected Attributes | Static Protected Attributes

Zend_Db_Select Class Reference

Inheritance diagram for Zend_Db_Select:
Zend_Db_Table_Select

Public Member Functions

 __construct (Zend_Db_Adapter_Abstract $adapter)
 getBind ()
 bind ($bind)
 distinct ($flag=true)
 from ($name, $cols= '*', $schema=null)
 columns ($cols= '*', $correlationName=null)
 union ($select=array(), $type=self::SQL_UNION)
 join ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 joinInner ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 joinLeft ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 joinRight ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 joinFull ($name, $cond, $cols=self::SQL_WILDCARD, $schema=null)
 joinCross ($name, $cols=self::SQL_WILDCARD, $schema=null)
 joinNatural ($name, $cols=self::SQL_WILDCARD, $schema=null)
 where ($cond, $value=null, $type=null)
 orWhere ($cond, $value=null, $type=null)
 group ($spec)
 having ($cond)
 orHaving ($cond)
 order ($spec)
 limit ($count=null, $offset=null)
 limitPage ($page, $rowCount)
 forUpdate ($flag=true)
 getPart ($part)
 query ($fetchMode=null, $bind=array())
 assemble ()
 reset ($part=null)
 getAdapter ()
 _joinUsing ($type, $name, $cond, $cols= '*', $schema=null)
 __call ($method, array $args)
 __toString ()

Data Fields

const DISTINCT = 'distinct'
const COLUMNS = 'columns'
const FROM = 'from'
const UNION = 'union'
const WHERE = 'where'
const GROUP = 'group'
const HAVING = 'having'
const ORDER = 'order'
const LIMIT_COUNT = 'limitcount'
const LIMIT_OFFSET = 'limitoffset'
const FOR_UPDATE = 'forupdate'
const INNER_JOIN = 'inner join'
const LEFT_JOIN = 'left join'
const RIGHT_JOIN = 'right join'
const FULL_JOIN = 'full join'
const CROSS_JOIN = 'cross join'
const NATURAL_JOIN = 'natural join'
const SQL_WILDCARD = '*'
const SQL_SELECT = 'SELECT'
const SQL_UNION = 'UNION'
const SQL_UNION_ALL = 'UNION ALL'
const SQL_FROM = 'FROM'
const SQL_WHERE = 'WHERE'
const SQL_DISTINCT = 'DISTINCT'
const SQL_GROUP_BY = 'GROUP BY'
const SQL_ORDER_BY = 'ORDER BY'
const SQL_HAVING = 'HAVING'
const SQL_FOR_UPDATE = 'FOR UPDATE'
const SQL_AND = 'AND'
const SQL_AS = 'AS'
const SQL_OR = 'OR'
const SQL_ON = 'ON'
const SQL_ASC = 'ASC'
const SQL_DESC = 'DESC'

Protected Member Functions

 _join ($type, $name, $cond, $cols, $schema=null)
 _tableCols ($correlationName, $cols, $afterCorrelationName=null)
 _where ($condition, $value=null, $type=null, $bool=true)
 _getDummyTable ()
 _getQuotedSchema ($schema=null)
 _getQuotedTable ($tableName, $correlationName=null)
 _renderDistinct ($sql)
 _renderColumns ($sql)
 _renderFrom ($sql)
 _renderUnion ($sql)
 _renderWhere ($sql)
 _renderGroup ($sql)
 _renderHaving ($sql)
 _renderOrder ($sql)
 _renderLimitoffset ($sql)
 _renderForupdate ($sql)

Protected Attributes

 $_bind = array()
 $_adapter
 $_parts = array()
 $_tableCols = array()

Static Protected Attributes

static $_partsInit
static $_joinTypes
static $_unionTypes

Detailed Description

Definition at line 44 of file Select.php.


Constructor & Destructor Documentation

__construct ( Zend_Db_Adapter_Abstract adapter )

Class constructor

Parameters:
Zend_Db_Adapter_Abstract$adapter

Definition at line 163 of file Select.php.


Member Function Documentation

__call ( method,
array $  args 
)

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

Parameters:
string$method
array$argsOPTIONAL Zend_Db_Table_Select query modifier
Returns:
Zend_Db_Select
Exceptions:
Zend_Db_Select_ExceptionIf an invalid method is called.

Recognize methods for Has-Many cases: findParent<Class>() findParent<Class>By<Rule>() Use the non-greedy pattern repeat modifier e.g. +?

Definition at line 1290 of file Select.php.

__toString (  )

Implements magic method.

Returns:
string This object as a SELECT string.

Definition at line 1328 of file Select.php.

_getDummyTable (  ) [protected]
Returns:
array

Definition at line 1006 of file Select.php.

_getQuotedSchema ( schema = null ) [protected]

Return a quoted schema name

Parameters:
string$schemaThe schema name OPTIONAL
Returns:
string|null

Definition at line 1017 of file Select.php.

_getQuotedTable ( tableName,
correlationName = null 
) [protected]

Return a quoted table name

Parameters:
string$tableNameThe table name
string$correlationNameThe correlation name OPTIONAL
Returns:
string

Definition at line 1032 of file Select.php.

_join ( type,
name,
cond,
cols,
schema = null 
) [protected]

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.

Parameters:
null | string$typeType of join; inner, left, and null are currently supported
array | string | Zend_Db_Expr$nameTable name
string$condJoin on this condition
array | string$colsThe columns to select from the joined table
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object
Exceptions:
Zend_Db_Select_Exception

See also:
Zend_Db_Select_Exception
Zend_Db_Select_Exception

Definition at line 742 of file Select.php.

_joinUsing ( type,
name,
cond,
cols = '*',
schema = null 
)

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

Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 861 of file Select.php.

_renderColumns ( sql ) [protected]

Render DISTINCT clause

Parameters:
string$sqlSQL query
Returns:
string|null

Definition at line 1058 of file Select.php.

_renderDistinct ( sql ) [protected]

Render DISTINCT clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1043 of file Select.php.

_renderForupdate ( sql ) [protected]

Render FOR UPDATE clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1272 of file Select.php.

_renderFrom ( sql ) [protected]

Render FROM clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1091 of file Select.php.

_renderGroup ( sql ) [protected]

Render GROUP clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1179 of file Select.php.

_renderHaving ( sql ) [protected]

Render HAVING clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1198 of file Select.php.

_renderLimitoffset ( sql ) [protected]

Render LIMIT OFFSET clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1242 of file Select.php.

_renderOrder ( sql ) [protected]

Render ORDER clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1213 of file Select.php.

_renderUnion ( sql ) [protected]

Render UNION query

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1139 of file Select.php.

_renderWhere ( sql ) [protected]

Render WHERE clause

Parameters:
string$sqlSQL query
Returns:
string

Definition at line 1164 of file Select.php.

_tableCols ( correlationName,
cols,
afterCorrelationName = null 
) [protected]

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

Parameters:
string$tblThe table/join the columns come from.
array | string$colsThe list of columns; preferably as an array, but possibly as a string containing one column.
bool|stringTrue if it should be prepended, a correlation name if it should be inserted
Returns:
void

Definition at line 908 of file Select.php.

_where ( condition,
value = null,
type = null,
bool = true 
) [protected]

Internal function for creating the where clause

Parameters:
string$condition
string$valueoptional
string$typeoptional
boolean$booltrue = AND, false = OR
Returns:
string clause

Definition at line 980 of file Select.php.

assemble (  )

Converts this object to an SQL SELECT string.

Returns:
string|null This object as a SELECT string. (or null if a string cannot be produced.)

Reimplemented in Zend_Db_Table_Select.

Definition at line 687 of file Select.php.

bind ( bind )

Set bind variables

Parameters:
mixed$bind
Returns:
Zend_Db_Select

Definition at line 185 of file Select.php.

columns ( cols = '*',
correlationName = null 
)

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:
array | string | Zend_Db_Expr$colsThe columns to select from this table.
string$correlationNameCorrelation name of target table. OPTIONAL
Returns:
Zend_Db_Select This Zend_Db_Select object.

See also:
Zend_Db_Select_Exception

Definition at line 243 of file Select.php.

distinct ( flag = true )

Makes the query SELECT DISTINCT.

Parameters:
bool$flagWhether or not the SELECT is DISTINCT (default true).
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 198 of file Select.php.

forUpdate ( flag = true )

Makes the query SELECT FOR UPDATE.

Parameters:
bool$flagWhether or not the SELECT is FOR UPDATE (default true).
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 638 of file Select.php.

from ( name,
cols = '*',
schema = null 
)

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.

Parameters:
array | string | Zend_Db_Expr$nameThe table name or an associative array relating table name to correlation name.
array | string | Zend_Db_Expr$colsThe columns to select from this table.
string$schemaThe schema name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Reimplemented in Zend_Db_Table_Select.

Definition at line 228 of file Select.php.

getAdapter (  )

Gets the Zend_Db_Adapter_Abstract for this particular Zend_Db_Select object.

Returns:
Zend_Db_Adapter_Abstract

Definition at line 721 of file Select.php.

getBind (  )

Get bind variables

Returns:
array

Definition at line 174 of file Select.php.

getPart ( part )

Get part of the structured information for the currect query.

Parameters:
string$part
Returns:
mixed
Exceptions:
Zend_Db_Select_Exception

Definition at line 651 of file Select.php.

group ( spec )

Adds grouping to the query.

Parameters:
array | string$specThe column(s) to group by.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 493 of file Select.php.

having ( cond )

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

Parameters:
string$condThe HAVING condition.
string | Zend_Db_Expr$valA single value to quote into the condition.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 520 of file Select.php.

join ( name,
cond,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
string$condJoin on this condition.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 301 of file Select.php.

joinCross ( name,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 404 of file Select.php.

joinFull ( name,
cond,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
string$condJoin on this condition.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 387 of file Select.php.

joinInner ( name,
cond,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
string$condJoin on this condition.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 322 of file Select.php.

joinLeft ( name,
cond,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
string$condJoin on this condition.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 343 of file Select.php.

joinNatural ( name,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 424 of file Select.php.

joinRight ( name,
cond,
cols = self::SQL_WILDCARD,
schema = null 
)

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:
array | string | Zend_Db_Expr$nameThe table name.
string$condJoin on this condition.
array | string$colsThe columns to select from the joined table.
string$schemaThe database name to specify, if any.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 365 of file Select.php.

limit ( count = null,
offset = null 
)

Sets a limit count and offset to the query.

Parameters:
int$countOPTIONAL The number of rows to return.
int$offsetOPTIONAL Start returning after this many rows.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 609 of file Select.php.

limitPage ( page,
rowCount 
)

Sets the limit and count by page number.

Parameters:
int$pageLimit results to this page number.
int$rowCountUse this many rows per page.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 623 of file Select.php.

order ( spec )

Adds a row order to the query.

Parameters:
mixed$specThe column(s) and direction to order by.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 569 of file Select.php.

orHaving ( cond )

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().

Parameters:
string$condThe HAVING condition.
string$valA single value to quote into the condition.
Returns:
Zend_Db_Select This Zend_Db_Select object.
See also:
having()

Definition at line 547 of file Select.php.

orWhere ( cond,
value = null,
type = null 
)

Adds a WHERE condition to the query by OR.

Otherwise identical to where().

Parameters:
string$condThe WHERE condition.
string$valueOPTIONAL A single value to quote into the condition.
constant$typeOPTIONAL The type of the given value
Returns:
Zend_Db_Select This Zend_Db_Select object.
See also:
where()

Definition at line 480 of file Select.php.

query ( fetchMode = null,
bind = array() 
)

Executes the current select object and returns the result

Parameters:
integer$fetchModeOPTIONAL
mixed$bindAn array of data to bind to the placeholders.
Returns:
PDO_Statement|Zend_Db_Statement

Definition at line 668 of file Select.php.

reset ( part = null )

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

Parameters:
string$partOPTIONAL
Returns:
Zend_Db_Select

Definition at line 705 of file Select.php.

union ( select = array(),
type = self::SQL_UNION 
)

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.

Parameters:
array | string | Zend_Db_Select$selectOne or more select clauses for the UNION.
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 271 of file Select.php.

where ( cond,
value = null,
type = null 
)

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:
string$condThe WHERE condition.
string$valueOPTIONAL A single value to quote into the condition.
constant$typeOPTIONAL The type of the given value
Returns:
Zend_Db_Select This Zend_Db_Select object.

Definition at line 461 of file Select.php.


Field Documentation

$_joinTypes [static, protected]
Initial value:
 array(
        self::INNER_JOIN,
        self::LEFT_JOIN,
        self::RIGHT_JOIN,
        self::FULL_JOIN,
        self::CROSS_JOIN,
        self::NATURAL_JOIN,
    )

Definition at line 124 of file Select.php.

$_partsInit [static, protected]
Initial 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
    )

Definition at line 105 of file Select.php.

$_unionTypes [static, protected]
Initial value:
 array(
        self::SQL_UNION,
        self::SQL_UNION_ALL
    )

Definition at line 138 of file Select.php.


The documentation for this class was generated from the following file: