/** * @param QueryBuilder $queryBuilder * @param Parameter $parameters * @return QueryBuilder */ protected function generateQueryParamFilter(QueryBuilder $queryBuilder, Parameters $parameters) { if (isset($parameters['query']) && is_array($parameters['query'])) { $i = 1; foreach ($parameters['query'] as $query) { $type = '='; if ($query['type'] == 'like') { $type = 'LIKE'; } $whereString = sprintf('row.%s %s :%sValue%d', $query['field'], $type, $query['field'], $i); if (!isset($query['where']) || $query['where'] == 'and') { $queryBuilder->andWhere($whereString); } else { if ($query['where'] == 'or') { $queryBuilder->orWhere($whereString); } } $queryBuilder->setParameter(sprintf('%sValue%d', $query['field'], $i), $query['value']); $i++; } } if (isset($parameters['orderBy']) && is_array($parameters['orderBy'])) { foreach ($parameters['orderBy'] as $orderKey => $orderDirection) { $queryBuilder->addOrderBy('row.' . $orderKey, strtoupper($orderDirection)); } } return $queryBuilder; }
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; }
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) { $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); } } }
/** * 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); } } }
/** * 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())))); } }
/** * 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'])); } }
/** * @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; }
/** * {@inheritdoc} */ public function search(QueryBuilder $queryBuilder, $searchString) { $alias = $queryBuilder->getRootAliases(); $queryBuilder->orWhere('_client.name LIKE :search')->orWhere($alias[0] . '.status LIKE :search')->orWhere($alias[0] . '.total LIKE :search')->setParameter('search', "%{$searchString}%"); }
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; }
/** * 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; }
/** * Applies filtering to the query and calls back the custom filtering function, if required. * * @param QueryBuilder $qb The query builder * @param ManagerFilter $filter The query filter */ protected function applyFiltering(QueryBuilder $qb, ManagerFilter $filter) { if ($filter->getFilter() !== null && $filter->getFilterField() !== null) { $aOrWhereFields = array(); if (is_array($filter->getFilterField())) { foreach ($filter->getFilterField() as $field) { $aOrWhereFields[] = "LOWER(q." . $field . ") LIKE :filter"; } } else { $aOrWhereFields[] = "LOWER(q." . $filter->getFilterField() . ") LIKE :filter"; } foreach ($aOrWhereFields as $or) { $qb->orWhere($or); } $qb->setParameter("filter", "%" . strtolower($filter->getFilter()) . "%"); } if ($filter->getFilterCallback() !== null) { call_user_func($filter->getFilterCallback(), $qb); } }
public function handleSearch(Builder $qb, $query = null) { if (!isset($query)) { $query = $this->getRequest()->getQuery('query'); } $filters = ['attributeDesc']; if ($query) { $where = []; $params = []; $i = 0; foreach ($filters as $condition) { if (isset($query[$condition])) { if (is_array($query[$condition])) { foreach ($query[$condition] as $criteria => $values) { $qb->innerJoin('e.attributes', 'v' . $i); $qb->innerJoin('v' . $i . '.attribute', 'a' . $i); $qb->andWhere('a' . $i . '= 1'); switch ($condition) { case 'attributeDesc': $expr = $qb->expr(); $andX = $expr->andX(); $where["desc_{$i}"] = "%{$criteria}%"; $andX->add($expr->like("a" . $i . ".attributeDesc", ":desc_{$i}")); if (is_array($values)) { $j = 0; foreach ($values as $value) { $where["value_{$j}"] = "%{$value}%"; $andX->add($expr->like("v" . $i . ".value", ":value_{$j}")); $j++; } } else { $where["value_{$i}"] = "%{$values}%"; $andX->add($expr->like("v" . $i . ".value", ":value_{$i}")); } $qb->orWhere($andX); $i++; break; } } } } elseif ("" !== $query[$condition]) { $qb->innerJoin('e.attributes', 'v'); $qb->innerJoin('v.attribute', 'a'); switch ($condition) { case 'attributeDesc': $where['attributeDesc'] = "%{$query[$condition]}%"; $qb->andWhere("a.attributeDesc LIKE :attributeDesc"); break; } } } if ($where) { foreach ($where as $key => $value) { $qb->setParameter($key, $value); } } } $qb->andWhere('e.active = 1'); return $qb; }
/** * método que adiciona filtro para a pesquisa da caixa de minutas * @param \Doctrine\ORM\QueryBuilder &$queryBuilder * @param Core_Dto_Search $dto */ protected function addWhere(\Doctrine\ORM\QueryBuilder &$queryBuilder, \Core_Dto_Search $dto) { $isDate = FALSE; $data = explode('/', $dto->getDataSearch()); if (count($data) == 3) { if (checkdate($data[1], $data[0], $data[2])) { $isDate = TRUE; $newDate = $data[2] . '-' . $data[1] . '-' . $data[0]; $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->orWhere('(LOWER(vcm.status) like :query)))'); $queryBuilder->setParameter('query', '%' . $query . '%'); $queryBuilder->andWhere('vcm.sqStatusArtefato <> :sqStatusArtefato54')->setParameter('sqStatusArtefato54', \Core_Configuration::getSgdoceStatusExcluida()); } }
/** * Adds a new condition to the given QueryBuilder. * * @param QueryBuilder $qb * @param string $field The field to be used for the condition * @param string $op The operator to be used for the condition. * @param string $filter */ private function _addCondition(QueryBuilder $qb, $field, $op, $filter) { $pn = $this->_getNewParameterNumber(); $pnUse = '?' . $pn; $func = 'where'; if (strpos($field, '(') !== false) { $func = 'having'; } $havingPart = $qb->getDQLPart('having'); $wherePart = $qb->getDQLPart('where'); if ($func == 'having' && !empty($havingPart)) { $func = 'andHaving'; } elseif ($func == 'where' && !empty($wherePart)) { $func = 'andWhere'; } switch (strtolower($op)) { case 'sqlexp': $qb->{$func}($filter); break; case 'isnull': $qb->{$func}($field . ' IS NULL '); break; case 'isnotnull': $qb->{$func}($field . ' IS NOT NULL '); break; case 'empty': $expr = $qb->expr()->eq($field, ''); $qb->{$func}($expr); break; case 'equal': case '=': $expr = $qb->expr()->eq($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case 'regex': $qb->{$func}($field . " REGEXP " . $pnUse); $qb->setParameter($pn, $filter); break; case 'rlike': $expr = $qb->expr()->like($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter . "%"); break; case 'llike': $expr = $qb->expr()->like($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, "%" . $filter); break; case '>=': $expr = $qb->expr()->gte($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case '>': $expr = $qb->expr()->gt($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case '<>': case '!=': $expr = $qb->expr()->neq($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case '<=': $expr = $qb->expr()->lte($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case '<': $expr = $qb->expr()->lt($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, $filter); break; case 'in': $filter = explode(',', $filter); $qb->{$func}($qb->expr()->in($field, $filter)); break; case '&': case 'and': case 'AND': case 'flag': case 'FLAG': $qb->{$func}($field . " & " . $pnUse . " <> 0"); $qb->setParameter($pn, $filter); break; case 'range': $pn2 = $this->_getNewParameterNumber(); $pn2Use = '?' . $pn2; $expr = $qb->expr()->between($field, $pnUse, $pn2Use); $start = substr($filter, 0, strpos($filter, '<>')); $end = substr($filter, strpos($filter, '<>') + 2); $qb->{$func}($expr); $qb->setParameter($pn, $start); $qb->setParameter($pn2, $end); break; case '||': $expr = $qb->expr()->like($field, $pnUse); $qb->orWhere($expr); $qb->setParameter($pn, "%" . $filter . "%"); break; case 'like': default: $expr = $qb->expr()->like($field, $pnUse); $qb->{$func}($expr); $qb->setParameter($pn, "%" . $filter . "%"); break; } return $this; }
/** * Adiciona parametros para realizar o filtro da grid * @param \Doctrine\ORM\QueryBuilder $query * @param \Core_Dto_Abstract $dto */ public function addWhere(\Doctrine\ORM\QueryBuilder $query, \Core_Dto_Abstract $dto) { $inputs = array('tp.sqTipoPessoa' => $dto->getSqTipoPessoa(), 'pf.nuCpf' => $dto->getNuCpf(), 'pj.nuCnpj' => $dto->getNuCnpj(), 'p.stRegistroAtivo' => $dto->getStRegistroAtivo()); switch ($dto->getSqTipoPessoa()) { case srvPessoa::SQ_TIPO_PESSOA_FISICA: $inputs['p.noPessoa'] = $dto->getNoPessoaFisica(); break; case srvPessoa::SQ_TIPO_PESSOA_JURIDICA: $inputs['p.noPessoa'] = $dto->getNoPessoaJuridica(); $inputs['nj.sqNaturezaJuridicaPai'] = $dto->getSqNaturezaJuridicaPai(); $inputs['nj.sqNaturezaJuridica'] = $dto->getSqNaturezaJuridica(); break; default: $inputs['p.noPessoa'] = $dto->getNoPessoaSemClass(); break; } $countParams = 0; foreach ($inputs as $field => $value) { if ($value != '') { if (strstr($field, 'sq') || strstr($field, 'nu') || strstr($field, 'st')) { if ($field == 'tp.sqTipoPessoa' && $value == srvPessoa::SEM_CLASSIFICACAO) { $query->andWhere($this->_em->createQueryBuilder()->expr()->in('tp.sqTipoPessoa', ':expre'))->setParameter('expre', array(srvPessoa::SQ_TIPO_PESSOA_FISICA, srvPessoa::SQ_TIPO_PESSOA_JURIDICA)); } else { $query->andWhere($field . ' = ?' . $countParams)->setParameter($countParams, \Zend_Filter::filterStatic($value, 'Digits')); } } else { $expre = $query->expr()->lower($query->expr()->trim($field)); $value = "%" . mb_strtolower(trim($value), 'UTF-8') . "%"; $query->andWhere($query->expr()->like('clear_accentuation(' . $expre . ')', $query->expr()->literal($this->translate($value)))); if ($dto->getSqTipoPessoa() == srvPessoa::SEM_CLASSIFICACAO || $dto->getSqTipoPessoa() == srvPessoa::SQ_TIPO_PESSOA_JURIDICA) { $expre = $query->expr()->lower($query->expr()->trim('pj.noFantasia')); $query->orWhere($query->expr()->like('clear_accentuation(' . $expre . ')', $query->expr()->literal($this->translate($value)))); } } $countParams++; } } }
/** * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param $filter * * @return QueryBuilder */ private function processFilter(QueryBuilder $queryBuilder, $filter) { if (isset($filter['searchString']) && '' !== $filter['searchString']) { $locationExpr = $queryBuilder->expr()->orX(); foreach (explode(' ', $filter['searchString']) as $keywordIndex => $keyword) { $queryParameter = 'searchString' . $keywordIndex; $queryBuilder->andWhere('event.title LIKE :' . $queryParameter); $queryBuilder->setParameter($queryParameter, '%' . trim($keyword) . '%'); $locationExpr->add($queryBuilder->expr()->like('event.city', ':' . $queryParameter)); } if (!isset($filter['lat']) && !isset($filter['long'])) { $queryBuilder->orWhere($locationExpr); } } if (isset($filter['eventIds'])) { $queryBuilder->andWhere('event.id IN (:eventIds)'); $queryBuilder->setParameter('eventIds', $filter['eventIds']); } if (isset($filter['isTopEvent'])) { $queryBuilder->andWhere('event.isTopEvent = :isTopEvent'); $queryBuilder->setParameter('isTopEvent', $filter['isTopEvent']); } if (isset($filter['categories']) && '' !== $filter['categories']) { $queryBuilder->leftJoin('event.categories', 'eventCategories'); $queryBuilder->andWhere('eventCategories.id IN (:categories)'); $queryBuilder->setParameter('categories', $filter['categories']); } if (isset($filter['dateFrom']) && '' !== $filter['dateFrom']) { /** @var DateTime $dateFrom */ $dateFrom = $filter['dateFrom']; $queryBuilder->andWhere('event.startDate >= :startDate'); $queryBuilder->setParameter('startDate', $dateFrom->format('Y-m-d')); } if (isset($filter['dateFrom']) && '' !== $filter['dateFrom'] && (isset($filter['dateTo']) && '' !== $filter['dateTo'])) { /** @var DateTime $dateTo */ $dateTo = $filter['dateTo']; $queryBuilder->andWhere('event.startDate <= :startEnd'); $queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d')); } elseif (isset($filter['dateTo']) && '' !== $filter['dateTo']) { /** @var DateTime $dateTo */ $dateTo = $filter['dateTo']; $queryBuilder->andWhere('event.startDate <= :startEnd'); $queryBuilder->setParameter('startEnd', $dateTo->format('Y-m-d')); } if (isset($filter['lat']) && isset($filter['long'])) { $queryBuilder->addSelect('( 3959 * acos(cos(radians(:latitude)) ' . '* cos(radians(event.latitude)) ' . '* cos(radians(event.longitude) ' . '- radians(:longitude)) ' . '+ sin(radians(:latitude)) ' . '* sin(radians(event.latitude)))) AS distance'); $queryBuilder->setParameter('latitude', $filter['lat']); $queryBuilder->setParameter('longitude', $filter['long']); $queryBuilder->having('distance <= :area'); $queryBuilder->setParameter('area', $filter['area']); $queryBuilder->orderBy('distance', 'ASC'); } else { $queryBuilder->orderBy('event.startDate', 'ASC'); } if (isset($filter['country'])) { $queryBuilder->andWhere('event.country = :country'); $queryBuilder->setParameter('country', $filter['country']); } return $queryBuilder; }
/** * 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 . '%'); } }
/** * @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); } }
public function handleSearch(Builder $qb) { $query = $this->getRequest()->getQuery(); $filters = ['description']; if ($query) { $where = []; $params = []; foreach ($filters as $condition) { if (isset($query[$condition]) && "" !== $query[$condition]) { switch ($condition) { case 'description': $where['description'] = "%{$query[$condition]}%"; $qb->andWhere("e.description LIKE :description"); $qb->orWhere("e.name LIKE :description"); break; } } } if ($where) { foreach ($where as $key => $value) { $qb->setParameter($key, $value); } } } $qb->andWhere('e.active = 1'); return $qb; }
private static function likeExpr(QueryBuilder $qb, $field, $value) { $qb->orWhere($qb->expr()->like(sprintf('LOWER(a.%s)', $field), $qb->expr()->literal($value))); }
/** * @param QueryBuilder $qb * @param array $value */ protected function addWhere(QueryBuilder $qb, $value) { if (isset($value['where'])) { if (isset($value['where']['and'])) { foreach ((array) $value['where']['and'] as $where) { $qb->andWhere($where); } } if (isset($value['where']['or'])) { foreach ((array) $value['where']['or'] as $where) { $qb->orWhere($where); } } } }
/** * @param QueryBuilder $queryBuilder * @param Request $request * @param bool $or */ private function prepareParams(QueryBuilder $queryBuilder, Request $request, $or) { $firstQuery = true; if (!empty($request->query->get('search'))) { $searchItems = explode(',', $request->query->get('search')); foreach ($searchItems as $item) { $query = explode(':', $item); if ($firstQuery) { $queryBuilder->where('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]); $firstQuery = false; } else { if ($or) { $queryBuilder->orWhere('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]); } else { $queryBuilder->andWhere('R.' . $query[0] . " " . $query[1] . ' :' . $query[0]); } } } $searchItems = explode(',', $request->query->get('search')); foreach ($searchItems as $item) { $parameter = explode(':', $item); $queryBuilder->setParameter($parameter[0], $parameter[2]); } if (!empty($request->query->get('orderBy'))) { $order = explode(',', $request->query->get('orderBy')); $queryBuilder->orderBy('R.' . $order[0], $order[1]); } } }