/**
* 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;
}
