Nextcloud PHP API (master)

IQueryBuilder
in

Application

This class provides a wrapper around Doctrine's QueryBuilder

Constants

MAX_ROW_DELETION = 100000
PARAM_BOOL = \Doctrine\DBAL\Types\Types::BOOLEAN
PARAM_DATE = \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE
PARAM_DATE_IMMUTABLE = \Doctrine\DBAL\Types\Types::DATE_IMMUTABLE: For passing a \DateTime instance when only interested in the date part (without timezone support)
PARAM_DATE_MUTABLE = \Doctrine\DBAL\Types\Types::DATE_MUTABLE: For passing a \DateTime instance when only interested in the date part (without timezone support)
PARAM_DATETIME_IMMUTABLE = \Doctrine\DBAL\Types\Types::DATETIME_IMMUTABLE: For passing a \DateTime instance (without timezone support)
PARAM_DATETIME_MUTABLE = \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE: For passing a \DateTime instance (without timezone support)
PARAM_DATETIME_TZ_IMMUTABLE = \Doctrine\DBAL\Types\Types::DATETIMETZ_IMMUTABLE: For passing a \DateTime instance with timezone support
PARAM_DATETIME_TZ_MUTABLE = \Doctrine\DBAL\Types\Types::DATETIMETZ_MUTABLE: For passing a \DateTime instance with timezone support
PARAM_INT = \Doctrine\DBAL\ParameterType::INTEGER
PARAM_INT_ARRAY = \Doctrine\DBAL\ArrayParameterType::INTEGER
PARAM_JSON = 'json'
PARAM_LOB = \Doctrine\DBAL\ParameterType::LARGE_OBJECT
PARAM_NULL = \Doctrine\DBAL\ParameterType::NULL
PARAM_STR = \Doctrine\DBAL\ParameterType::STRING
PARAM_STR_ARRAY = \Doctrine\DBAL\ArrayParameterType::STRING
PARAM_TIME_IMMUTABLE = \Doctrine\DBAL\Types\Types::TIME_MUTABLE: For passing a \DateTimeImmutable instance when only interested in the time part (without timezone support)
PARAM_TIME_MUTABLE = \Doctrine\DBAL\Types\Types::TIME_MUTABLE: For passing a \DateTime instance when only interested in the time part (without timezone support)

Methods

addGroupBy() : $this: Adds a grouping expression to the query.
addOrderBy() : $this: Adds an ordering to the query results.
addSelect() : $this: Adds an item that is to be returned in the query result.
andHaving() : $this: Adds a restriction over the groups of the query, forming a logical conjunction with any existing having restrictions.
andWhere() : $this: Adds one or more restrictions to the query results, forming a logical conjunction with any previously specified restrictions.
automaticTablePrefix() : mixed: Enable/disable automatic prefixing of table names with the oc_ prefix
createFunction() : IQueryFunction: Creates a new function
createNamedParameter() : IParameter: Creates a new named parameter and bind the value $value to it.
createParameter() : IParameter: Creates a new parameter
createPositionalParameter() : IParameter: Creates a new positional parameter and bind the given value to it.
delete() : $this: Turns the query being built into a bulk delete query that ranges over a certain table.
execute() : IResult|int: Executes this query using the bound parameters and their types.
executeQuery() : IResult: Execute for select statements
executeStatement() : int: Execute insert, update and delete statements
expr() : IExpressionBuilder: Gets an ExpressionBuilder used for object-oriented construction of query expressions.
from() : $this: Creates and adds a query root corresponding to the table identified by the given alias, forming a cartesian product with any existing query roots.
func() : IFunctionBuilder: Gets an FunctionBuilder used for object-oriented construction of query functions.
getColumnName() : string: Returns the column name quoted and with table alias prefix as needed by the implementation
getConnection() : IDBConnection: Gets the associated DBAL Connection for this query builder.
getFirstResult() : int: Gets the position of the first result the query object was set to retrieve (the "offset").
getLastInsertId() : int: Used to get the id of the last inserted element
getMaxResults() : int|null: Gets the maximum number of results the query object was set to retrieve (the "limit").
getOutputColumns() : array<string|int, mixed>: Get a list of column names that are expected in the query output
getParameter() : mixed: Gets a (previously set) query parameter of the query being constructed.
getParameters() : array<string|int, mixed>: Gets all defined query parameters for the query being constructed indexed by parameter index or name.
getParameterType() : mixed: Gets a (previously set) query parameter type of the query being constructed.
getParameterTypes() : array<string|int, mixed>: Gets all defined query parameter types for the query being constructed indexed by parameter index or name.
getQueryPart() : mixed: Gets a query part by its name.
getQueryParts() : array<string|int, mixed>: Gets all query parts.
getSQL() : string: Gets the complete SQL string formed by the current specifications of this QueryBuilder.
getState() : int: Gets the state of this query builder instance.
getTableName() : string: Returns the table name quoted and with database prefix as needed by the implementation.
getType() : int: Gets the type of the currently built query.
groupBy() : $this: Specifies a grouping over the results of the query.
having() : $this: Specifies a restriction over the groups of the query.
hintShardKey() : $this: Provide a hint for the shard key for queries where this can't be detected otherwise
innerJoin() : $this: Creates and adds a join to the query.
insert() : $this: Turns the query being built into an insert query that inserts into a certain table
join() : $this: Creates and adds a join to the query.
leftJoin() : $this: Creates and adds a left join to the query.
orderBy() : $this: Specifies an ordering for the query results.
orHaving() : $this: Adds a restriction over the groups of the query, forming a logical disjunction with any existing having restrictions.
orWhere() : $this: Adds one or more restrictions to the query results, forming a logical disjunction with any previously specified restrictions.
prefixTableName() : string: Returns the table name with database prefix as needed by the implementation
resetQueryPart() : $this: Resets a single SQL part.
resetQueryParts() : $this: Resets SQL parts.
rightJoin() : $this: Creates and adds a right join to the query.
runAcrossAllShards() : $this: Set the query to run across all shards if sharding is enabled.
select() : $this: Specifies an item that is to be returned in the query result.
selectAlias() : $this: Specifies an item that is to be returned with a different name in the query result.
selectDistinct() : $this: Specifies an item that is to be returned uniquely in the query result.
set() : $this: Sets a new value for a column in a bulk update query.
setFirstResult() : $this: Sets the position of the first result to retrieve (the "offset").
setMaxResults() : $this: Sets the maximum number of results to retrieve (the "limit").
setParameter() : $this: Sets a query parameter for the query being constructed.
setParameters() : $this: Sets a collection of query parameters for the query being constructed.
setValue() : $this: Sets a value for a column in an insert query.
update() : $this: Turns the query being built into a bulk update query that ranges over a certain table
values() : $this: Specifies values for an insert query indexed by column names.
where() : $this: Specifies one or more restrictions to the query result.

MAX_ROW_DELETION


    public
        mixed
    MAX_ROW_DELETION
    = 100000

PARAM_BOOL


    public
        mixed
    PARAM_BOOL
    = \Doctrine\DBAL\Types\Types::BOOLEAN

PARAM_DATE

use PARAM_DATETIME_MUTABLE instead


    public
        mixed
    PARAM_DATE
    = \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE

PARAM_DATE_IMMUTABLE

For passing a \DateTime instance when only interested in the date part (without timezone support)


    public
        mixed
    PARAM_DATE_IMMUTABLE
    = \Doctrine\DBAL\Types\Types::DATE_IMMUTABLE

PARAM_DATE_MUTABLE

For passing a \DateTime instance when only interested in the date part (without timezone support)


    public
        mixed
    PARAM_DATE_MUTABLE
    = \Doctrine\DBAL\Types\Types::DATE_MUTABLE

PARAM_DATETIME_IMMUTABLE

For passing a \DateTime instance (without timezone support)


    public
        mixed
    PARAM_DATETIME_IMMUTABLE
    = \Doctrine\DBAL\Types\Types::DATETIME_IMMUTABLE

PARAM_DATETIME_MUTABLE

For passing a \DateTime instance (without timezone support)


    public
        mixed
    PARAM_DATETIME_MUTABLE
    = \Doctrine\DBAL\Types\Types::DATETIME_MUTABLE

PARAM_DATETIME_TZ_IMMUTABLE

For passing a \DateTime instance with timezone support


    public
        mixed
    PARAM_DATETIME_TZ_IMMUTABLE
    = \Doctrine\DBAL\Types\Types::DATETIMETZ_IMMUTABLE

PARAM_DATETIME_TZ_MUTABLE

For passing a \DateTime instance with timezone support


    public
        mixed
    PARAM_DATETIME_TZ_MUTABLE
    = \Doctrine\DBAL\Types\Types::DATETIMETZ_MUTABLE

PARAM_INT


    public
        mixed
    PARAM_INT
    = \Doctrine\DBAL\ParameterType::INTEGER

PARAM_INT_ARRAY


    public
        mixed
    PARAM_INT_ARRAY
    = \Doctrine\DBAL\ArrayParameterType::INTEGER

PARAM_JSON


    public
        mixed
    PARAM_JSON
    = 'json'

PARAM_LOB


    public
        mixed
    PARAM_LOB
    = \Doctrine\DBAL\ParameterType::LARGE_OBJECT

PARAM_NULL


    public
        mixed
    PARAM_NULL
    = \Doctrine\DBAL\ParameterType::NULL

PARAM_STR


    public
        mixed
    PARAM_STR
    = \Doctrine\DBAL\ParameterType::STRING

PARAM_STR_ARRAY


    public
        mixed
    PARAM_STR_ARRAY
    = \Doctrine\DBAL\ArrayParameterType::STRING

PARAM_TIME_IMMUTABLE

For passing a \DateTimeImmutable instance when only interested in the time part (without timezone support)


    public
        mixed
    PARAM_TIME_IMMUTABLE
    = \Doctrine\DBAL\Types\Types::TIME_MUTABLE

PARAM_TIME_MUTABLE

For passing a \DateTime instance when only interested in the time part (without timezone support)


    public
        mixed
    PARAM_TIME_MUTABLE
    = \Doctrine\DBAL\Types\Types::TIME_MUTABLE

addGroupBy()

Adds a grouping expression to the query.


    public
                    addGroupBy(mixed ...$groupBy) : $this


    $qb = $conn->getQueryBuilder()
        ->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|ILiteral|IParameter|IQueryFunction $sort[, string $order = null ]) : $this

Parameters

$sort : string|ILiteral|IParameter|IQueryFunction: 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) : $this


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

Parameters

$select : mixed: 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->getQueryBuilder()
        ->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.

automaticTablePrefix()

Enable/disable automatic prefixing of table names with the oc_ prefix


    public
                    automaticTablePrefix(bool $enabled) : mixed

Parameters

$enabled : bool: If set to true table names will be prefixed with the owncloud database prefix automatically.

createFunction()

Creates a new function


    public
                    createFunction(string $call) : IQueryFunction

Attention: Column names inside the call have to be quoted before hand

Example: $qb = $conn->getQueryBuilder(); $qb->select($qb->createFunction('COUNT()')) ->from('users', 'u') echo $qb->getSQL(); // SELECT COUNT() FROM users u $qb = $conn->getQueryBuilder(); $qb->select($qb->createFunction('COUNT(column)')) ->from('users', 'u') echo $qb->getSQL(); // SELECT COUNT(column) FROM users u

Parameters

$call : string

Return values

IQueryFunction

createNamedParameter()

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


    public
                    createNamedParameter(mixed $value[, self::PARAM_* $type = self::PARAM_STR ][, string $placeHolder = null ]) : IParameter

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 : self::PARAM_* = self::PARAM_STR
$placeHolder : string = null: The name to bind with. The string must start with a colon ':'.

Return values

IParameter

createParameter()

Creates a new parameter


    public
                    createParameter(string $name) : IParameter

Example: $qb = $conn->getQueryBuilder(); $qb->select('u.*') ->from('users', 'u') ->where('u.username = ' . $qb->createParameter('name')) ->setParameter('name', 'Bar', IQueryBuilder::PARAM_STR))

Parameters

$name : string

Return values

IParameter

createPositionalParameter()

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


    public
                    createPositionalParameter(mixed $value[, self::PARAM_* $type = self::PARAM_STR ]) : IParameter

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->getQueryBuilder(); $qb->select('u.*') ->from('users', 'u') ->where('u.username = ' . $qb->createPositionalParameter('Foo', IQueryBuilder::PARAM_STR)) ->orWhere('u.username = ' . $qb->createPositionalParameter('Bar', IQueryBuilder::PARAM_STR))

Parameters

$value : mixed
$type : self::PARAM_* = self::PARAM_STR

Return values

IParameter

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->getQueryBuilder()
        ->delete('users')
        ->where('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([IDBConnection|null $connection = null ]) : IResult|int

Use executeQuery or executeStatement

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

Warning: until Nextcloud 20, this method could return a \Doctrine\DBAL\Driver\Statement but since that interface changed in a breaking way the adapter \OCP\DB\QueryBuilder\IStatement is returned to bridge old code to the new API

Parameters

$connection : IDBConnection|null = null: (optional) the connection to run the query against. since 30.0

Return values

IResult|int

executeQuery()

Execute for select statements


    public
                    executeQuery([IDBConnection|null $connection = null ]) : IResult

Parameters

$connection : IDBConnection|null = null: (optional) the connection to run the query against. since 30.0

Return values

IResult

executeStatement()

Execute insert, update and delete statements


    public
                    executeStatement([IDBConnection|null $connection = null ]) : int

Parameters

$connection : IDBConnection|null = null: (optional) the connection to run the query against. since 30.0

Return values

int —

the number of affected rows

expr()

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


    public
                    expr() : IExpressionBuilder

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


    $qb = $conn->getQueryBuilder()
        ->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

IExpressionBuilder

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|IQueryFunction $from[, string|null $alias = null ]) : $this


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

Parameters

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

Return values

$this —

This QueryBuilder instance.

func()

Gets an FunctionBuilder used for object-oriented construction of query functions.


    public
                    func() : IFunctionBuilder

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


    $qb = $conn->getQueryBuilder()
        ->select('u')
        ->from('users', 'u')
        ->where($qb->fun()->md5('u.id'));

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

Return values

IFunctionBuilder

getColumnName()

Returns the column name quoted and with table alias prefix as needed by the implementation


    public
                    getColumnName(string $column[, string $tableAlias = '' ]) : string

Parameters

$column : string
$tableAlias : string = ''

Return values

string

getConnection()

Gets the associated DBAL Connection for this query builder.


    public
                    getConnection() : IDBConnection

Return values

IDBConnection

getFirstResult()

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


    public
                    getFirstResult() : int

Returns 0 if was not applied to this QueryBuilder.

Return values

int —

The position of the first result.

getLastInsertId()

Used to get the id of the last inserted element


    public
                    getLastInsertId() : int

Return values

int

getMaxResults()

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


    public
                    getMaxResults() : int|null

Returns NULL if was not applied to this query builder.

Return values

int|null —

The maximum number of results.

getOutputColumns()

Get a list of column names that are expected in the query output


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

Return values

array<string|int, mixed>

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

This function is going to be removed with the next Doctrine/DBAL update and we can not fix this in our wrapper. Please track the details you need, outside the object.

Parameters

$queryPartName : string

getQueryParts()

Gets all query parts.


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

This function is going to be removed with the next Doctrine/DBAL update and we can not fix this in our wrapper. Please track the details you need, outside the object.

Return values

array<string|int, mixed>

getSQL()

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


    public
                    getSQL() : string


    $qb = $conn->getQueryBuilder()
        ->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

This function is going to be removed with the next Doctrine/DBAL update and we can not fix this in our wrapper.

Return values

int —

Either QueryBuilder::STATE_DIRTY or QueryBuilder::STATE_CLEAN.

getTableName()

Returns the table name quoted and with database prefix as needed by the implementation.


    public
                    getTableName(string|IQueryFunction $table) : string

If a query function is passed the function is casted to string, this allows passing functions as sub-queries for join expression.

Parameters

$table : string|IQueryFunction

Return values

string

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 ...$groupBys) : $this

Replaces any previously specified groupings, if any.


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

Parameters

$groupBys : 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.

hintShardKey()

Provide a hint for the shard key for queries where this can't be detected otherwise


    public
                    hintShardKey(string $column, mixed $value[, bool $overwrite = false ]) : $this

Parameters

$column : string
$value : mixed
$overwrite : bool = false

Return values

$this

innerJoin()

Creates and adds a join to the query.


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


    $qb = $conn->getQueryBuilder()
        ->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|ICompositeExpression|null = 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->getQueryBuilder()
        ->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|ICompositeExpression|null $condition = null ]) : $this


    $qb = $conn->getQueryBuilder()
        ->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|ICompositeExpression|null = 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|IQueryFunction $join, string $alias[, string|ICompositeExpression|null $condition = null ]) : $this


    $qb = $conn->getQueryBuilder()
        ->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|IQueryFunction: The table name to join.
$alias : string: The alias of the join table.
$condition : string|ICompositeExpression|null = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

orderBy()

Specifies an ordering for the query results.


    public
                    orderBy(string|IQueryFunction|ILiteral|IParameter $sort[, string $order = null ]) : $this

Replaces any previously specified orderings, if any.

Parameters

$sort : string|IQueryFunction|ILiteral|IParameter: 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 = $conn->getQueryBuilder()
        ->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.

prefixTableName()

Returns the table name with database prefix as needed by the implementation


    public
                    prefixTableName(string $table) : string

Parameters

$table : string

Return values

string

resetQueryPart()

Resets a single SQL part.


    public
                    resetQueryPart(string $queryPartName) : $this

This function is going to be removed with the next Doctrine/DBAL update and we can not fix this in our wrapper. Please create a new IQueryBuilder instead.

Parameters

$queryPartName : string

Return values

$this —

This QueryBuilder instance.

resetQueryParts()

Resets SQL parts.


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

This function is going to be removed with the next Doctrine/DBAL update and we can not fix this in our wrapper. Please create a new IQueryBuilder instead.

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|ICompositeExpression|null $condition = null ]) : $this


    $qb = $conn->getQueryBuilder()
        ->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|ICompositeExpression|null = null: The condition for the join.

Return values

$this —

This QueryBuilder instance.

runAcrossAllShards()

Set the query to run across all shards if sharding is enabled.


    public
                    runAcrossAllShards() : $this

Return values

$this

select()

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


    public
                    select(mixed ...$selects) : $this

Replaces any previously specified selections, if any.


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

Parameters

$selects : mixed: The selection expressions.

Return values

$this —

This QueryBuilder instance.

selectAlias()

Specifies an item that is to be returned with a different name in the query result.


    public
                    selectAlias(mixed $select, string $alias) : $this


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

Parameters

$select : mixed: The selection expressions.
$alias : string: The column alias used in the constructed query.

Return values

$this —

This QueryBuilder instance.

selectDistinct()

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


    public
                    selectDistinct(mixed $select) : $this


    $qb = $conn->getQueryBuilder()
        ->selectDistinct('type')
        ->from('users');

Parameters

$select : mixed: 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, ILiteral|IParameter|IQueryFunction|string $value) : $this


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

Parameters

$key : string: The column to set.
$value : ILiteral|IParameter|IQueryFunction|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|null $maxResults) : $this

Parameters

$maxResults : int|null: 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|int $type = null ]) : $this


    $qb = $conn->getQueryBuilder()
        ->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|int = null: One of the IQueryBuilder::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 = [] ]) : $this


    $qb = $conn->getQueryBuilder()
        ->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> = []: 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, IParameter|string $value) : $this


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

Parameters

$column : string: The column into which the value should be inserted.
$value : IParameter|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->getQueryBuilder()
        ->update('users')
        ->set('email', ':email')
        ->where('id = :user_id');
        ->setParameter(':user_id', 1);

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->getQueryBuilder()
        ->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->getQueryBuilder()
        ->select('u.name')
        ->from('users', 'u')
        ->where('u.id = ?');
// You can optionally programmatically build and/or expressions
$qb = $conn->getQueryBuilder();

$or = $qb->expr()->orx(
    $qb->expr()->eq('u.id', 1),
    $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.

IQueryBuilder in Application

Tags

Table of Contents

Constants

Methods

Constants

MAX_ROW_DELETION

Tags

PARAM_BOOL

Tags

PARAM_DATE

Tags

PARAM_DATE_IMMUTABLE

Tags

PARAM_DATE_MUTABLE

Tags

PARAM_DATETIME_IMMUTABLE

Tags

PARAM_DATETIME_MUTABLE

Tags

PARAM_DATETIME_TZ_IMMUTABLE

Tags

PARAM_DATETIME_TZ_MUTABLE

Tags

PARAM_INT

Tags

PARAM_INT_ARRAY

Tags

PARAM_JSON

Tags

PARAM_LOB

Tags

PARAM_NULL

Tags

PARAM_STR

Tags

PARAM_STR_ARRAY

Tags

PARAM_TIME_IMMUTABLE

Tags

PARAM_TIME_MUTABLE

Tags

Methods

addGroupBy()

Parameters

Tags

Return values

addOrderBy()

Parameters

Tags

Return values

addSelect()

Parameters

Tags

Return values

andHaving()

Parameters

Tags

Return values

andWhere()

Parameters

Tags

Return values

automaticTablePrefix()

Parameters

Tags

createFunction()

Parameters

Tags

Return values

createNamedParameter()

Parameters

Tags

Return values

createParameter()

Parameters

Tags

Return values

createPositionalParameter()

Parameters

IQueryBuilder
in

Application