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 function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('pName', 'ASC');
break;
case "date":
$query->orderBy('pDateAdded', 'DESC');
break;
}
switch ($this->featured) {
case "featured":
$query->andWhere("pFeatured = 1");
break;
case "nonfeatured":
$query->andWhere("pFeatured = 0");
break;
}
if ($this->activeOnly) {
$query->andWhere("pActive = 1");
}
if ($this->search) {
$query->andWhere('pName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
return $query;
}
public function where($where)
{
if (!$this->isWhereInConditions($where)) {
return $this;
}
return parent::where($where);
}
public function where($column, $operator = null, $value = null, $boolean = 'and')
{
$args = func_get_args();
if (count($args) == 1) {
parent::where($args[0]);
return $this;
}
$column = $args[0];
$boolean = 'and';
$operator = '=';
if (count($args) == 2) {
$value = $args[1];
}
if (count($args) == 3) {
$operator = $args[1];
$value = $args[2];
}
if (count($args) == 4) {
$operator = $args[1];
$value = $args[2];
$boolean = $args[3];
}
if (is_array($value)) {
$operator = $operator == '=' ? 'in' : 'notIn';
$where_clause = $this->expr()->{$operator}($column, parent::createNamedParameter($value, \Doctrine\DBAL\Connection::PARAM_STR_ARRAY));
} else {
$where_clause = $column . $operator . parent::createNamedParameter($value);
}
if ($boolean == 'and') {
parent::andWhere($where_clause);
} elseif ($boolean == 'or') {
parent::orWhere($where_clause);
}
return $this;
}
/**
* @param string $predicates The restriction predicates.
*
* @return QueryBuilder
*/
public function where($predicates)
{
if ($this->mergeNextWhere) {
$this->mergeNextWhere = false;
parent::andWhere($predicates);
} else {
parent::where($predicates);
}
return $this;
}
/**
* @param QueryBuilder $queryBuilder
* @param Request $request
* @return QueryBuilder
*/
public function beforeGetList(QueryBuilder $queryBuilder, Request $request)
{
$from = $request->query->get('from');
$to = $request->query->get('to');
$author = $request->query->get('author');
if (!$from && !$to && !$author) {
return $queryBuilder;
} else {
return $queryBuilder->where($queryBuilder->expr()->like('author', '?'))->andWhere('o.created BETWEEN ? AND ?')->setParameter(0, $author ? $author : self::DEFAULT_AUTHOR)->setParameter(1, $from ? $from : self::DEFAULT_FROM)->setParameter(2, $to ? $to : self::DEFAULT_TO);
}
}
public function finalizeQuery(\Doctrine\DBAL\Query\QueryBuilder $query)
{
$paramcount = 0;
if (isset($this->gID) && $this->gID > 0) {
$query->where('gID = ?')->setParameter($paramcount++, $this->gID);
}
switch ($this->sortBy) {
case "alpha":
$query->orderBy('drName', 'ASC');
break;
}
if ($this->search) {
$query->andWhere('drName like ?')->setParameter($paramcount++, '%' . $this->search . '%');
}
$query->andWhere('drDeleted is NULL');
return $query;
}
/**
* Builds the WHERE clause from $filter
* @param QueryBuilder $qb
* @param array $filters should be an array with arrays wich contains 3 datas
* [
* ['column_name', 'expr_type', 'value'],
* [],
* ...
* ]
* expr_types: 'eq', 'neq', 'lt', 'lte', 'gt', 'gte', 'like', 'in', 'notIn', 'notLike'
* @return self
*/
protected function buildWhere(QueryBuilder $qb, array $filters = [])
{
if (!empty($filters)) {
$expr = $qb->expr()->andX();
foreach ($filters as $f) {
$column = $f[0];
$expr_type = $f[1];
$value = isset($f[2]) ? $f[2] : null;
$type = \PDO::PARAM_STR;
if ($this->getColumn($column) === null) {
throw Schema\SchemaException::columnDoesNotExist($column, $this->table_name);
}
if (!in_array($expr_type, $this->getExpressionTypes())) {
throw QueryBuilderException::expressionTypeDoesNotExist($expr_type);
}
if (in_array($expr_type, ['in', 'notIn']) && is_array($value)) {
switch ($this->column_types[$column]) {
case 'integer':
$type = \Doctrine\DBAL\Connection::PARAM_INT_ARRAY;
break;
case 'string':
$type = \Doctrine\DBAL\Connection::PARAM_STR_ARRAY;
break;
}
}
$expr->add($qb->expr()->{$expr_type}($this->conn->quoteIdentifier($column), $qb->createNamedParameter($value, $type)));
}
$qb->where($expr);
}
return $this;
}
/**
* Set the QueryBuilder where parameters.
*
* @param QueryBuilder $query
* @param string $type
*/
private function timedWhere(QueryBuilder $query, $type)
{
if ($type === 'publish') {
$query->where('status = :oldstatus')->andWhere('datepublish < CURRENT_TIMESTAMP')->setParameter('oldstatus', 'timed')->setParameter('newstatus', 'published');
} else {
$query->where('status = :oldstatus')->andWhere('datedepublish <= CURRENT_TIMESTAMP')->andWhere('datedepublish > :zeroday')->andWhere('datechanged < datedepublish')->setParameter('oldstatus', 'published')->setParameter('newstatus', 'held')->setParameter('zeroday', '1900-01-01 00:00:01');
}
}
/**
* @inheritdoc
*/
protected function addWhere(QueryBuilder $queryBuilder)
{
$queryBuilder->where('`main`.`status` != :status');
}
/**
* Set the QueryBuilder where parameters.
*
* @param QueryBuilder $query
* @param string $type
*/
private function timedWhere(QueryBuilder $query, $type)
{
if ($type === 'publish') {
$query->where('status = :oldstatus')->andWhere('datepublish < :currenttime')->setParameter('oldstatus', 'timed')->setParameter('newstatus', 'published')->setParameter('currenttime', new \DateTime(), \Doctrine\DBAL\Types\Type::DATETIME);
} else {
$query->where('status = :oldstatus')->andWhere('datedepublish <= :currenttime')->andWhere('datedepublish > :zeroday')->andWhere('datechanged < datedepublish')->setParameter('oldstatus', 'published')->setParameter('newstatus', 'held')->setParameter('zeroday', '1900-01-01 00:00:01')->setParameter('currenttime', new \DateTime(), \Doctrine\DBAL\Types\Type::DATETIME);
}
}
/**
* @param array $conditions
* @return $this
*/
public function where($conditions)
{
$this->queryBuilder->where($this->clause($conditions));
return $this;
}
/**
* @test
*/
public function whereDelegatesToConcreteQueryBuilder()
{
$this->concreteQueryBuilder->where('uid=1', 'type=9')->shouldBeCalled()->willReturn($this->subject);
$this->subject->where('uid=1', 'type=9');
}
/**
* Specifies one or more restrictions to the query result.
* Replaces any previously specified restrictions, if any.
*
* @param mixed $predicates The restriction predicates.
*
* @return self
*/
public function where($predicates)
{
$this->qb->where($predicates);
return $this;
}
/**
* Modify the query to count how many rows is between a range of date diff in seconds
*
* @param QueryBuilder $query
* @param string $dateColumn1
* @param string $dateColumn2
* @param integer $startSecond
* @param integer $endSecond
* @param array $filters will be added to where claues
* @param string $tablePrefix
*/
public function modifyCountDateDiffQuery(QueryBuilder &$query, $dateColumn1, $dateColumn2, $startSecond = 0, $endSecond = 60, $tablePrefix = 't')
{
$query->select('COUNT(' . $tablePrefix . '.' . $dateColumn1 . ') AS count');
$query->where('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') >= :startSecond');
$query->andWhere('TIMESTAMPDIFF(SECOND, ' . $tablePrefix . '.' . $dateColumn1 . ', ' . $tablePrefix . '.' . $dateColumn2 . ') < :endSecond');
$query->setParameter('startSecond', $startSecond);
$query->setParameter('endSecond', $endSecond);
}
/**
* @param int[] $expected
* @param string $permission
* @param UserInterface $user
* @param string[] $orX
* @param string $where
*/
private function verify(array $expected, $permission, UserInterface $user = null, array $orX = [], $where = null)
{
$fails = [];
$permission = strtoupper($permission);
$DBALQueryBuilder = new DBALQueryBuilder($this->connection);
$DBALQueryBuilder->select('*')->from('posts', 'p');
if (null !== $where) {
$DBALQueryBuilder->where($where);
}
$this->aclFilter->apply($DBALQueryBuilder, $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($DBALQueryBuilder), 'DBALQueryBuilder failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
$ORMQueryBuilder = $this->em->createQueryBuilder();
$ORMQueryBuilder->select('p')->from('Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p');
if (null !== $where) {
$ORMQueryBuilder->where($where);
}
$cloneORMQueryBuilder = clone $ORMQueryBuilder;
$query = $this->aclFilter->apply($ORMQueryBuilder, $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($query), 'ORMQueryBuilder failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
$query = $this->aclFilter->apply($cloneORMQueryBuilder->getQuery(), $permission, 'Nuxia\\AclBundle\\Tests\\Model\\PostObject', 'p.id', $user, $orX);
try {
$this->assertEquals($expected, $this->getPostIds($query), 'ORMQuery failed');
} catch (\PHPUnit_Framework_ExpectationFailedException $e) {
$fails[] = $e;
}
if (!empty($fails)) {
$messages = [];
/** @var \PHPUnit_Framework_ExpectationFailedException $e */
foreach ($fails as $e) {
$messages[] = $e->getMessage() . $e->getComparisonFailure()->getDiff();
}
$this->fail(implode(PHP_EOL, $messages));
}
}
/**
* Set the QueryBuilder where parameters.
*
* @param QueryBuilder $query
*/
private function getPublishedQuery(QueryBuilder $query)
{
$query->where('datedepublish <= :currenttime')->andWhere('datedepublish > :zeroday')->andWhere('datechanged < datedepublish')->setParameter('status', 'published')->setParameter('zeroday', '1900-01-01 00:00:01');
}
/**
* Set any required WHERE clause on a QueryBuilder.
*
* @param QueryBuilder $query
* @param integer $level
* @param string $context
*
* @return QueryBuilder
*/
private function setWhere(QueryBuilder $query, $level = null, $context = null)
{
if ($level || $context) {
$where = $query->expr()->andX();
if ($level) {
$where->add($query->expr()->eq('level', ':level'));
}
if ($context) {
$where->add($query->expr()->eq('context', ':context'));
}
$query->where($where)->setParameters(array(':level' => $level, ':context' => $context));
}
return $query;
}
/**
* @param array $params
*/
public function adaptQueryBuilder(QueryBuilder $queryBuilder, array $params = array())
{
$queryBuilder->where('1=1');
}
/**
* Set any required WHERE clause on a QueryBuilder.
*
* @param QueryBuilder $query
* @param string $contenttype
* @param array $options
*
* @return QueryBuilder
*/
private function setWhere(QueryBuilder $query, $contenttype, array $options)
{
$where = $query->expr()->andX()->add($query->expr()->eq('contenttype', ':contenttype'));
// Set any required WHERE
if (isset($options['contentid']) || isset($options['id'])) {
if (isset($options['contentid'])) {
$where->add($query->expr()->eq('contentid', ':contentid'));
}
if (isset($options['id'])) {
$where->add($query->expr()->eq('log.id', ':logid'));
}
}
$query->where($where)->setParameters([':contenttype' => $contenttype, ':contentid' => isset($options['contentid']) ? $options['contentid'] : null, ':logid' => isset($options['id']) ? $options['id'] : null]);
return $query;
}
/**
* Set any required WHERE clause on a QueryBuilder.
*
* @param QueryBuilder $query
* @param string $contenttype
* @param array $options
*/
protected function setWhere(QueryBuilder $query, $contenttype, array $options)
{
$where = $query->expr()->andX()->add($query->expr()->eq('contenttype', ':contenttype'));
// Set any required WHERE
if (isset($options['contentid']) || isset($options['id'])) {
if (isset($options['contentid'])) {
$where->add($query->expr()->eq('contentid', ':contentid'));
}
if (isset($options['id'])) {
$tableName = $this->getTableName();
$where->add($query->expr()->eq("{$tableName}.id", ':logid'));
}
}
$query->where($where)->setParameters([':contenttype' => $contenttype, ':contentid' => isset($options['contentid']) ? $options['contentid'] : null, ':logid' => isset($options['id']) ? $options['id'] : null]);
}
/**
* Specifies one or more restrictions to the query result.
* Replaces any previously specified restrictions, if any.
*
* @param mixed,... $predicates
* @return QueryBuilder This QueryBuilder instance.
*/
public function where(...$predicates) : QueryBuilder
{
$this->concreteQueryBuilder->where(...$predicates);
return $this;
}
/**
* @group DBAL-959
*/
public function testGetParameterTypes()
{
$qb = new QueryBuilder($this->conn);
$qb->select('*')->from('users');
$this->assertSame(array(), $qb->getParameterTypes());
$qb->where('name = :name');
$qb->setParameter('name', 'foo');
$this->assertSame(array(), $qb->getParameterTypes());
$qb->setParameter('name', 'foo', \PDO::PARAM_STR);
$qb->where('is_active = :isActive');
$qb->setParameter('isActive', true, \PDO::PARAM_BOOL);
$this->assertSame(array('name' => \PDO::PARAM_STR, 'isActive' => \PDO::PARAM_BOOL), $qb->getParameterTypes());
}
private function applyQueryFilter(QueryBuilder $query, Filter $filter)
{
$query->where('u.enabled = 1');
if ($filter->isEmpty()) {
return;
}
if ($nt = $filter->getNotificationType()) {
// JOIN NotificationSubscriptions ns ON (u.id = ns.idUser AND ns.idNotificationType = x)
$query->innerJoin('u', 'NotificationSubscriptions', 'ns', 'u.id = ns.idUser AND ns.idNotificationType = :ntype');
$query->setParameter('ntype', $nt->getId());
}
if ($status = $filter->getUserStatus()) {
$query->andWhere('u.status = :status');
$query->setParameter('status', $status);
}
$sslist = $filter->getScholarships();
if (!empty($sslist)) {
$ssids = [];
foreach ($sslist as $scholarship) {
$ssids[] = $scholarship->getId();
}
$ssids = implode(',', $ssids);
$query->innerJoin('u', 'Entries', 'e', 'u.id = e.idUser AND e.idScholarship IN(' . $ssids . ')');
}
if (class_exists('GotChosen\\SiteBundle\\Entity\\EGGame') && $filter->hasSubmittedGame()) {
$query->innerJoin('u', 'Games', 'g', 'u.id = g.user_id');
}
if ($lang = $filter->getLanguage()) {
$query->innerJoin('u', 'UserProfile', 'up', 'u.id = up.user_id AND up.property_id = 19 AND up.propertyValue = :lang');
$query->setParameter('lang', $lang);
}
}
