PHP Interface Doctrine\DBAL\Query\QueryBuilder

Mostra file Open project: doctrine/dbal Interface Usage Examples

Public Methods

Method	Description
__clone ( ) : void	Deep clone of all expression objects in the SQL parts.
__construct ( Doctrine\DBAL\Connection $connection )	Initializes a new `QueryBuilder`.
__toString ( ) : string	Gets a string representation of this QueryBuilder which corresponds to the final SQL query being constructed.
add ( string $sqlPartName, string $sqlPart, boolean $append = false )	Either appends to or replaces a single, generic query part.
addGroupBy ( mixed $groupBy )	Adds a grouping expression to the query.
addOrderBy ( string $sort, string $order = null )	Adds an ordering to the query results.
addSelect ( mixed $select = null )	Adds an item that is to be returned in the query result.
andHaving ( mixed $having )	Adds a restriction over the groups of the query, forming a logical conjunction with any existing having restrictions.
andWhere ( mixed $where )	Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.
createNamedParameter ( mixed $value, mixed $type = PDO::PARAM_STR, string $placeHolder = null ) : string	Creates a new named parameter and bind the value $value to it.
createPositionalParameter ( mixed $value, integer $type = PDO::PARAM_STR ) : string	Creates a new positional parameter and bind the given value to it.
delete ( string $delete = null, string $alias = null )	Turns the query being built into a bulk delete query that ranges over a certain table.
execute ( ) : Doctrine\DBAL\Driver\Statement \| integer	Executes this query using the bound parameters and their types.
expr ( ) : Doctrine\DBAL\Query\Expression\ExpressionBuilder	Gets an ExpressionBuilder used for object-oriented construction of query expressions.
from ( string $from, string \| null $alias = null )	Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product with any existing query roots.
getConnection ( ) : Doctrine\DBAL\Connection	Gets the associated DBAL Connection for this query builder.
getFirstResult ( ) : integer	Gets the position of the first result the query object was set to retrieve (the "offset").
getMaxResults ( ) : integer	Gets the maximum number of results the query object was set to retrieve (the "limit").
getParameter ( mixed $key ) : mixed	Gets a (previously set) query parameter of the query being constructed.
getParameterType ( mixed $key ) : mixed	Gets a (previously set) query parameter type of the query being constructed.
getParameterTypes ( ) : array	Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
getParameters ( ) : array	Gets all defined query parameters for the query being constructed indexed by parameter index or name.
getQueryPart ( string $queryPartName ) : mixed	Gets a query part by its name.
getQueryParts ( ) : array	Gets all query parts.
getSQL ( ) : string	Gets the complete SQL string formed by the current specifications of this QueryBuilder.
getState ( ) : integer	Gets the state of this query builder instance.
getType ( ) : integer	Gets the type of the currently built query.
groupBy ( mixed $groupBy )	Specifies a grouping over the results of the query.
having ( mixed $having )	Specifies a restriction over the groups of the query.
innerJoin ( string $fromAlias, string $join, string $alias, string $condition = null )	Creates and adds a join to the query.
insert ( string $insert = null )	Turns the query being built into an insert query that inserts into a certain table
join ( string $fromAlias, string $join, string $alias, string $condition = null )	Creates and adds a join to the query.
leftJoin ( string $fromAlias, string $join, string $alias, string $condition = null )	Creates and adds a left join to the query.
orHaving ( mixed $having )	Adds a restriction over the groups of the query, forming a logical disjunction with any existing having restrictions.
orWhere ( mixed $where )	Adds one or more restrictions to the query results, forming a logical disjunction with any previously specified restrictions.
orderBy ( string $sort, string $order = null )	Specifies an ordering for the query results.
resetQueryPart ( string $queryPartName )	Resets a single SQL part.
resetQueryParts ( array \| null $queryPartNames = null )	Resets SQL parts.
rightJoin ( string $fromAlias, string $join, string $alias, string $condition = null )	Creates and adds a right join to the query.
select ( mixed $select = null )	Specifies an item that is to be returned in the query result.
set ( string $key, string $value )	Sets a new value for a column in a bulk update query.
setFirstResult ( integer $firstResult )	Sets the position of the first result to retrieve (the "offset").
setMaxResults ( integer $maxResults )	Sets the maximum number of results to retrieve (the "limit").
setParameter ( string \| integer $key, mixed $value, string \| null $type = null )	Sets a query parameter for the query being constructed.
setParameters ( array $params, array $types = [] )	Sets a collection of query parameters for the query being constructed.
setValue ( string $column, string $value )	Sets a value for a column in an insert query.
update ( string $update = null, string $alias = null )	Turns the query being built into a bulk update query that ranges over a certain table
values ( array $values )	Specifies values for an insert query indexed by column names.
where ( mixed $predicates )	Specifies one or more restrictions to the query result.

Private Methods

Method	Description
getFromClauses ( ) : string[]
getSQLForDelete ( ) : string	Converts this instance into a DELETE string in SQL.
getSQLForInsert ( ) : string	Converts this instance into an INSERT string in SQL.
getSQLForJoins ( string $fromAlias, array &$knownAliases ) : string
getSQLForSelect ( ) : string
getSQLForUpdate ( ) : string	Converts this instance into an UPDATE string in SQL.
isLimitQuery ( ) : boolean
verifyAllAliasesAreKnown ( array $knownAliases )

Method Details

__clone() public method

Deep clone of all expression objects in the SQL parts.

public __clone ( ) : void
return	void

__construct() public method

Initializes a new QueryBuilder.

public __construct ( Doctrine\DBAL\Connection $connection )
$connection	Doctrine\DBAL\Connection	The DBAL Connection.

__toString() public method

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

public __toString ( ) : string
return	string	The string representation of this QueryBuilder.

add() public method

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

public add ( string $sqlPartName, string $sqlPart, boolean $append = false )
$sqlPartName	string
$sqlPart	string
$append	boolean

addGroupBy() public method


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

public addGroupBy ( mixed $groupBy )
$groupBy	mixed	The grouping expression.

addOrderBy() public method

Adds an ordering to the query results.

public addOrderBy ( string $sort, string $order = null )
$sort	string	The ordering expression.
$order	string	The ordering direction.

addSelect() public method


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

public addSelect ( mixed $select = null )
$select	mixed	The selection expression.

andHaving() public method

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

public andHaving ( mixed $having )
$having	mixed	The restriction to append.

andWhere() public method


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

createNamedParameter() public method

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'

See link: http://www.zetacomponents.org

public createNamedParameter ( mixed $value, mixed $type = PDO::PARAM_STR, string $placeHolder = null ) : string
$value	mixed
$type	mixed
$placeHolder	string	The name to bind with. The string must start with a colon ':'.
return	string	the placeholder name used.

createPositionalParameter() public method

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

public createPositionalParameter ( mixed $value, integer $type = PDO::PARAM_STR ) : string
$value	mixed
$type	integer
return	string

delete() public method


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

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

execute() public method

Uses {@see \Connection::executeQuery} for select statements and {@see \Connection::executeUpdate} for insert, update and delete statements.

public execute ( ) : Doctrine\DBAL\Driver\Statement \| integer
return	Doctrine\DBAL\Driver\Statement \| integer

expr() public method

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 expr ( ) : Doctrine\DBAL\Query\Expression\ExpressionBuilder
return	Doctrine\DBAL\Query\Expression\ExpressionBuilder

from() public method


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

public from ( string $from, string \| null $alias = null )
$from	string	The table.
$alias	string \| null	The alias of the table.

getConnection() public method

Gets the associated DBAL Connection for this query builder.

public getConnection ( ) : Doctrine\DBAL\Connection
return	Doctrine\DBAL\Connection

getFirstResult() public method

Returns NULL if {@link setFirstResult} was not applied to this QueryBuilder.

public getFirstResult ( ) : integer
return	integer	The position of the first result.

getMaxResults() public method

Returns NULL if {@link setMaxResults} was not applied to this query builder.

public getMaxResults ( ) : integer
return	integer	The maximum number of results.

getParameter() public method

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

public getParameter ( mixed $key ) : mixed
$key	mixed	The key (index or name) of the bound parameter.
return	mixed	The value of the bound parameter.

getParameterType() public method

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

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

getParameterTypes() public method

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

public getParameterTypes ( ) : array
return	array	The currently defined query parameter types indexed by parameter index or name.

getParameters() public method

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

public getParameters ( ) : array
return	array	The currently defined query parameters indexed by parameter index or name.

getQueryPart() public method

Gets a query part by its name.

public getQueryPart ( string $queryPartName ) : mixed
$queryPartName	string
return	mixed

getQueryParts() public method

Gets all query parts.

public getQueryParts ( ) : array
return	array

getSQL() public method


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

public getSQL ( ) : string
return	string	The SQL query string.

getState() public method

Gets the state of this query builder instance.

public getState ( ) : integer
return	integer	Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

getType() public method

Gets the type of the currently built query.

public getType ( ) : integer
return	integer

groupBy() public method

Replaces any previously specified groupings, if any.


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

public groupBy ( mixed $groupBy )
$groupBy	mixed	The grouping expression.

having() public method

Replaces any previous having restrictions, if any.

public having ( mixed $having )
$having	mixed	The restriction over the groups.

innerJoin() public method


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

public innerJoin ( string $fromAlias, string $join, string $alias, string $condition = null )
$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	The condition for the join.

insert() public method


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

public insert ( string $insert = null )
$insert	string	The table into which the rows should be inserted.

join() public method


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

public join ( string $fromAlias, string $join, string $alias, string $condition = null )
$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	The condition for the join.

leftJoin() public method


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

public leftJoin ( string $fromAlias, string $join, string $alias, string $condition = null )
$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	The condition for the join.

orHaving() public method

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

public orHaving ( mixed $having )
$having	mixed	The restriction to add.

orWhere() public method


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

orderBy() public method

Replaces any previously specified orderings, if any.

public orderBy ( string $sort, string $order = null )
$sort	string	The ordering expression.
$order	string	The ordering direction.

resetQueryPart() public method

Resets a single SQL part.

public resetQueryPart ( string $queryPartName )
$queryPartName	string

resetQueryParts() public method

Resets SQL parts.

public resetQueryParts ( array \| null $queryPartNames = null )
$queryPartNames	array \| null

rightJoin() public method


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

public rightJoin ( string $fromAlias, string $join, string $alias, string $condition = null )
$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	The condition for the join.

select() public method

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');

public select ( mixed $select = null )
$select	mixed	The selection expressions.

set() public method


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

public set ( string $key, string $value )
$key	string	The column to set.
$value	string	The value, expression, placeholder, etc.

setFirstResult() public method

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

public setFirstResult ( integer $firstResult )
$firstResult	integer	The first result to return.

setMaxResults() public method

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

public setMaxResults ( integer $maxResults )
$maxResults	integer	The maximum number of results to retrieve.

setParameter() public method


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

public setParameter ( string \| integer $key, mixed $value, string \| null $type = null )
$key	string \| integer	The parameter position or name.
$value	mixed	The parameter value.
$type	string \| null	One of the PDO::PARAM_* constants.

setParameters() public method


    $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
        ));

public setParameters ( array $params, array $types = [] )
$params	array	The query parameters to set.
$types	array	The query parameters types to set.

setValue() public method


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

public setValue ( string $column, string $value )
$column	string	The column into which the value should be inserted.
$value	string	The value that should be inserted into the column.

update() public method


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

public update ( string $update = null, string $alias = null )
$update	string	The table whose rows are subject to the update.
$alias	string	The table alias used in the constructed query.

values() public method

Replaces any previous values, if any.


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

public values ( array $values )
$values	array	The values to specify for the insert query indexed by column names.

where() public method

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

public where ( mixed $predicates )
$predicates	mixed	The restriction predicates.

public andWhere ( mixed $where )
$where	mixed	The query restrictions.

public orWhere ( mixed $where )
$where	mixed	The WHERE statement.