Class Doctrine\DBAL\Query\QueryBuilder


					
	public

__construct(Connection $connection)

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

Initializes a new QueryBuilder.

Parameters

$connection

The DBAL Connection.

#


					
	public

expr(): ExpressionBuilder

Gets an ExpressionBuilder used for object-oriented construction of query expressions. This producer method is intended…

Gets an ExpressionBuilder used for object-oriented construction of query expressions. 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.

#


					
	public

getType(): int

Gets the type of the currently built query.

#


					
	public

getConnection(): Connection

Gets the associated DBAL Connection for this query builder.

#


					
	public

getState(): int

Gets the state of this query builder instance.

Returns

Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

#


					
	public

execute(): Result|int|string

Executes this query using the bound parameters and their types.

Throws

Doctrine\DBAL\Exception

#


					
	public

getSQL(): string

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


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

Returns

The SQL query string.

#


					
	public

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

Sets a query parameter for the query being constructed.


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

Parameters

`$key`	Parameter position or name
`$value`	Parameter value
`$type`	Parameter type

Returns

This QueryBuilder instance.

#


					
	public

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

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


    $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`	Parameters to set
`$types`	Parameter types

Returns

This QueryBuilder instance.

#


					
	public

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

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

Returns

The currently defined query parameters

#


					
	public

getParameter(mixed $key): mixed

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

Parameters

$key

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

Returns

The value of the bound parameter.

#


					
	public

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

Returns

The currently defined query parameter types

#


					
	public

getParameterType(int|string $key): int|string|Type|null

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

Parameters

$key

The key of the bound parameter type

Returns

The value of the bound parameter type

#


					
	public

setFirstResult(int $firstResult): $this

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

Parameters

$firstResult

The first result to return.

Returns

This QueryBuilder instance.

#


					
	public

getFirstResult(): int

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

Returns

The position of the first result.

#


					
	public

setMaxResults(int|null $maxResults): $this

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

Parameters

$maxResults

The maximum number of results to retrieve or NULL to retrieve all results.

Returns

This QueryBuilder instance.

#


					
	public

getMaxResults(): int|null

Gets the maximum number of results the query object was set to retrieve (the "limit"). Returns NULL if all results will…

Gets the maximum number of results the query object was set to retrieve (the "limit"). Returns NULL if all results will be returned.

Returns

The maximum number of results.

#


					
	public

add(string $sqlPartName, mixed $sqlPart, bool $append = false): $this

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

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

Returns

This QueryBuilder instance.

#


					
	public

select(string|string[]|null $select = null): $this

Specifies an item that is to be returned in the query result. Replaces any previously specified selections, if any.

USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument.


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

Parameters

$select

The selection expression. USING AN ARRAY OR NULL IS DEPRECATED. Pass each value as an individual argument.

Returns

This QueryBuilder instance.

#


					
	public

distinct(): $this

Adds DISTINCT to the query.


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

Returns

This QueryBuilder instance.

#


					
	public

addSelect(string|string[]|null $select = null): $this

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

USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument.


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

Parameters

$select

The selection expression. USING AN ARRAY OR NULL IS DEPRECATED. Pass each value as an individual argument.

Returns

This QueryBuilder instance.

#


					
	public

delete(string $delete = null, string $alias = null): $this

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


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

Parameters

`$delete`	The table whose rows are subject to the deletion.
`$alias`	The table alias used in the constructed query.

Returns

This QueryBuilder instance.

#


					
	public

update(string $update = null, string $alias = null): $this

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


    $qb = $conn->createQueryBuilder()
        ->update('counters', 'c')
        ->set('c.value', 'c.value + 1')
        ->where('c.id = ?');

Parameters

`$update`	The table whose rows are subject to the update.
`$alias`	The table alias used in the constructed query.

Returns

This QueryBuilder instance.

#


					
	public

insert(string $insert = null): $this

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


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

Parameters

$insert

The table into which the rows should be inserted.

Returns

This QueryBuilder instance.

#


					
	public

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

Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product…

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


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

Parameters

`$from`	The table.
`$alias`	The alias of the table.

Returns

This QueryBuilder instance.

#


					
	public

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

Creates and adds a join to the query.


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

Parameters

`$fromAlias`	The alias that points to a from clause.
`$join`	The table name to join.
`$alias`	The alias of the join table.
`$condition`	The condition for the join.

Returns

This QueryBuilder instance.

#


					
	public

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

Creates and adds a join to the query.


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

Parameters

`$fromAlias`	The alias that points to a from clause.
`$join`	The table name to join.
`$alias`	The alias of the join table.
`$condition`	The condition for the join.

Returns

This QueryBuilder instance.

#


					
	public

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

Creates and adds a left join to the query.


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

Parameters

`$fromAlias`	The alias that points to a from clause.
`$join`	The table name to join.
`$alias`	The alias of the join table.
`$condition`	The condition for the join.

Returns

This QueryBuilder instance.

#


					
	public

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

Creates and adds a right join to the query.


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

Parameters

`$fromAlias`	The alias that points to a from clause.
`$join`	The table name to join.
`$alias`	The alias of the join table.
`$condition`	The condition for the join.

Returns

This QueryBuilder instance.

#


					
	public

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

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


    $qb = $conn->createQueryBuilder()
        ->update('counters', 'c')
        ->set('c.value', 'c.value + 1')
        ->where('c.id = ?');

Parameters

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

Returns

This QueryBuilder instance.

#


					
	public

where(mixed $predicates): $this

Specifies one or more restrictions to the query result. Replaces any previously specified restrictions, if any.


    $qb = $conn->createQueryBuilder()
        ->select('c.value')
        ->from('counters', 'c')
        ->where('c.id = ?');
// You can optionally programatically build and/or expressions
$qb = $conn->createQueryBuilder();
$or = $qb->expr()->orx();
$or->add($qb->expr()->eq('c.id', 1));
$or->add($qb->expr()->eq('c.id', 2));

$qb->update('counters', 'c') ->set('c.value', 'c.value + 1') ->where($or);

Parameters

$predicates

The restriction predicates.

Returns

This QueryBuilder instance.

#


					
	public

andWhere(mixed $where): $this

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

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


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

Parameters

$where

The query restrictions.

Returns

This QueryBuilder instance.

#


					
	public

orWhere(mixed $where): $this

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

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


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

Parameters

$where

The WHERE statement.

Returns

This QueryBuilder instance.

#


					
	public

groupBy(string|string[] $groupBy): $this

Specifies a grouping over the results of the query. Replaces any previously specified groupings, if any.

USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument.


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

Parameters

$groupBy

The grouping expression. USING AN ARRAY IS DEPRECATED. Pass each value as an individual argument.

Returns

This QueryBuilder instance.

#


					
	public

addGroupBy(string|string[] $groupBy): $this

Adds a grouping expression to the query.

USING AN ARRAY ARGUMENT IS DEPRECATED. Pass each value as an individual argument.


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

Parameters

$groupBy

The grouping expression. USING AN ARRAY IS DEPRECATED. Pass each value as an individual argument.

Returns

This QueryBuilder instance.

#


					
	public

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

Sets a value for a column in an insert query.


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

Parameters

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

Returns

This QueryBuilder instance.

#


					
	public

values(mixed[] $values): $this

Specifies values for an insert query indexed by column names. Replaces any previous values, if any.


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

Parameters

$values

The values to specify for the insert query indexed by column names.

Returns

This QueryBuilder instance.

#


					
	public

having(mixed $having): $this

Specifies a restriction over the groups of the query. Replaces any previous having restrictions, if any.

Parameters

$having

The restriction over the groups.

Returns

This QueryBuilder instance.

#


					
	public

andHaving(mixed $having): $this

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

Parameters

$having

The restriction to append.

Returns

This QueryBuilder instance.

#


					
	public

orHaving(mixed $having): $this

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

Parameters

$having

The restriction to add.

Returns

This QueryBuilder instance.

#


					
	public

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

Specifies an ordering for the query results. Replaces any previously specified orderings, if any.

Parameters

`$sort`	The ordering expression.
`$order`	The ordering direction.

Returns

This QueryBuilder instance.

#


					
	public

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

Adds an ordering to the query results.

Parameters

`$sort`	The ordering expression.
`$order`	The ordering direction.

Returns

This QueryBuilder instance.

#


					
	public

getQueryPart(string $queryPartName): mixed

Gets a query part by its name.

#


					
	public

getQueryParts(): mixed[]

Gets all query parts.

#


					
	public

resetQueryParts(string[]|null $queryPartNames = null): $this

Resets SQL parts.

Returns

This QueryBuilder instance.

#


					
	public

resetQueryPart(string $queryPartName): $this

Resets a single SQL part.

Returns

This QueryBuilder instance.

#


					
	public

__toString(): string

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

Returns

The string representation of this QueryBuilder.

#


					
	public

createNamedParameter(
	mixed $value,
	int|string|Type|null $type = ParameterType::STRING,
	string $placeHolder = null,
): string

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

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 {@link http://php.net/pdostatement-bindparam}

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

Parameters

$placeHolder

The name to bind with. The string must start with a colon ':'.

Returns

the placeholder name used.

#


					
	public

createPositionalParameter(mixed $value, int|string|Type|null $type = ParameterType::STRING): string

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

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', ParameterType::STRING)) ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', ParameterType::STRING))

#


					
	public

__clone(): void

Deep clone of all expression objects in the SQL parts.

#

Constants
`public`	`SELECT = 0`	#
`public`	`DELETE = 1`	#
`public`	`UPDATE = 2`	#
`public`	`INSERT = 3`	#
`public`	`STATE_DIRTY = 0`	#
`public`	`STATE_CLEAN = 1`	#

Namespaces

Classes

Parameters

Returns

Throws

Returns

Parameters

Returns

Parameters

Returns

Returns

Parameters

Returns

Returns

Parameters

Returns

Parameters

Returns

Returns

Parameters

Returns

Returns

Returns

Parameters

Returns

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Parameters

Returns

Returns

Returns

Returns

Parameters

Returns