PHP QueryBuilder::andWhere方法代码示例

 public function alterQueryBuilder(QueryBuilder $queryBuilder)
 {
     if ($this->year !== null) {
         $queryBuilder->andWhere('YEAR(transaction.date) = :year');
         $queryBuilder->setParameter(':year', $this->year);
     }
     if ($this->month && in_array($this->month, array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))) {
         $queryBuilder->andWhere('MONTH(transaction.date) = :month');
         $queryBuilder->setParameter(':month', $this->month);
     }
     if ($this->processed != null && in_array($this->processed, array(0, 1))) {
         $queryBuilder->andWhere('transaction.is_processed = :processed');
         $queryBuilder->setParameter(':processed', $this->processed ? true : false);
     }
     if ($this->tag != null) {
         $queryBuilder->leftJoin('transaction.tags', 'tag');
         $queryBuilder->andWhere('tag.slug = :tag');
         $queryBuilder->setParameter(':tag', $this->tag);
     }
     if ($this->category !== null) {
         if ($this->category == 'empty') {
             $queryBuilder->andWhere('transaction.category IS NULL');
         } else {
             $queryBuilder->leftJoin('transaction.category', 'category');
             $queryBuilder->leftJoin('category.parent', 'parent');
             $queryBuilder->andWhere('category.slug = :category or parent.slug = :category');
             $queryBuilder->setParameter(':category', $this->category);
         }
     }
     return $queryBuilder;
 }

 /**
  * Cria um filtro do tipo boolean na consulta.
  * 
  * @param Array $filter 
  */
 private function createBooleanFilter($filter)
 {
     $field = $filter['field'];
     $searchField = $this->getSearchField($field);
     $value = $filter['value'] == '' ? '0' : '1';
     $this->qb->andWhere($searchField . ' = :' . $field)->setParameter($field, $value);
 }

 /**
  * método que adiciona a clausula where
  * @param query
  * @return query
  */
 public function addWhere(\Doctrine\ORM\QueryBuilder &$query, \Core_Dto_Search $search, $nuCpfCnpjPassaporte)
 {
     $sqPessoa = $search->getSqPessoaCorporativo() ?: $search->getSqPessoa();
     if ($sqPessoa) {
         $query->andWhere('p.sqPessoa = :sqPessoa')->setParameter('sqPessoa', $sqPessoa);
     }
     switch ($search->getSqTipoPessoa()) {
         case \Core_Configuration::getSgdoceTipoPessoaPessoaFisica():
             $passaporte = $this->getDocumento();
             $query->leftJoin('p.sqPessoaFisica', 'pf');
             //passar cpf ou passaporte , passa parametro
             if ($nuCpfCnpjPassaporte != '') {
                 if ($search->getSqNacionalidade() == '1') {
                     $query->andWhere('pf.nuCpf = :nuCpf')->setParameter('nuCpf', $nuCpfCnpjPassaporte);
                 } else {
                     $query->leftJoin("p.sqPessoaDocumento", "d", "WITH", "d.sqAtributoTipoDocumento = :sqAtributoTipoDocumento")->setParameter("sqAtributoTipoDocumento", $passaporte['sqAtributoTipoDocumento'])->andWhere('pf.sqNacionalidade <> :sqNacionalidade')->setParameter('sqNacionalidade', '1')->andWhere('d.txValor = :txValor')->setParameter('txValor', $nuCpfCnpjPassaporte);
                 }
             }
             break;
         case \Core_Configuration::getSgdoceTipoPessoaPessoaJuridica():
             $query->leftJoin('p.sqPessoaJuridica', 'pj');
             if ($nuCpfCnpjPassaporte != '') {
                 $query->andWhere('pj.nuCnpj = :nuCnpj')->setParameter('nuCnpj', $nuCpfCnpjPassaporte);
             }
             break;
         case \Core_Configuration::getSgdoceTipoPessoaMinisterioPublico():
             $query->leftJoin('p.sqUnidadeOrgInterna', 'ui');
             $query->leftJoin('p.sqRppn', 'rpn');
             break;
         case \Core_Configuration::getSgdoceTipoPessoaOutrosOrgaos():
             $query->leftJoin('p.sqUnidadeOrgExterna', 'ue');
             break;
     }
 }

 /**
  * {@inheritdoc}
  *
  * Order collection by properties. The order of the ordered properties is the same as the order specified in the
  * query.
  */
 public function apply(ResourceInterface $resource, QueryBuilder $queryBuilder, Request $request)
 {
     $metadata = $this->getClassMetadata($resource);
     $fieldNames = $metadata->getFieldNames();
     foreach ($request->query->all() as $filter => $values) {
         if (!is_array($values)) {
             continue;
         }
         if (null !== $this->properties) {
             if (false === in_array($filter, $this->properties)) {
                 continue;
             }
         }
         if (in_array($filter, $fieldNames)) {
             foreach ($values as $period => $date) {
                 $period = strtolower($period);
                 $date = new \DateTime($date);
                 if ('before' === $period) {
                     $parameter = sprintf('%s%s', $period, $filter);
                     $queryBuilder->andWhere(sprintf('o.%s <= :%s', $filter, $parameter))->setParameter($parameter, $date);
                 }
                 if ('after' === $period) {
                     $parameter = sprintf('%s%s', $period, $filter);
                     $queryBuilder->andWhere(sprintf('o.%s >= :%s', $filter, $parameter))->setParameter($parameter, $date);
                 }
             }
         }
     }
 }

 /**
  * get the search dql.
  *
  * @return string
  */
 protected function _addSearch(\Doctrine\ORM\QueryBuilder $queryBuilder)
 {
     if ($this->search == true) {
         $request = $this->request;
         $search_fields = array_values($this->fields);
         foreach ($search_fields as $i => $search_field) {
             $search_param = $request->get("sSearch_{$i}");
             if ($request->get("sSearch_{$i}") !== false && !empty($search_param)) {
                 $field = explode(' ', trim($search_field));
                 $search_field = $field[0];
                 if (preg_match('/~/', $search_param)) {
                     $search_params = explode('~', $search_param);
                     $from_date = date('Y-m-d 00:00:00', strtotime($search_params[0]));
                     $to_date = date('Y-m-d 23:59:59', strtotime($search_params[1]));
                     if (($search_params[0] == '' || $from_date < '2000-01-01 00:00:00') && ($search_params[1] == '' || $to_date < '2000-01-01 00:00:00')) {
                         // both date fields are empty/invalid -- do nothing
                     } elseif ($search_params[0] == '' || $from_date < '2000-01-01 00:00:00') {
                         $queryBuilder->andWhere(" {$search_field} <= '{$to_date}' ");
                     } elseif ($search_params[1] == '' || $to_date < '2000-01-01 00:00:00') {
                         $queryBuilder->andWhere(" {$search_field} >= '{$from_date}' ");
                     } else {
                         $queryBuilder->andWhere(" {$search_field} between '{$from_date}' and '{$to_date}' ");
                     }
                 } else {
                     $queryBuilder->andWhere(" {$search_field} like '%{$request->get("sSearch_{$i}")}%' ");
                 }
             }
         }
     }
 }

 /**
  * Return a modified query builder with the conditions.
  *
  * @param object \Doctrine\ORM\QueryBuilder
  * @return object \Doctrine\ORM\QueryBuilder
  */
 public function digest(\Doctrine\ORM\QueryBuilder $builder)
 {
     foreach ($this->conditions as $condition) {
         // \Condition object
         // if sub conditions, redigest
         if ($condition instanceof \Adadgio\DoctrineDQLBundle\DQL\Condition) {
             // case of between ! (value is an array of two values)
             if ($condition->isBetween()) {
                 $builder->andWhere($condition->getStatement())->setParameter($condition->getParamKey(0), $condition->getValue(0))->setParameter($condition->getParamKey(1), $condition->getValue(1));
             } else {
                 $builder->andWhere($condition->getStatement())->setParameter($condition->getParam(), $condition->getValue());
             }
         } else {
             $exprs = array();
             foreach ($condition as $c) {
                 $exprs[] = $this->expression($builder, $c);
                 $builder->setParameter($c->getParam(), $c->getValue());
             }
             // use doctrine expression builder to make the "OR"
             $orX = $builder->expr()->orX();
             $orX->addMultiple($exprs);
             $builder->andWhere($orX);
         }
     }
     return $builder;
 }

 /**
  *
  * @return boolean
  */
 public function process()
 {
     $this->form->submit($this->request);
     if ($this->form->isValid()) {
         $formData = $this->form->getData();
         $name = (string) $formData->getName();
         if ($name) {
             $this->qb->andWhere($this->qb->expr()->like('p.name', ':name'))->setParameter('name', '%' . $name . '%');
         }
         $text = (string) $formData->getText();
         if ($text) {
             $this->qb->andWhere($this->qb->expr()->orx($this->qb->expr()->like('p.intro', ':intro'), $this->qb->expr()->like('p.content', ':content')))->setParameter('intro', '%' . $text . '%')->setParameter('content', '%' . $text . '%');
         }
         $isPublished = (string) $formData->getIsPublished();
         if ($isPublished) {
             if ($isPublished == 'yes') {
                 $this->qb->andWhere($this->qb->expr()->eq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
             }
             if ($isPublished == 'no') {
                 $this->qb->andWhere($this->qb->expr()->neq('p.status', ':status'))->setParameter('status', PostStatus::PUBLISHED);
             }
         }
         $order = (string) $formData->getOrder();
         if ($order) {
             $this->qb->orderBy('p.' . $order, 'DESC');
         }
         return true;
     }
     return false;
 }

 public function filters(QueryBuilder $qb, $key, $val)
 {
     switch ($key) {
         case 'history':
             if ($val) {
                 $orx = $qb->expr()->orX();
                 $orx->add('s.fk = :fk');
                 $orx->add('t.fk = :fk');
                 $qb->andWhere($orx);
                 $qb->setParameter('fk', intval($val));
             }
             break;
         case 'class':
             $orx = $qb->expr()->orX();
             $orx->add('s.class = :class');
             $orx->add('t.class = :class');
             $qb->andWhere($orx);
             $qb->setParameter('class', $val);
             break;
         case 'blamed':
             if ($val === 'null') {
                 $qb->andWhere($qb->expr()->isNull('a.blame'));
             } else {
                 // this allows us to safely ignore empty values
                 // otherwise if $qb is not changed, it would add where the string is empty statement.
                 $qb->andWhere($qb->expr()->eq('b.fk', ':blame'));
                 $qb->setParameter('blame', $val);
             }
             break;
         default:
             // if user attemps to filter by other fields, we restrict it
             throw new \Exception("filter not allowed");
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('alerts', $criteria)) {
         $ids = is_array($criteria['alerts']) ? $criteria['alerts'] : [$criteria['alerts']];
         $qb->join('x.alerts', 'al');
         $qb->andWhere($qb->expr()->in('al.id', ':alerts'));
         $qb->setParameter(':alerts', $ids);
     }
     //cleanup all the possible relationship filters
     unset($criteria['alerts']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("x.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('x.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * @param string $column
  * @param mixed  $value
  * @return void
  */
 public function whereRootFieldEq($column, $value)
 {
     $qualified = $this->aliases->get($this->getRootId()) . '.' . $column;
     $param = ':' . $this->aliases->get($this->getRootId()) . $column;
     $this->builder->andWhere($this->builder->expr()->eq($qualified, $param));
     $this->builder->setParameter($param, $value);
 }

 /**
  * {@inheritdoc}
  */
 public function addFieldFilter($field, $operator, $value)
 {
     $field = current($this->qb->getRootAliases()) . '.' . $field;
     $condition = $this->prepareCriteriaCondition($field, $operator, $value);
     $this->qb->andWhere($condition);
     return $this;
 }

 /**
  * @param FilterInterface $filter
  * @param FormInterface $form
  * @param QueryBuilder $qb
  * @param string $alias
  */
 public function handleForm(FilterInterface $filter, FormInterface $form, QueryBuilder $qb, $alias)
 {
     $data = $form->getData();
     if (!$data) {
         return;
     }
     $columns = $filter->getFullAttributeReferences($alias);
     if (!empty($data[DateRangeType::START_NAME])) {
         $startDate = $data[DateRangeType::START_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('fromDate');
             $dql[] = "{$column} >= :{$uid}";
             $qb->setParameter($uid, $startDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
     if (!empty($data[DateRangeType::END_NAME])) {
         $endDate = $data[DateRangeType::END_NAME];
         $dql = [];
         foreach ($columns as $column) {
             $uid = uniqid('endDate');
             $dql[] = "{$column} <= :{$uid}";
             $qb->setParameter($uid, $endDate);
         }
         $qb->andWhere(implode(' OR ', $dql));
     }
 }

 /**
  * @param QueryBuilder $qb
  * @param array $criteria
  * @param array $orderBy
  * @param int $limit
  * @param int $offset
  *
  * @return QueryBuilder
  */
 protected function attachCriteriaToQueryBuilder(QueryBuilder $qb, $criteria, $orderBy, $limit, $offset)
 {
     if (array_key_exists('courses', $criteria)) {
         $ids = is_array($criteria['courses']) ? $criteria['courses'] : [$criteria['courses']];
         $qb->join('p.programYears', 'c_programYear');
         $qb->join('c_programYear.cohort', 'c_cohort');
         $qb->join('c_cohort.courses', 'c_course');
         $qb->andWhere($qb->expr()->in('c_course.id', ':courses'));
         $qb->setParameter(':courses', $ids);
     }
     if (array_key_exists('sessions', $criteria)) {
         $ids = is_array($criteria['sessions']) ? $criteria['sessions'] : [$criteria['sessions']];
         $qb->join('p.programYears', 'se_programYear');
         $qb->join('se_programYear.cohort', 'se_cohort');
         $qb->join('se_cohort.courses', 'se_course');
         $qb->join('se_course.sessions', 'se_session');
         $qb->andWhere($qb->expr()->in('se_session.id', ':sessions'));
         $qb->setParameter(':sessions', $ids);
     }
     if (array_key_exists('terms', $criteria)) {
         $ids = is_array($criteria['terms']) ? $criteria['terms'] : [$criteria['terms']];
         $qb->join('p.programYears', 't_programYear');
         $qb->join('t_programYear.terms', 't_term');
         $qb->andWhere($qb->expr()->in('t_term.id', ':terms'));
         $qb->setParameter(':terms', $ids);
     }
     if (array_key_exists('schools', $criteria)) {
         $ids = is_array($criteria['schools']) ? $criteria['schools'] : [$criteria['schools']];
         $qb->join('p.school', 'sc_school');
         $qb->andWhere($qb->expr()->in('sc_school.id', ':schools'));
         $qb->setParameter(':schools', $ids);
     }
     unset($criteria['schools']);
     unset($criteria['courses']);
     unset($criteria['sessions']);
     unset($criteria['terms']);
     if (count($criteria)) {
         foreach ($criteria as $key => $value) {
             $values = is_array($value) ? $value : [$value];
             $qb->andWhere($qb->expr()->in("p.{$key}", ":{$key}"));
             $qb->setParameter(":{$key}", $values);
         }
     }
     if (empty($orderBy)) {
         $orderBy = ['id' => 'ASC'];
     }
     if (is_array($orderBy)) {
         foreach ($orderBy as $sort => $order) {
             $qb->addOrderBy('p.' . $sort, $order);
         }
     }
     if ($offset) {
         $qb->setFirstResult($offset);
     }
     if ($limit) {
         $qb->setMaxResults($limit);
     }
     return $qb;
 }

 /**
  * Convert an array of ID's to Doctrine entities / arrays
  *
  * @param  array $data
  * @return array
  */
 public function convert($data)
 {
     if (empty($data)) {
         return array();
     }
     $this->qb->andWhere($this->qb->expr()->in($this->searchfield, $data));
     return $this->qb->getQuery()->getResult();
 }

 /**
  * @param QueryBuilder $qb
  * @param array $filter
  */
 private function applyFilter(QueryBuilder $qb, array $filter)
 {
     if (isset($filter['category']) && !empty($filter['category'])) {
         $qb->andWhere('s.category = :categoryId')->setParameter('categoryId', $filter['category']);
     }
     if (isset($filter['location']) && !empty($filter['location'])) {
         $qb->andWhere('l.id IN (:locations)')->setParameter('locations', $filter['location']);
     }
 }