/** * Updates Doctrine's query builder for this comparator. * * @param \Doctrine\ORM\QueryBuilder $queryBuilder * @param string $alias */ public function updateQb(\Doctrine\ORM\QueryBuilder $queryBuilder, $alias) { // Clear ORDER BY, multiple comparators not allowed. $queryBuilder->add('orderBy', ''); foreach ($this->order as $property => $order) { $queryBuilder->add('orderBy', $alias . '.' . $property . ' ' . $order, true); } }
/** * Load the collection * */ protected function initialize() { if ($this->initialized) { return; } // Handle search if (isset($this->searchExpression)) { $queryBuilderExpression = $this->buildQueryBuilderExpression($this->searchExpression); $this->queryBuilder->andWhere($queryBuilderExpression); } // Handle filters if (isset($this->filterExpression)) { $queryBuilderExpression = $this->buildQueryBuilderExpression($this->filterExpression); $this->queryBuilder->andWhere($queryBuilderExpression); } // Handle sort if (null !== $this->sortField && null !== $this->sortDirection) { $oldOrderBys = $this->queryBuilder->getDQLPart('orderBy'); $this->queryBuilder->resetDQLPart('orderBy'); $this->queryBuilder->orderBy($this->sortField, $this->sortDirection); foreach ($oldOrderBys as $oldOrderBy) { $this->queryBuilder->add('orderBy', $oldOrderBy, true); } } // Handle pagination if (isset($this->limitPerPage)) { $paginator = new DoctrineORMPaginator($this->queryBuilder->getQuery()); $paginator->setLimitPerPage($this->limitPerPage)->setRangeLimit($this->rangeLimit)->setPage($this->page); $this->iterator = $paginator->getIterator(); $this->paginator = $paginator; } else { $items = $this->queryBuilder->getQuery()->getResult(); $this->iterator = new \ArrayIterator($items); $this->paginator = null; } $this->initialized = true; }
/** * @param QueryBuilder $queryBuilder * @param bool $isHead */ protected function applyHeadFilter(QueryBuilder $queryBuilder, $isHead = true) { $queryBuilder->add('where', $queryBuilder->expr()->andX($queryBuilder->expr()->eq('e.head', ':head')))->setParameters(['head' => (bool) $isHead]); }
/** * Adds ORDER BY clause to given query builder. * * @param Doctrine\ORM\QueryBuilder $qb Given query builder instance. * @param string $orderBy The order-by clause to use when retrieving the collection (optional) (default=''). * * @return Doctrine\ORM\QueryBuilder query builder instance to be further processed */ protected function genericBaseQueryAddOrderBy(QueryBuilder $qb, $orderBy = '') { if ($orderBy == 'RAND()') { // random selection $qb->addSelect('MOD(tbl.id, ' . mt_rand(2, 15) . ') AS randomIdentifiers')->add('orderBy', 'randomIdentifiers'); $orderBy = ''; } elseif (empty($orderBy)) { $orderBy = $this->defaultSortingField; } // add order by clause if (!empty($orderBy)) { if (strpos($orderBy, '.') === false) { $orderBy = 'tbl.' . $orderBy; } $qb->add('orderBy', $orderBy); } return $qb; }
/** * Prepare certain search condition * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * @param string|null $value Condition data * * @return void */ protected function prepareCndSubstring(\Doctrine\ORM\QueryBuilder $queryBuilder, $value) { $searchWords = $this->getSearchWords($value); $searchPhrase = implode(' ', $searchWords); $cnd = new \Doctrine\ORM\Query\Expr\Orx(); foreach ($this->getSubstringSearchFields() as $field) { foreach ($searchWords as $index => $word) { // Collect OR expressions $cnd->add($field . ' LIKE :word' . $index); $queryBuilder->setParameter('word' . $index, '%' . $word . '%'); } } if ($searchPhrase) { $queryBuilder->addSelect(sprintf('RELEVANCE(\'%s\', %s, %s) as relevance', $value, $this->getRelevanceTitleField(), $this->getRelevanceTextField())); $orderBys = $queryBuilder->getDQLPart('orderBy'); $queryBuilder->resetDQLPart('orderBy'); $queryBuilder->addOrderBy('relevance', 'desc'); foreach ($orderBys as $value) { $queryBuilder->add('orderBy', $value, true); } } $queryBuilder->andWhere($cnd); }
/** * @param QueryBuilder $queryBuilder * @param string $dqlPartName ("join", "select", ...) */ public function cleanQueryBuilderDqlPart(QueryBuilder $queryBuilder, $dqlPartName) { $dqlPart = $queryBuilder->getDQLPart($dqlPartName); $newDqlPart = []; if (count($dqlPart)) { $queryBuilder->resetDQLPart($dqlPartName); if ($dqlPartName == 'join') { foreach ($dqlPart as $root => $elements) { foreach ($elements as $element) { preg_match('/^(?P<joinType>[^ ]+) JOIN (?P<join>[^ ]+) (?P<alias>[^ ]+)/', $element->__toString(), $matches); if (!array_key_exists($matches['alias'], $newDqlPart)) { $newDqlPart[$matches['alias']] = $element; } } $dqlPart[$root] = array_values($newDqlPart); } $dqlPart = array_shift($dqlPart); foreach ($dqlPart as $element) { $queryBuilder->add($dqlPartName, [$element], true); } return; } foreach ($dqlPart as $element) { $newDqlPart[$element->__toString()] = $element; } $dqlPart = array_values($newDqlPart); foreach ($dqlPart as $element) { $queryBuilder->add($dqlPartName, $element, true); } } }
/** * Check if a Snippet is disabled * Internal use only * * @param Doctrine\ORM\QueryBuilder $queryBuilder * @param mixed $result * @param string $show */ protected function checkIfSnippetIsDisabled($queryBuilder, $result, $show) { if ($show == 'enabled' && is_null($result)) { $expr = $queryBuilder->getDQLPart('where')->getParts(); $newExpr = new \Doctrine\ORM\Query\Expr\Andx(); $newExpr->addMultiple(preg_grep("/\\bsnippet.enabled\\b/i", $expr, PREG_GREP_INVERT)); $queryBuilder->resetDQLPart('where'); $queryBuilder->add('where', $newExpr); if (!is_null($queryBuilder->getQuery()->getOneOrNullResult())) { throw new \Exception('Result was found but disabled.'); } } return $result; }
/** * Adds a search criteria to the given query builder based on the given query string * * @param QueryBuilder $queryBuilder The query builder * @param string $search The search string */ protected function addSearchCriteria(QueryBuilder $queryBuilder, $search) { $queryBuilder->add('where', $queryBuilder->expr()->orX($queryBuilder->expr()->like($queryBuilder->expr()->concat('user.firstName', $queryBuilder->expr()->concat($queryBuilder->expr()->literal(' '), 'user.lastName')), '?1'), $queryBuilder->expr()->like($queryBuilder->expr()->concat('user.lastName', $queryBuilder->expr()->concat($queryBuilder->expr()->literal(' '), 'user.firstName')), '?1'), $queryBuilder->expr()->like('user.username', '?1')))->setParameter(1, '%' . str_replace(' ', '%', $search) . '%'); }
private static function getTotalResult(QueryBuilder $qb, $class) { if (UtilRepository2::getSession()->get("total") === false) { return 0; } $select = $qb->getDQLPart("select"); $qb->select("count(distinct {$class}.id)"); // print $qb->getQuery()->getSQL();die; $r = $qb->getQuery()->getSingleScalarResult(); $qb->resetDQLPart("select"); foreach ($select as $expr) { $qb->add("select", $expr); } return $r; //Esto es para si por esta via no se puede obtener un unico resultado en la consulta(esto pasa a veces con group by a queries con leftJoin) // if(UtilRepository2::getSession()->get("total") !== false) { // // $select = $qb->getDQLPart("select"); // $qb->select("count($class.id)"); // $r = $qb->getQuery()->getSingleScalarResult(); // $qb->resetDQLPart("select"); // foreach ($select as $expr) // $qb->add("select", $expr); // // return $r; // } // else // { // $select = $qb->getDQLPart("select"); // $qb->select("$class.id"); // // $r = count($qb->getQuery()->getScalarResult()); // $qb->resetDQLPart("select"); // foreach ($select as $expr) // $qb->add("select", $expr); // // return $r; // } }
/** * {@inheritdoc} */ public function parse($value, QueryBuilder $qb) { if (!is_array($value)) { $value = Yaml::parse($value); } $processor = new Processor(); $value = $processor->processConfiguration(new QueryConfiguration(), $value); if (!isset($value['from'])) { throw new \RuntimeException('Missing mandatory "from" section'); } foreach ((array) $value['from'] as $from) { $qb->from($from['table'], $from['alias']); } if (isset($value['select'])) { foreach ($value['select'] as $select) { $qb->add('select', new Expr\Select($select), true); } } if (isset($value['distinct'])) { $qb->distinct((bool) $value['distinct']); } if (isset($value['groupBy'])) { $qb->groupBy($value['groupBy']); } if (isset($value['having'])) { $qb->having($value['having']); } $this->addJoin($qb, $value); $this->addWhere($qb, $value); $this->addOrder($qb, $value); return $qb; }