\Dewdrop\DbSelect

\Dewdrop\Db\Select provides a simple programmatic API for assembilng SQL SELECT statements. It's essentially a direct port of Zend_Db_Select from Zend Framework 1.

The Zend Framework test cases were used to ensure that \Dewdrop\Db\Select generates accurate and functional SQL statements.

Summary

Methods
Properties
Constants
__construct()
getBind()
bind()
distinct()
preColumnsOption()
from()
columns()
union()
join()
joinInner()
joinLeft()
joinRight()
joinFull()
joinCross()
joinNatural()
where()
orWhere()
registerConditionSet()
whereConditionSet()
group()
having()
orHaving()
order()
limit()
limitPage()
forUpdate()
getPart()
quoteWithAlias()
query()
assemble()
reset()
getAdapter()
joinUsingInternal()
__call()
__toString()
No public properties found
DISTINCT
PRE_COLUMNS_OPTIONS
COLUMNS
FROM
UNION
WHERE
WHERE_CONDITION_SETS
GROUP
HAVING
ORDER
LIMIT_COUNT
LIMIT_OFFSET
FOR_UPDATE
INNER_JOIN
LEFT_JOIN
RIGHT_JOIN
FULL_JOIN
CROSS_JOIN
NATURAL_JOIN
SQL_WILDCARD
SQL_SELECT
SQL_UNION
SQL_UNION_ALL
SQL_FROM
SQL_WHERE
SQL_DISTINCT
SQL_GROUP_BY
SQL_ORDER_BY
SQL_HAVING
SQL_FOR_UPDATE
SQL_AND
SQL_AS
SQL_OR
SQL_ON
SQL_ASC
SQL_DESC
joinInternal()
tableCols()
whereInternal()
getDummyTable()
getQuotedSchema()
getQuotedTable()
renderDistinct()
renderPrecolumnsoptions()
renderColumns()
renderFrom()
renderUnion()
renderWhere()
renderWheresets()
renderGroup()
renderHaving()
renderOrder()
renderLimitoffset()
renderForupdate()
$bind
$adapter
$partsInit
$joinTypes
$unionTypes
$parts
$tableCols
N/A
uniqueCorrelation()
No private properties found
N/A

Constants

DISTINCT

DISTINCT

PRE_COLUMNS_OPTIONS

PRE_COLUMNS_OPTIONS

COLUMNS

COLUMNS

FROM

FROM

UNION

UNION

WHERE

WHERE

WHERE_CONDITION_SETS

WHERE_CONDITION_SETS

GROUP

GROUP

HAVING

HAVING

ORDER

ORDER

LIMIT_COUNT

LIMIT_COUNT

LIMIT_OFFSET

LIMIT_OFFSET

FOR_UPDATE

FOR_UPDATE

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_WILDCARD

SQL_SELECT

SQL_SELECT

SQL_UNION

SQL_UNION

SQL_UNION_ALL

SQL_UNION_ALL

SQL_FROM

SQL_FROM

SQL_WHERE

SQL_WHERE

SQL_DISTINCT

SQL_DISTINCT

SQL_GROUP_BY

SQL_GROUP_BY

SQL_ORDER_BY

SQL_ORDER_BY

SQL_HAVING

SQL_HAVING

SQL_FOR_UPDATE

SQL_FOR_UPDATE

SQL_AND

SQL_AND

SQL_AS

SQL_AS

SQL_OR

SQL_OR

SQL_ON

SQL_ON

SQL_ASC

SQL_ASC

SQL_DESC

SQL_DESC

Properties

$bind

$bind : array

Bind variables for query

Type

array

$adapter

$adapter : \Dewdrop\Db\Adapter

The DB adapter that generated this object

Type

\Dewdrop\Db\Adapter

$partsInit

$partsInit : array

The initial values for the $parts array.

NOTE: It is important for the 'FOR_UPDATE' part to be last to ensure maximum compatibility with database adapters.

Type

array

$joinTypes

$joinTypes : array

Specify legal join types.

Type

array

$unionTypes

$unionTypes : array

Specify legal union types.

Type

array

$parts

$parts : array

The component parts of a SELECT statement.

Initialized to the $partsInit array in the constructor.

Type

array

$tableCols

$tableCols : array

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

Type

array

Methods

__construct()

__construct(\Dewdrop\Db\Adapter  $adapter) 

Class constructor

Parameters

\Dewdrop\Db\Adapter $adapter

getBind()

getBind() : array

Get bind variables

Returns

array

bind()

bind(mixed  $bind) : \Dewdrop\Db\Select

Set bind variables

Parameters

mixed $bind

Returns

\Dewdrop\Db\Select

distinct()

distinct(boolean  $flag = true) : \Dewdrop\Db\Select

Makes the query SELECT DISTINCT.

Parameters

boolean $flag

Whether or not the SELECT is DISTINCT (default true).

Returns

\Dewdrop\Db\Select

preColumnsOption()

preColumnsOption(string  $option) : \Dewdrop\Db\Select

Adds an option verbatim to the SQL before the selected columns

Parameters

string $option

Returns

\Dewdrop\Db\Select

from()

from(array|string|\Dewdrop\Db\Expr  $name, array|string|\Dewdrop\Db\Expr  $cols = '*', string  $schema = null) : \Dewdrop\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 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 Expr object, or else an array of strings or 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|\Dewdrop\Db\Expr $name

The table name or an associative array relating table name to correlation name.

array|string|\Dewdrop\Db\Expr $cols

The columns to select from this table.

string $schema

The schema name to specify, if any.

Returns

\Dewdrop\Db\Select

columns()

columns(array|string|\Dewdrop\Db\Expr  $cols = '*', string  $correlationName = null) : \Dewdrop\Db\Select

Specifies the columns used in the FROM clause.

The parameter can be a single string or Expr object, or else an array of strings or Expr objects.

Parameters

array|string|\Dewdrop\Db\Expr $cols

The columns to select from this table.

string $correlationName

Correlation name of target table. OPTIONAL

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

union()

union(array  $select = array(), string  $type = self::SQL_UNION) : \Dewdrop\Db\Select

Adds a UNION clause to the query.

The first parameter has to be an array of \Dewdrop\Db\Select or sql query strings.

$sql1 = $db->select();
$sql2 = "SELECT ...";
$select = $db->select()
     ->union(array($sql1, $sql2))
     ->order("id");

Parameters

array $select

Array of select clauses for the union.

string $type

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

join()

join(array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinInner()

joinInner(array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinLeft()

joinLeft(array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinRight()

joinRight(array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinFull()

joinFull(array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinCross()

joinCross(array|string|\Dewdrop\Db\Expr  $name, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

joinNatural()

joinNatural(array|string|\Dewdrop\Db\Expr  $name, array|string  $cols = self::SQL_WILDCARD, string  $schema = null) : \Dewdrop\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

array|string|\Dewdrop\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.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

where()

where(string  $cond, mixed  $value = null, integer  $type = null) : \Dewdrop\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

string $cond

The WHERE condition.

mixed $value

OPTIONAL The value to quote into the condition.

integer $type

OPTIONAL The type of the given value

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

orWhere()

orWhere(string  $cond, mixed  $value = null, integer  $type = null) : \Dewdrop\Db\Select

Adds a WHERE condition to the query by OR.

Otherwise identical to where().

Parameters

string $cond

The WHERE condition.

mixed $value

OPTIONAL The value to quote into the condition.

integer $type

OPTIONAL The type of the given value

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

registerConditionSet()

registerConditionSet(string  $name, string  $conjunction) : $this

Register a new condition set, useful when you want a block of conditions to be joined to the overall Select, grouped in their own parens. Useful to help users build complex boolean queries that interact predictably with WHERE clause additions you've made in code.

Parameters

string $name
string $conjunction

Throws

\Dewdrop\Db\Select\SelectException

Returns

$this

whereConditionSet()

whereConditionSet(string  $setName, string  $condition, mixed  $value = null, null|string  $type = null, null|integer  $number = null) : $this

Add a new condition to a condition set. The set must be registered with registerConditionSet() prior to calling this method. Otherwise, it behaves just like where().

Parameters

string $setName
string $condition
mixed $value
null|string $type
null|integer $number

Throws

\Dewdrop\Db\Select\SelectException

Returns

$this

group()

group(array|string  $spec) : \Dewdrop\Db\Select

Adds grouping to the query.

Parameters

array|string $spec

The column(s) to group by.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

having()

having(string  $cond, mixed  $value = null, integer  $type = null) : \Dewdrop\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 \where() for an example

Parameters

string $cond

The HAVING condition.

mixed $value

OPTIONAL The value to quote into the condition.

integer $type

OPTIONAL The type of the given value

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

orHaving()

orHaving(string  $cond, mixed  $value = null, integer  $type = null) : \Dewdrop\Db\Select

Adds a HAVING condition to the query by OR.

Otherwise identical to orHaving().

Parameters

string $cond

The HAVING condition.

mixed $value

OPTIONAL The value to quote into the condition.

integer $type

OPTIONAL The type of the given value

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

order()

order(mixed  $spec) : \Dewdrop\Db\Select

Adds a row order to the query.

Parameters

mixed $spec

The column(s) and direction to order by.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

limit()

limit(integer  $count = null, integer  $offset = null) : \Dewdrop\Db\Select

Sets a limit count and offset to the query.

Parameters

integer $count

OPTIONAL The number of rows to return.

integer $offset

OPTIONAL Start returning after this many rows.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

limitPage()

limitPage(integer  $page, integer  $rowCount) : \Dewdrop\Db\Select

Sets the limit and count by page number.

Parameters

integer $page

Limit results to this page number.

integer $rowCount

Use this many rows per page.

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

forUpdate()

forUpdate(boolean  $flag = true) : \Dewdrop\Db\Select

Makes the query SELECT FOR UPDATE.

Parameters

boolean $flag

Whether or not the SELECT is FOR UPDATE (default true).

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

getPart()

getPart(string  $part) : mixed

Get part of the structured information for the currect query.

Parameters

string $part

Throws

\Dewdrop\Db\Select\SelectException

Returns

mixed

quoteWithAlias()

quoteWithAlias(string  $tableName, string  $columnName) : string

This method will quote the supplied table and column name pair, using whatever alias has been defined for the table on this Select object.

This can be useful when you're modifying a Select and you are not sure what aliases have been added for the table you want to reference.

Parameters

string $tableName
string $columnName

Throws

\Dewdrop\Db\Select\SelectException

Returns

string

query()

query(mixed  $bind = array()) : mixed

Executes the current select object and returns the result

Parameters

mixed $bind

An array of data to bind to the placeholders.

Returns

mixed

assemble()

assemble() : string|null

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

reset()

reset(string  $part = null) : \Dewdrop\Db\Select

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

Parameters

string $part

OPTIONAL

Returns

\Dewdrop\Db\Select

getAdapter()

getAdapter() : \Dewdrop\Db\Adapter

Gets the \Dewdrop\Db\Adapter for this particular \Dewdrop\Db\Select object.

Returns

\Dewdrop\Db\Adapter

joinUsingInternal()

joinUsingInternal(string  $type, string  $name, string  $cond, string|array  $cols = '*', string  $schema = null) : \Dewdrop\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

string $type
string $name
string $cond
string|array $cols
string $schema

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object.

__call()

__call(string  $method, array  $args) : \Dewdrop\Db\Select

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

Parameters

string $method
array $args

OPTIONAL Zend_Db_Table_Select query modifier

Throws

\Dewdrop\Db\Select_Exception

If an invalid method is called.

Returns

\Dewdrop\Db\Select

__toString()

__toString() : string

Implements magic method.

Returns

string —

This object as a SELECT string.

joinInternal()

joinInternal(null|string  $type, array|string|\Dewdrop\Db\Expr  $name, string  $cond, array|string  $cols, string  $schema = null) : \Dewdrop\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

null|string $type

Type of join; inner, left, and null are currently supported

array|string|\Dewdrop\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.

Throws

\Dewdrop\Db\Select_Exception

Returns

\Dewdrop\Db\Select

This \Dewdrop\Db\Select object

tableCols()

tableCols(string  $correlationName, array|string  $cols, boolean|string  $afterCorrelationName = null) : void

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

Parameters

string $correlationName

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.

boolean|string $afterCorrelationName

True if it should be prepended, a correlation name if it should be inserted

whereInternal()

whereInternal(string  $condition, mixed  $value = null, string  $type = null, boolean  $bool = true) : string

Internal function for creating the where clause

Parameters

string $condition
mixed $value

optional

string $type

optional

boolean $bool

true = AND, false = OR

Returns

string —

clause

getDummyTable()

getDummyTable() : array

Return an array to serve as a dummy table when needed

Returns

array

getQuotedSchema()

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

Return a quoted schema name

Parameters

string $schema

The schema name OPTIONAL

Returns

string|null

getQuotedTable()

getQuotedTable(string  $tableName, string  $correlationName = null) : string

Return a quoted table name

Parameters

string $tableName

The table name

string $correlationName

The correlation name OPTIONAL

Returns

string

renderDistinct()

renderDistinct(string  $sql) : string

Render DISTINCT clause

Parameters

string $sql

SQL query

Returns

string

renderPrecolumnsoptions()

renderPrecolumnsoptions(string  $sql) : string

Renders pre-columns options

Parameters

string $sql

Returns

string

renderColumns()

renderColumns(string  $sql) : string|null

Render DISTINCT clause

Parameters

string $sql

SQL query

Returns

string|null

renderFrom()

renderFrom(string  $sql) : string

Render FROM clause

Parameters

string $sql

SQL query

Returns

string

renderUnion()

renderUnion(string  $sql) : string

Render UNION query

Parameters

string $sql

SQL query

Returns

string

renderWhere()

renderWhere(string  $sql) : string

Render WHERE clause

Parameters

string $sql

SQL query

Returns

string

renderWheresets()

renderWheresets(string  $sql) : string

Render the condition sets that have been registered with this Select.

Parameters

string $sql

Returns

string

renderGroup()

renderGroup(string  $sql) : string

Render GROUP clause

Parameters

string $sql

SQL query

Returns

string

renderHaving()

renderHaving(string  $sql) : string

Render HAVING clause

Parameters

string $sql

SQL query

Returns

string

renderOrder()

renderOrder(string  $sql) : string

Render ORDER clause

Parameters

string $sql

SQL query

Returns

string

renderLimitoffset()

renderLimitoffset(string  $sql) : string

Render LIMIT OFFSET clause

Parameters

string $sql

SQL query

Returns

string

renderForupdate()

renderForupdate(string  $sql) : string

Render FOR UPDATE clause

Parameters

string $sql

SQL query

Returns

string

uniqueCorrelation()

uniqueCorrelation(string|array  $name) : string

Generate a unique correlation name

Parameters

string|array $name

A qualified identifier.

Returns

string —

A unique correlation name.