PHP QueryBuilder::orWhere方法代码示例

 public function getNuArtefatoNuDigital(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto)
 {
     if ($dto->getNuArtefato()) {
         $nuArtefato = str_replace(' ', '', str_replace('.', '', str_replace('/', '', str_replace('-', '', $dto->getNuArtefato()))));
         $queryBuilder->orWhere('vwca.nuDigital like :nuDigital')->setParameter('nuDigital', '%' . $dto->getNuArtefato() . '%');
         $queryBuilder->orWhere('vwca.nuArtefato like :nuArtefato')->setParameter('nuArtefato', '%' . $nuArtefato . '%');
     }
 }

 /**
  * {@inheritdoc}
  */
 public function restrict($expression, $condition = DataSourceInterface::CONDITION_AND)
 {
     switch ($condition) {
         case DataSourceInterface::CONDITION_AND:
             $this->queryBuilder->andWhere($expression);
             break;
         case DataSourceInterface::CONDITION_OR:
             $this->queryBuilder->orWhere($expression);
             break;
     }
 }

 private function addQueryFilter(QueryBuilder $query, $filter)
 {
     $expr = $query->expr();
     /*$query->orWhere($expr->eq('entity.id', ':filter_int'));*/
     $query->orWhere($expr->like('entity.code', ':filter_str'));
     $query->orWhere($expr->like('entity.oName', ':filter_str'));
     $query->orWhere($expr->like('entity.dName', ':filter_str'));
     /*$query->setParameter('filter_int', $filter);*/
     $query->setParameter('filter_str', '%' . $filter . '%');
     return $query;
 }

 /**
  * Quick search
  * 
  * @return \ZfTable\Source\DoctrineQueryBuilder
  */
 protected function search()
 {
     if ($search = $this->getParamAdapter()->getQuickSearch()) {
         foreach ($this->getTable()->getHeaders() as $k => $v) {
             $column = isset($v['column']) ? $v['column'] : $k;
             if (isset($v['tableAlias'])) {
                 $this->query->orWhere($this->query->expr()->like($v['tableAlias'] . '.' . $column, "'%" . $search . "%'"));
             }
         }
     }
     return $this;
 }

 /**
  * Adds a new WHERE or HAVING restriction depends on the given parameters.
  *
  * @param mixed  $restriction The restriction to add.
  * @param string $condition   The condition.
  *                            Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
  * @param bool   $isComputed  Specifies whether the restriction should be added to the HAVING part of a query.
  */
 public function addRestriction($restriction, $condition, $isComputed = false)
 {
     $restriction = $this->qbTools->replaceAliasesWithFields($restriction);
     if ($condition === FilterUtility::CONDITION_OR) {
         if ($isComputed) {
             $this->qb->orHaving($restriction);
         } else {
             $this->qb->orWhere($restriction);
         }
     } else {
         if ($isComputed) {
             $this->qb->andHaving($restriction);
         } else {
             $this->qb->andWhere($restriction);
         }
     }
 }

 private function whereToPublish(QueryBuilder $builder)
 {
     $builder->where('r.published = :published');
     $builder->setParameter('published', false);
     $builder->orWhere('p.modified = :modified');
     $builder->setParameter('modified', true);
     return $this;
 }

 /**
  * Adds a new WHERE or HAVING restriction depends on the given parameters.
  *
  * @param mixed  $restriction The restriction to add.
  * @param string $condition   The condition.
  *                            Can be FilterUtility::CONDITION_OR or FilterUtility::CONDITION_AND.
  * @param bool   $isComputed  Specifies whether the restriction should be added to the HAVING part of a query.
  */
 public function addRestriction($restriction, $condition, $isComputed = false)
 {
     if ($this->fixComparison($restriction, $condition)) {
         return;
     }
     if ($condition === FilterUtility::CONDITION_OR) {
         if ($isComputed) {
             $this->qb->orHaving($restriction);
         } else {
             $this->qb->orWhere($restriction);
         }
     } else {
         if ($isComputed) {
             $this->qb->andHaving($restriction);
         } else {
             $this->qb->andWhere($restriction);
         }
     }
 }

 /**
  * Multiple filtering
  *
  * @param array $rules   array of rules fore multiple filtering
  * @param array $options array of search options
  *
  * @return void
  */
 protected function _multiFilter($rules, $options = array())
 {
     $boolean = strtoupper($options['boolean']);
     foreach ($rules as $rule) {
         if ($boolean == 'OR') {
             $this->_qb->orWhere($this->getService()->getAlias() . '.' . $rule['field'] . ' ' . str_replace('?', ':' . $rule['field'], $this->_operator[$rule['expression']]));
         } else {
             $this->_qb->andWhere($this->getService()->getAlias() . '.' . $rule['field'] . ' ' . str_replace('?', ':' . $rule['field'], $this->_operator[$rule['expression']]));
         }
         $this->_qb->setParameter($rule['field'], $this->_setWildCardInValue($rule['expression'], $rule['value']));
     }
 }

 /**
  * Apply to given query builder object additional conditions
  * for integrate activity lists from inheritance targets
  *
  * @param QueryBuilder $qb
  * @param string  $entityClass
  * @param integer $entityId
  */
 public function applyInheritanceActivity(QueryBuilder $qb, $entityClass, $entityId)
 {
     if (!$this->hasInheritances($entityClass)) {
         return;
     }
     $inheritanceTargets = $this->getInheritanceTargetsRelations($entityClass);
     foreach ($inheritanceTargets as $key => $inheritanceTarget) {
         $alias = 'ta_' . $key;
         $qb->leftJoin('activity.' . $inheritanceTarget['targetClassAlias'], $alias);
         $qb->orWhere($qb->expr()->andX($qb->expr()->andX($qb->expr()->in($alias . '.id', $this->getSubQuery($inheritanceTarget['targetClass'], $inheritanceTarget['path'], $entityId, $key)->getDQL()))));
     }
 }

 /**
  * @param \Doctrine\ORM\Query|\Doctrine\ORM\QueryBuilder $query
  * @param array $array
  * @param string $contiene
  * @return \Doctrine\ORM\Query|\Doctrine\ORM\QueryBuilder
  */
 public function consultaContiene($query, $array, $contiene)
 {
     if ($contiene != "") {
         $fields = array_keys($this->getClassMetadata()->fieldNames);
         $alias = $query->getRootAlias();
         $count = 0;
         foreach ($array as $field) {
             $fieldMapping = $this->getClassMetadata()->getFieldForColumn($field);
             $where = sprintf("UPPER(%s.%s) LIKE :condicion", $alias, $fieldMapping);
             if ($count == 0) {
                 $query->andWhere($where);
             } else {
                 $query->orWhere($where);
             }
             $count++;
         }
         $query->setParameter("condicion", "%" . strtoupper($contiene) . "%");
     }
     return $query;
 }

 /**
  * Adiciona condição
  * @param \Doctrine\ORM\QueryBuilder $queryBuilder
  * @param \Core_Dto_Search $dto
  */
 protected function addWhere(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto)
 {
     $isDate = FALSE;
     $dataSearch = strtotime($dto->getDataSearch());
     if (checkdate(date('m', $dataSearch), date('d', $dataSearch), date('Y', $dataSearch))) {
         $isDate = TRUE;
         $newDate = date('Y-m-d', $dataSearch);
         $queryBuilder->andWhere('vcm.dataCriacao = :data')->setParameter('data', $newDate);
         $queryBuilder->orWhere('vcm.prazo = :data')->setParameter('data', $newDate);
     }
     if (!$isDate && $dto->getDataSearch() != '') {
         $query = mb_strtolower($dto->getDataSearch(), 'UTF-8');
         $queryBuilder->andWhere('(LOWER(vcm.tipo) like :query)');
         $queryBuilder->orWhere('(LOWER(vcm.origem) like :query)');
         $queryBuilder->orWhere('(LOWER(vcm.assunto) like :query)');
         $queryBuilder->orWhere('(LOWER(vcm.autor) like :query)');
         $queryBuilder->setParameter('query', '%' . $query . '%');
     }
 }

 /**
  * Create a LIKE comparison with entity texts colunms.
  *
  * @param string                  $pattern
  * @param DoctrineORMQueryBuilder $qb
  * @param string                  $alias
  */
 protected function classicLikeComparison($pattern, \Doctrine\ORM\QueryBuilder &$qb, $alias = "obj")
 {
     /*
      * get fields needed for a search
      * query
      */
     $metadatas = $this->_em->getClassMetadata($this->getEntityName());
     $criteriaFields = [];
     $cols = $metadatas->getColumnNames();
     foreach ($cols as $col) {
         $field = $metadatas->getFieldName($col);
         $type = $metadatas->getTypeOfField($field);
         if (in_array($type, $this->searchableTypes) && $field != 'folder' && $field != 'childrenOrder' && $field != 'childrenOrderDirection') {
             $criteriaFields[$field] = '%' . strip_tags($pattern) . '%';
         }
     }
     foreach ($criteriaFields as $key => $value) {
         $qb->orWhere($qb->expr()->like($alias . '.' . $key, $qb->expr()->literal($value)));
     }
 }

 /**
  * {@inheritdoc}
  */
 public function orWhere($where)
 {
     $this->queryBuilder->orWhere($where);
     return $this;
 }

 protected function buildQueryAutoCompleteTerm(array $fields, $term)
 {
     $qb = new QueryBuilder($this->_em);
     $qb->select('e')->from($this->_entityName, 'e');
     if (count($fields) > 0 && $term != "") {
         // term in quotes bedeutet wörtliche suche
         if (\Psc\Preg::match($term, '/^\\s*("|\')(.*)("|\')\\s*$/', $m)) {
             $term = $m[2];
             foreach ($fields as $field) {
                 $qb->orWhere('e.' . $field . ' = ?1 ');
             }
             $qb->setParameter(1, $term);
         } else {
             /* normale suche */
             foreach ($fields as $field) {
                 $qb->orWhere('e.' . $field . ' = ?1 ');
                 for ($i = 2; $i <= 4; $i++) {
                     $qb->orWhere('e.' . $field . ' LIKE ?' . $i);
                 }
             }
             $qb->setParameter(1, $term);
             $qb->setParameter(2, '%' . $term);
             $qb->setParameter(3, '%' . $term . '%');
             $qb->setParameter(4, $term . '%');
         }
     }
     return $qb;
 }

 /**
  * @param QueryBuilder $qb
  * @param array        $params
  *
  * @return array
  * @throws \Exception
  */
 public function getDatatables(QueryBuilder $qb, array $params)
 {
     if ($this->getAColumns() != '' && $this->getAColumnsArray() != '') {
         if ($_GET['sSearch'] != "") {
             for ($i = 0; $i < count($this->getAColumns()); $i++) {
                 if ($this->getAColumns()[$i]['type'] == "string") {
                     $qb->orWhere("LOWER(remove_accents(" . $this->getAColumns()[$i]['campo'] . ")) LIKE LOWER(remove_accents(:busca" . $i . "))")->setParameter("busca" . $i, '%' . $params['sSearch'] . '%');
                 } else {
                     if (is_int($params['sSearch'])) {
                         $qb->orWhere($this->getAColumns()[$i]['campo'] . " = '" . $params['sSearch'] . "'");
                     }
                 }
             }
         }
         // Select total
         $stmt = $this->getEntityManager()->getConnection()->prepare("SELECT count(a) FROM (:sql) AS a")->setParameter("sql", $this->get_raw_sql($qb));
         $stmt->execute();
         if (isset($params['iSortCol_0'])) {
             $primeiro = true;
             for ($i = 0; $i < intval($params['iSortingCols']); $i++) {
                 if ($params['bSortable_' . intval($params['iSortCol_' . $i])] == "true") {
                     if ($primeiro) {
                         $primeiro = false;
                         $qb->orderBy($this->getAColumns()[intval($params['iSortCol_' . $i])]['campo'], $params['sSortDir_' . $i]);
                     } else {
                         $qb->add($this->getAColumns()[intval($params['iSortCol_' . $i])]['campo'], $params['sSortDir_' . $i]);
                     }
                 }
             }
         }
         for ($i = 0; $i < count($this->getAColumns()); $i++) {
             if ($_GET['bSearchable_' . $i] == "true" && $params['sSearch_' . $i] != '') {
                 if ($this->getAColumns()[$i]['type'] == "string") {
                     $qb->orWhere("LOWER(remove_accents(" . $this->getAColumns()[$i]['campo'] . ")) LIKE LOWER(remove_accents(:busca" . $i . "))")->setParameter("busca" . $i, '%' . $params['sSearch_' . $i] . '%');
                 } else {
                     if (is_int($params['sSearch'])) {
                         $qb->andWhere($this->getAColumns()[$i]['campo'] . " = '" . $params['sSearch_' . $i] . "'");
                     }
                 }
             }
         }
         $qb->setMaxResults($params['iDisplayLength']);
         $qb->setFirstResult($params['iDisplayStart']);
         $rows = array();
         foreach ($qb->getQuery()->getResult() as $key => $value) {
             $aRow = $value->toArray();
             $row = array();
             for ($i = 0; $i < count($this->getAColumns()); $i++) {
                 if (isset($this->getAColumnsArray()[$i]['html'])) {
                     $html = $this->getAColumnsArray()[$i]['html'];
                     foreach ($aRow as $key => $value) {
                         $html = str_replace("{" . $key . "}", $value, $html);
                     }
                     $row[] = $html;
                 } else {
                     $row[] = $aRow[$this->getAColumnsArray()[$i]['campo']];
                 }
             }
             $rows[] = $row;
         }
         $total = $stmt->fetchAll()[0]['count'];
         $output = array("sEcho" => $params['sEcho'], "iTotalRecords" => $total, "iTotalDisplayRecords" => $total, "iDisplayLength" => $params['iDisplayLength'], "aaData" => $rows);
         return $output;
     } else {
         throw new \Exception("The value(getAColumns, getAColumnsArray) is required", 1);
     }
 }