Documentation

QueryBuilder
in package

Application

QueryBuilder class is responsible to dynamically create SQL queries.

Important: Verify that every feature you use will work with your database vendor. SQL Query Builder does not attempt to validate the generated SQL at all.

The query builder does no validation whatsoever if certain features even work with the underlying database vendor. Limit queries and joins are NOT applied to UPDATE and DELETE statements even if some vendors such as MySQL support it.

DELETE


    public
    mixed
    DELETE
    = 1

INSERT


    public
    mixed
    INSERT
    = 3

SELECT


    public
    mixed
    SELECT
    = 0

STATE_CLEAN


    public
    mixed
    STATE_CLEAN
    = 1

STATE_DIRTY


    public
    mixed
    STATE_DIRTY
    = 0

UPDATE


    public
    mixed
    UPDATE
    = 2

$boundCounter

The counter of bound parameters used with {@see bindValue).


    private
        int
    $boundCounter
     = 0

$connection

The DBAL Connection.


    private
        Connection
    $connection

$firstResult

The index of the first result to retrieve.


    private
        int
    $firstResult
     = null

$maxResults

The maximum number of results to retrieve.


    private
        int
    $maxResults
     = null

$params

The query parameters.


    private
        array<string|int, mixed>
    $params
     = array()

$paramTypes

The parameter type map of this query.


    private
        array<string|int, mixed>
    $paramTypes
     = array()

$sql

The complete SQL string for this query.


    private
        string
    $sql

$sqlParts


    private
        array<string|int, mixed>
    $sqlParts
     = array('select' => array(), 'from' => array(), 'join' => array(), 'set' => array(), 'where' => null, 'groupBy' => array(), 'having' => null, 'orderBy' => array(), 'values' => array())

The array of SQL parts collected.

$state

The state of the query object. Can be dirty or clean.


    private
        int
    $state
     = self::STATE_CLEAN

$type

The type of query this is. Can be select, update or delete.


    private
        int
    $type
     = self::SELECT

__clone()

Deep clone of all expression objects in the SQL parts.


    public
                __clone() : void

Return values

void —

__construct()

Initializes a new <tt>QueryBuilder</tt>.


    public
                __construct(Connection $connection) : mixed

Parameters

$connection : Connection: The DBAL Connection.

Return values

mixed —

__toString()

Gets a string representation of this QueryBuilder which corresponds to the final SQL query being constructed.


    public
                __toString() : string

Return values

string —

The string representation of this QueryBuilder.

add()

Either appends to or replaces a single, generic query part.


    public
                add(string $sqlPartName, string $sqlPart[, bool $append = false ]) : $this

The available parts are: 'select', 'from', 'set', 'where', 'groupBy', 'having' and 'orderBy'.

Parameters

$sqlPartName : string
$sqlPart : string
$append : bool = false

Return values

$this —

This QueryBuilder instance.

addGroupBy()

Adds a grouping expression to the query.


    public
                addGroupBy(mixed $groupBy) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->groupBy('u.lastLogin');
        ->addGroupBy('u.createdAt')

Parameters

$groupBy : mixed: The grouping expression.

Return values

$this —

This QueryBuilder instance.

addOrderBy()

Adds an ordering to the query results.


    public
                addOrderBy(string $sort[, string $order = null ]) : $this

Parameters

$sort : string: The ordering expression.
$order : string = null: The ordering direction.

Return values

$this —

This QueryBuilder instance.

addSelect()

Adds an item that is to be returned in the query result.


    public
                addSelect([mixed $select = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.id')
        ->addSelect('p.id')
        ->from('users', 'u')
        ->leftJoin('u', 'phonenumbers', 'u.id = p.user_id');

Parameters

$select : mixed = null: The selection expression.

Return values

$this —

This QueryBuilder instance.

andHaving()

Adds a restriction over the groups of the query, forming a logical conjunction with any existing having restrictions.


    public
                andHaving(mixed $having) : $this

Parameters

$having : mixed: The restriction to append.

Return values

$this —

This QueryBuilder instance.

andWhere()

Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.


    public
                andWhere(mixed $where) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u')
        ->from('users', 'u')
        ->where('u.username LIKE ?')
        ->andWhere('u.is_active = 1');

Parameters

$where : mixed: The query restrictions.

Return values

$this —

This QueryBuilder instance.

createNamedParameter()

Creates a new named parameter and bind the value $value to it.


    public
                createNamedParameter(mixed $value[, mixed $type = PDO::PARAM_STR ][, string $placeHolder = null ]) : string

This method provides a shortcut for PDOStatement::bindValue when using prepared statements.

The parameter $value specifies the value that you want to bind. If $placeholder is not provided bindValue() will automatically create a placeholder for you. An automatic placeholder will be of the name ':dcValue1', ':dcValue2' etc.

For more information see

Example: $value = 2; $q->eq( 'id', $q->bindValue( $value ) ); $stmt = $q->executeQuery(); // executed with 'id = 2'

Parameters

$value : mixed
$type : mixed = PDO::PARAM_STR
$placeHolder : string = null: The name to bind with. The string must start with a colon ':'.

Return values

string —

the placeholder name used.

createPositionalParameter()

Creates a new positional parameter and bind the given value to it.


    public
                createPositionalParameter(mixed $value[, int $type = PDO::PARAM_STR ]) : string

Attention: If you are using positional parameters with the query builder you have to be very careful to bind all parameters in the order they appear in the SQL statement , otherwise they get bound in the wrong order which can lead to serious bugs in your code.

Example: $qb = $conn->createQueryBuilder(); $qb->select('u.*') ->from('users', 'u') ->where('u.username = ' . $qb->createPositionalParameter('Foo', PDO::PARAM_STR)) ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', PDO::PARAM_STR))

Parameters

$value : mixed
$type : int = PDO::PARAM_STR

Return values

string —

delete()

Turns the query being built into a bulk delete query that ranges over a certain table.


    public
                delete([string $delete = null ][, string $alias = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->delete('users', 'u')
        ->where('u.id = :user_id');
        ->setParameter(':user_id', 1);

Parameters

$delete : string = null: The table whose rows are subject to the deletion.
$alias : string = null: The table alias used in the constructed query.

Return values

$this —

This QueryBuilder instance.

execute()

Executes this query using the bound parameters and their types.


    public
                execute() : Statement|int

Uses Connection::executeQuery for select statements and Connection::executeUpdate for insert, update and delete statements.

Return values

Statement|int —

expr()

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


    public
                expr() : ExpressionBuilder

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


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

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

Return values

ExpressionBuilder —

from()

Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product with any existing query roots.


    public
                from(string $from[, string|null $alias = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.id')
        ->from('users', 'u')

Parameters

$from : string: The table.
$alias : string|null = null: The alias of the table.

Return values

$this —

This QueryBuilder instance.

getConnection()

Gets the associated DBAL Connection for this query builder.


    public
                getConnection() : Connection

Return values

Connection —

getFirstResult()

Gets the position of the first result the query object was set to retrieve (the "offset").


    public
                getFirstResult() : int

Returns NULL if was not applied to this QueryBuilder.

Return values

int —

The position of the first result.

getMaxResults()

Gets the maximum number of results the query object was set to retrieve (the "limit").


    public
                getMaxResults() : int

Returns NULL if was not applied to this query builder.

Return values

int —

The maximum number of results.

getParameter()

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


    public
                getParameter(mixed $key) : mixed

Parameters

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

Return values

mixed —

The value of the bound parameter.

getParameters()

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


    public
                getParameters() : array<string|int, mixed>

Return values

array<string|int, mixed> —

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

getParameterType()

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


    public
                getParameterType(mixed $key) : mixed

Parameters

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

Return values

mixed —

The value of the bound parameter type.

getParameterTypes()

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


    public
                getParameterTypes() : array<string|int, mixed>

Return values

array<string|int, mixed> —

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

getQueryPart()

Gets a query part by its name.


    public
                getQueryPart(string $queryPartName) : mixed

Parameters

$queryPartName : string

Return values

mixed —

getQueryParts()

Gets all query parts.


    public
                getQueryParts() : array<string|int, mixed>

Return values

array<string|int, mixed> —

getSQL()

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


    public
                getSQL() : string


    $qb = $em->createQueryBuilder()
        ->select('u')
        ->from('User', 'u')
    echo $qb->getSQL(); // SELECT u FROM User u

Return values

string —

The SQL query string.

getState()

Gets the state of this query builder instance.


    public
                getState() : int

Return values

int —

Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

getType()

Gets the type of the currently built query.


    public
                getType() : int

Return values

int —

groupBy()

Specifies a grouping over the results of the query.


    public
                groupBy(mixed $groupBy) : $this

Replaces any previously specified groupings, if any.


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->groupBy('u.id');

Parameters

$groupBy : mixed: The grouping expression.

Return values

$this —

This QueryBuilder instance.

having()

Specifies a restriction over the groups of the query.


    public
                having(mixed $having) : $this

Replaces any previous having restrictions, if any.

Parameters

$having : mixed: The restriction over the groups.

Return values

$this —

This QueryBuilder instance.

innerJoin()

Creates and adds a join to the query.


    public
                innerJoin(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->innerJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

insert()

Turns the query being built into an insert query that inserts into a certain table


    public
                insert([string $insert = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->insert('users')
        ->values(
            array(
                'name' => '?',
                'password' => '?'
            )
        );

Parameters

$insert : string = null: The table into which the rows should be inserted.

Return values

$this —

This QueryBuilder instance.

join()

Creates and adds a join to the query.


    public
                join(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->join('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

leftJoin()

Creates and adds a left join to the query.


    public
                leftJoin(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->leftJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

orderBy()

Specifies an ordering for the query results.


    public
                orderBy(string $sort[, string $order = null ]) : $this

Replaces any previously specified orderings, if any.

Parameters

$sort : string: The ordering expression.
$order : string = null: The ordering direction.

Return values

$this —

This QueryBuilder instance.

orHaving()

Adds a restriction over the groups of the query, forming a logical disjunction with any existing having restrictions.


    public
                orHaving(mixed $having) : $this

Parameters

$having : mixed: The restriction to add.

Return values

$this —

This QueryBuilder instance.

orWhere()

Adds one or more restrictions to the query results, forming a logical disjunction with any previously specified restrictions.


    public
                orWhere(mixed $where) : $this


    $qb = $em->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->where('u.id = 1')
        ->orWhere('u.id = 2');

Parameters

$where : mixed: The WHERE statement.

Return values

$this —

This QueryBuilder instance.

resetQueryPart()

Resets a single SQL part.


    public
                resetQueryPart(string $queryPartName) : $this

Parameters

$queryPartName : string

Return values

$this —

This QueryBuilder instance.

resetQueryParts()

Resets SQL parts.


    public
                resetQueryParts([array<string|int, mixed>|null $queryPartNames = null ]) : $this

Parameters

$queryPartNames : array<string|int, mixed>|null = null

Return values

$this —

This QueryBuilder instance.

rightJoin()

Creates and adds a right join to the query.


    public
                rightJoin(string $fromAlias, string $join, string $alias[, string $condition = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->rightJoin('u', 'phonenumbers', 'p', 'p.is_primary = 1');

Parameters

$fromAlias : string: The alias that points to a from clause.
$join : string: The table name to join.
$alias : string: The alias of the join table.
$condition : string = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

select()

Specifies an item that is to be returned in the query result.


    public
                select([mixed $select = null ]) : $this

Replaces any previously specified selections, if any.


    $qb = $conn->createQueryBuilder()
        ->select('u.id', 'p.id')
        ->from('users', 'u')
        ->leftJoin('u', 'phonenumbers', 'p', 'u.id = p.user_id');

Parameters

$select : mixed = null: The selection expressions.

Return values

$this —

This QueryBuilder instance.

set()

Sets a new value for a column in a bulk update query.


    public
                set(string $key, string $value) : $this


    $qb = $conn->createQueryBuilder()
        ->update('users', 'u')
        ->set('u.password', md5('password'))
        ->where('u.id = ?');

Parameters

$key : string: The column to set.
$value : string: The value, expression, placeholder, etc.

Return values

$this —

This QueryBuilder instance.

setFirstResult()

Sets the position of the first result to retrieve (the "offset").


    public
                setFirstResult(int $firstResult) : $this

Parameters

$firstResult : int: The first result to return.

Return values

$this —

This QueryBuilder instance.

setMaxResults()

Sets the maximum number of results to retrieve (the "limit").


    public
                setMaxResults(int $maxResults) : $this

Parameters

$maxResults : int: The maximum number of results to retrieve.

Return values

$this —

This QueryBuilder instance.

setParameter()

Sets a query parameter for the query being constructed.


    public
                setParameter(string|int $key, mixed $value[, string|null $type = null ]) : $this


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

Parameters

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

Return values

$this —

This QueryBuilder instance.

setParameters()

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


    public
                setParameters(array<string|int, mixed> $params[, array<string|int, mixed> $types = array() ]) : $this


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

Parameters

$params : array<string|int, mixed>: The query parameters to set.
$types : array<string|int, mixed> = array(): The query parameters types to set.

Return values

$this —

This QueryBuilder instance.

setValue()

Sets a value for a column in an insert query.


    public
                setValue(string $column, string $value) : $this


    $qb = $conn->createQueryBuilder()
        ->insert('users')
        ->values(
            array(
                'name' => '?'
            )
        )
        ->setValue('password', '?');

Parameters

$column : string: The column into which the value should be inserted.
$value : string: The value that should be inserted into the column.

Return values

$this —

This QueryBuilder instance.

update()

Turns the query being built into a bulk update query that ranges over a certain table


    public
                update([string $update = null ][, string $alias = null ]) : $this


    $qb = $conn->createQueryBuilder()
        ->update('users', 'u')
        ->set('u.password', md5('password'))
        ->where('u.id = ?');

Parameters

$update : string = null: The table whose rows are subject to the update.
$alias : string = null: The table alias used in the constructed query.

Return values

$this —

This QueryBuilder instance.

values()

Specifies values for an insert query indexed by column names.


    public
                values(array<string|int, mixed> $values) : $this

Replaces any previous values, if any.


    $qb = $conn->createQueryBuilder()
        ->insert('users')
        ->values(
            array(
                'name' => '?',
                'password' => '?'
            )
        );

Parameters

$values : array<string|int, mixed>: The values to specify for the insert query indexed by column names.

Return values

$this —

This QueryBuilder instance.

where()

Specifies one or more restrictions to the query result.


    public
                where(mixed $predicates) : $this

Replaces any previously specified restrictions, if any.


    $qb = $conn->createQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->where('u.id = ?');
// You can optionally programatically build and/or expressions
$qb = $conn->createQueryBuilder();

$or = $qb->expr()->orx();
$or->add($qb->expr()->eq('u.id', 1));
$or->add($qb->expr()->eq('u.id', 2));

$qb->update('users', 'u')
    ->set('u.password', md5('password'))
    ->where($or);

Parameters

$predicates : mixed: The restriction predicates.

Return values

$this —

This QueryBuilder instance.

getFromClauses()


    private
                getFromClauses() : array<string|int, string>

Return values

array<string|int, string> —

getSQLForDelete()

Converts this instance into a DELETE string in SQL.


    private
                getSQLForDelete() : string

Return values

string —

getSQLForInsert()

Converts this instance into an INSERT string in SQL.


    private
                getSQLForInsert() : string

Return values

string —

getSQLForJoins()


    private
                getSQLForJoins(string $fromAlias, array<string|int, mixed> &$knownAliases) : string

Parameters

$fromAlias : string
$knownAliases : array<string|int, mixed>

Return values

string —

getSQLForSelect()


    private
                getSQLForSelect() : string

Return values

string —

getSQLForUpdate()

Converts this instance into an UPDATE string in SQL.


    private
                getSQLForUpdate() : string

Return values

string —

isLimitQuery()


    private
                isLimitQuery() : bool

Return values

bool —

verifyAllAliasesAreKnown()


    private
                verifyAllAliasesAreKnown(array<string|int, mixed> $knownAliases) : mixed

Parameters

$knownAliases : array<string|int, mixed>

Return values

mixed —