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