class QueryBuilder (View source)

Constants

DISTINCT

COLUMNS

FROM

UNION

WHERE

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

REGEX_COLUMN_EXPR

REGEX_COLUMN_EXPR_ORDER

REGEX_COLUMN_EXPR_GROUP

REGEX_SQL_COMMENTS

Properties

protected Connection $_adapter \Doctrine\DBAL\Connection object.
static protected array $_partsInit The initial values for the $_parts array.
static protected array $_joinTypes Specify legal join types.
static protected array $_unionTypes Specify legal union types.
protected array $_parts The component parts of a SELECT statement.
protected array $_tableCols Tracks which columns are being select from each table and join.
protected array $params The query parameters.
protected array $paramTypes The parameter type map of this query.

Methods

__construct(Connection $adapter)

Class constructor

$this
setParameter(string|int $key, mixed $value, string|null $type = null)

Sets a query parameter for the query being constructed.

$this
setParameters(array $params, array $types = [])

Sets a collection of query parameters for the query being constructed.

array
getParameters()

Gets all defined query parameters for the query being constructed indexed by parameter index or name.

mixed
getParameter(mixed $key)

Gets a (previously set) query parameter of the query being constructed.

array
getParameterTypes()

Gets all defined query parameter types for the query being constructed indexed by parameter index or name.

mixed
getParameterType(mixed $key)

Gets a (previously set) query parameter type of the query being constructed.

array
getBind()

Get bind variables

bind(mixed $bind) deprecated

Set bind variables

distinct(bool $flag = true)

Makes the query SELECT DISTINCT.

from(array|string|Expression $name, array|string|Expression $cols = '*', string $schema = null)

Adds a FROM table and optional columns to the query.

columns(array|string|Expression $cols = '*', string $correlationName = null)

Specifies the columns used in the FROM clause.

union($select = [], $type = self::SQL_UNION)

Adds a UNION clause to the query.

join(array|string|Expression $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null)

Adds a JOIN table and columns to the query.

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

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

joinRight(array|string|Expression $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null)

Add a RIGHT OUTER JOIN table and colums to the query.

joinFull(array|string|Expression $name, string $cond, array|string $cols = self::SQL_WILDCARD, string $schema = null)

Add a FULL OUTER JOIN table and colums to the query.

joinCross(array|string|Expression $name, array|string $cols = self::SQL_WILDCARD, string $schema = null)

Add a CROSS JOIN table and colums to the query.

joinNatural(array|string|Expression $name, array|string $cols = self::SQL_WILDCARD, string $schema = null)

Add a NATURAL JOIN table and colums to the query.

where(string $cond, mixed $value = null, int $type = null)

Adds a WHERE condition to the query by AND.

orWhere(string $cond, mixed $value = null, int $type = null)

Adds a WHERE condition to the query by OR.

group(array|string $spec)

Adds grouping to the query.

having(string $cond, mixed $value = null, int $type = null)

Adds a HAVING condition to the query by AND.

orHaving(string $cond, mixed $value = null, int $type = null)

Adds a HAVING condition to the query by OR.

order(mixed $spec)

Adds a row order to the query.

limit(int $count = null, int $offset = null)

Sets a limit count and offset to the query.

limitPage(int $page, int $rowCount)

Sets the limit and count by page number.

forUpdate(bool $flag = true)

Makes the query SELECT FOR UPDATE.

mixed
getPart(string $part)

Get part of the structured information for the current query.

Statement
query(int $fetchMode = null, mixed $bind = [])

Executes the current select object and returns the result

Statement|int
execute()

Executes this query using the bound parameters and their types.

string|null
assemble()

Converts this object to an SQL SELECT string.

reset(string $part = null)

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

Connection|Connection
getAdapter()

Gets the \Doctrine\DBAL\Connection for this particular QueryBuilder object.

_join(null|string $type, array|string|Expression $name, string $cond, array|string $cols, string $schema = null)

Populate the {@link $_parts} 'join' key

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

Handle JOIN.

void
_tableCols($correlationName, $cols, $afterCorrelationName = null)

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

string
_where(string $condition, mixed $value = null, string $type = null, bool $bool = true)

Internal function for creating the where clause

array
_getDummyTable()

No description

string|null
_getQuotedSchema(string $schema = null)

Return a quoted schema name

string
_getQuotedTable(string $tableName, string $correlationName = null)

Return a quoted table name

string
_renderDistinct(string $sql)

Render DISTINCT clause

string|null
_renderColumns(string $sql)

Render DISTINCT clause

string
_renderFrom(string $sql)

Render FROM clause

string
_renderUnion(string $sql)

Render UNION query

string
_renderWhere(string $sql)

Render WHERE clause

string
_renderGroup(string $sql)

Render GROUP clause

string
_renderHaving(string $sql)

Render HAVING clause

string
_renderOrder(string $sql)

Render ORDER clause

string
_renderLimitoffset(string $sql)

Render LIMIT OFFSET clause

string
_renderForupdate(string $sql)

Render FOR UPDATE clause

ExpressionBuilder
expr()

Gets an ExpressionBuilder used for object-oriented construction of query expressions.

__call(string $method, array $args)

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

string
getSQL()

Gets the complete SQL string formed by the current specifications of this QueryBuilder.

string
__toString()

Implements magic method.

__sleep()

No description

Details

__construct(Connection $adapter)

Class constructor

Parameters

Connection $adapter

$this setParameter(string|int $key, mixed $value, string|null $type = null)

Sets a query parameter for the query being constructed.

$qb = $conn->select() ->from(['u' => 'users']) ->columns('*') ->where('u.id = :user_id') ->setParameter(':user_id', 1);

$qb->execute()->fetchAll();

Parameters

string|int $key The parameter position or name.
mixed $value The parameter value.
string|null $type One of the PDO::PARAM_* constants.

Return Value

$this This QueryBuilder instance.

$this setParameters(array $params, array $types = [])

Sets a collection of query parameters for the query being constructed.

$qb = $conn->select() ->from(['u' => 'users']) ->columns('*') ->where('u.id = :user_id1 OR u.id = :user_id2') ->setParameters(array( ':user_id1' => 1, ':user_id2' => 2 ));

$qb->execute()->fetchAll();

Parameters

array $params The query parameters to set.
array $types The query parameters types to set.

Return Value

$this This QueryBuilder instance.

array getParameters()

Gets all defined query parameters for the query being constructed indexed by parameter index or name.

Return Value

array The currently defined query parameters indexed by parameter index or name.

mixed getParameter(mixed $key)

Gets a (previously set) query parameter of the query being constructed.

Parameters

mixed $key The key (index or name) of the bound parameter.

Return Value

mixed The value of the bound parameter.

array getParameterTypes()

Gets all defined query parameter types for the query being constructed indexed by parameter index or name.

Return Value

array The currently defined query parameter types indexed by parameter index or name.

mixed getParameterType(mixed $key)

Gets a (previously set) query parameter type of the query being constructed.

Parameters

mixed $key The key (index or name) of the bound parameter type.

Return Value

mixed The value of the bound parameter type.

array getBind()

Get bind variables

Return Value

array

QueryBuilder bind(mixed $bind) deprecated

deprecated Use setParameters() instead

Set bind variables

Parameters

mixed $bind

Return Value

QueryBuilder

QueryBuilder distinct(bool $flag = true)

Makes the query SELECT DISTINCT.

Parameters

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

Return Value

QueryBuilder

QueryBuilder from(array|string|Expression $name, array|string|Expression $cols = '*', string $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 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 Expression object, or else an array of strings or Expression 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|Expression $name The table name or an associative array relating correlation name to table name.
array|string|Expression $cols The columns to select from this table.
string $schema The schema name to specify, if any.

Return Value

QueryBuilder

QueryBuilder columns(array|string|Expression $cols = '*', string $correlationName = null)

Specifies the columns used in the FROM clause.

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

Parameters

array|string|Expression $cols The columns to select from this table.
string $correlationName Correlation name of target table. OPTIONAL

Return Value

QueryBuilder

QueryBuilder union($select = [], $type = self::SQL_UNION)

Adds a UNION clause to the query.

The first parameter has to be an array of QueryBuilder or sql query strings.

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

Parameters

$select
$type

Return Value

QueryBuilder

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

Return Value

QueryBuilder

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

Return Value

QueryBuilder

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

Return Value

QueryBuilder

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

Return Value

QueryBuilder

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

Return Value

QueryBuilder

QueryBuilder joinCross(array|string|Expression $name, array|string $cols = self::SQL_WILDCARD, string $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|Expression $name The table name.
array|string $cols The columns to select from the joined table.
string $schema The database name to specify, if any.

Return Value

QueryBuilder

QueryBuilder joinNatural(array|string|Expression $name, array|string $cols = self::SQL_WILDCARD, string $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|Expression $name The table name.
array|string $cols The columns to select from the joined table.
string $schema The database name to specify, if any.

Return Value

QueryBuilder

QueryBuilder where(string $cond, mixed $value = null, int $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 $cond The WHERE condition.
mixed $value OPTIONAL The value to quote into the condition.
int $type OPTIONAL The type of the given value

Return Value

QueryBuilder

QueryBuilder orWhere(string $cond, mixed $value = null, int $type = null)

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.
int $type OPTIONAL The type of the given value

Return Value

QueryBuilder

See also

where()

QueryBuilder group(array|string $spec)

Adds grouping to the query.

Parameters

array|string $spec The column(s) to group by.

Return Value

QueryBuilder

QueryBuilder having(string $cond, mixed $value = null, int $type = null)

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

string $cond The HAVING condition.
mixed $value OPTIONAL The value to quote into the condition.
int $type OPTIONAL The type of the given value

Return Value

QueryBuilder

QueryBuilder orHaving(string $cond, mixed $value = null, int $type = null)

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.
int $type OPTIONAL The type of the given value

Return Value

QueryBuilder

See also

having()

QueryBuilder order(mixed $spec)

Adds a row order to the query.

Parameters

mixed $spec The column(s) and direction to order by.

Return Value

QueryBuilder

QueryBuilder limit(int $count = null, int $offset = null)

Sets a limit count and offset to the query.

Parameters

int $count OPTIONAL The number of rows to return.
int $offset OPTIONAL Start returning after this many rows.

Return Value

QueryBuilder

QueryBuilder limitPage(int $page, int $rowCount)

Sets the limit and count by page number.

Parameters

int $page Limit results to this page number.
int $rowCount Use this many rows per page.

Return Value

QueryBuilder

QueryBuilder forUpdate(bool $flag = true)

Makes the query SELECT FOR UPDATE.

Parameters

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

Return Value

QueryBuilder

mixed getPart(string $part)

Get part of the structured information for the current query.

Parameters

string $part

Return Value

mixed

Exceptions

Exception

Statement query(int $fetchMode = null, mixed $bind = [])

Executes the current select object and returns the result

Parameters

int $fetchMode OPTIONAL
mixed $bind An array of data to bind to the placeholders.

Return Value

Statement

Statement|int execute()

Executes this query using the bound parameters and their types.

Uses {see Connection::executeQuery}

Return Value

Statement|int

string|null assemble()

Converts this object to an SQL SELECT string.

Return Value

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

QueryBuilder reset(string $part = null)

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

Parameters

string $part OPTIONAL

Return Value

QueryBuilder

Connection|Connection getAdapter()

Gets the \Doctrine\DBAL\Connection for this particular QueryBuilder object.

Return Value

Connection|Connection

protected QueryBuilder _join(null|string $type, array|string|Expression $name, string $cond, array|string $cols, string $schema = null)

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|Expression $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.

Return Value

QueryBuilder

Exceptions

Exception

QueryBuilder _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

Parameters

$type
$name
$cond
$cols
$schema

Return Value

QueryBuilder

protected void _tableCols($correlationName, $cols, $afterCorrelationName = null)

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

Parameters

$correlationName
$cols
$afterCorrelationName

Return Value

void

protected string _where(string $condition, mixed $value = null, string $type = null, bool $bool = true)

Internal function for creating the where clause

Parameters

string $condition
mixed $value optional
string $type optional
bool $bool true = AND, false = OR

Return Value

string clause

protected array _getDummyTable()

Return Value

array

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

Return a quoted schema name

Parameters

string $schema The schema name OPTIONAL

Return Value

string|null

protected string _getQuotedTable(string $tableName, string $correlationName = null)

Return a quoted table name

Parameters

string $tableName The table name
string $correlationName The correlation name OPTIONAL

Return Value

string

protected string _renderDistinct(string $sql)

Render DISTINCT clause

Parameters

string $sql SQL query

Return Value

string

protected string|null _renderColumns(string $sql)

Render DISTINCT clause

Parameters

string $sql SQL query

Return Value

string|null

protected string _renderFrom(string $sql)

Render FROM clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderUnion(string $sql)

Render UNION query

Parameters

string $sql SQL query

Return Value

string

protected string _renderWhere(string $sql)

Render WHERE clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderGroup(string $sql)

Render GROUP clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderHaving(string $sql)

Render HAVING clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderOrder(string $sql)

Render ORDER clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderLimitoffset(string $sql)

Render LIMIT OFFSET clause

Parameters

string $sql SQL query

Return Value

string

protected string _renderForupdate(string $sql)

Render FOR UPDATE clause

Parameters

string $sql SQL query

Return Value

string

ExpressionBuilder expr()

Gets an ExpressionBuilder used for object-oriented construction of query expressions.

This producer method is intended for convenient inline usage. Example:

$qb = $conn->select() ->where($qb->expr()->eq('u.id', 1));

For more complex expression construction, consider storing the expression builder object in a local variable.

Return Value

ExpressionBuilder

QueryBuilder __call(string $method, array $args)

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

Parameters

string $method
array $args OPTIONAL query modifier

Return Value

QueryBuilder

Exceptions

Exception If an invalid method is called.

string getSQL()

Gets the complete SQL string formed by the current specifications of this QueryBuilder.

$qb = $conn->select() ->from(['u' => 'users']) ->columns('*') ->where('u.id = :user_id');

echo $qb->getSQL(); // SELECT u.* FROM users u WHERE u.id = :user_id

Return Value

string The SQL query string.

string __toString()

Implements magic method.

Return Value

string This object as a SELECT string.

__sleep()