/** * 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); } } }
/** * Add labels. * * @param Search $entity * * @return Builder */ public function addLabels(Search $entity) { if ($entity->getLabels()->count()) { $this->add(function (QueryBuilder $query) use($entity) { $ids = []; foreach ($entity->getLabels() as $label) { $ids[] = (int) $label->getId(); } $query->innerJoin('i.labels', 'l')->andWhere('l.id IN (' . implode(',', $ids) . ')'); }); $this->select->andHaving('COUNT(i.id) = ' . $entity->getLabels()->count()); } return $this; }
/** * @param QueryBuilder $queryBuilder * * @return void */ public function apply($queryBuilder) { $compiler = new SqlFilterCompiler(); // @codingStandardsIgnoreStart // $compiler->setSkipMissingProperties(true); // @codingStandardsIgnoreEnd $compiler->compile($this->properties, $this->filter); $select = $compiler->getSelect(false) and $queryBuilder->addSelect($select); $where = $compiler->getWhere(false) and $queryBuilder->andWhere($where); $having = $compiler->getHaving(false) and $queryBuilder->andHaving($having); $order = $compiler->getOrder(false) and $queryBuilder->addOrderBy($order); foreach ($compiler->getParameters() as $key => $value) { $queryBuilder->setParameter($key, $value); } }
/** * @param QueryBuilder $qb */ public function applyRestrictions(QueryBuilder $qb) { if (!$this->groupNode) { return; } $violationList = $this->validator->validate($this->groupNode, new GroupNodeConstraint()); foreach ($violationList as $violation) { throw new LogicException($violation->getMessage()); } list($uncomputedExpr, $computedExpr) = $this->resolveGroupNode($this->groupNode); if ($computedExpr) { $qb->andHaving($computedExpr); } if ($uncomputedExpr) { $qb->andWhere($uncomputedExpr); } }
/** * * @param type $columns * @param type $page * @param type $limit * @param type $maxResults * @param type $gridDataJunction * @return \APY\DataGridBundle\Grid\Rows */ public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION) { $this->query = $this->getQueryBuilder(); $this->querySelectfromSource = clone $this->query; $bindIndex = 123; $serializeColumns = array(); $where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx(); foreach ($columns as $column) { if (!in_array($column->getId(), $this->excludedColumns)) { $fieldName = $this->getFieldName($column, true); $this->query->addSelect($fieldName); $this->querySelectfromSource->addSelect($fieldName); if ($column->isSorted()) { $this->query->orderBy($this->getFieldName($column), $column->getOrder()); } if ($column->isFiltered()) { // Some attributes of the column can be changed in this function $filters = $column->getFilters('entity'); $isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION; $hasHavingClause = $column->hasDQLFunction(); $sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where); foreach ($filters as $filter) { // \Doctrine\Common\Util\Debug::dump($column); $operator = $this->normalizeOperator($filter->getOperator()); $q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}"); if ($filter->getOperator() == Column::OPERATOR_NLIKE) { $q = $this->query->expr()->not($q); } $sub->add($q); if ($filter->getValue() !== null) { $this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue())); } } if ($hasHavingClause) { $this->query->andHaving($sub); } elseif ($isDisjunction) { $where->add($sub); } } if ($column->getType() === 'array') { $serializeColumns[] = $column->getId(); } } } if ($where->count() > 0) { //Using ->andWhere here to make sure we preserve any other where clauses present in the query builder //the other where clauses may have come from an external builder $this->query->andWhere($where); } foreach ($this->joins as $alias => $field) { if (null !== $field['type'] && strtolower($field['type']) === 'inner') { $join = 'join'; } else { $join = 'leftJoin'; } $this->query->{$join}($field['field'], $alias); $this->querySelectfromSource->{$join}($field['field'], $alias); } if ($page > 0) { $this->query->setFirstResult($page * $limit); } if ($limit > 0) { if ($maxResults !== null && $maxResults - $page * $limit < $limit) { $limit = $maxResults - $page * $limit; } $this->query->setMaxResults($limit); } elseif ($maxResults !== null) { $this->query->setMaxResults($maxResults); } if (!empty($this->groupBy)) { $this->query->resetDQLPart('groupBy'); $this->querySelectfromSource->resetDQLPart('groupBy'); foreach ($this->groupBy as $field) { $this->query->addGroupBy($this->getGroupByFieldName($field)); $this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field)); } } if ($this->groupById === true) { $this->query->addGroupBy($this->getGroupByFieldName('id')); } //call overridden prepareQuery or associated closure $this->prepareQuery($this->query); $query = $this->query->getQuery(); foreach ($this->hints as $hintKey => $hintValue) { $query->setHint($hintKey, $hintValue); } $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'TMSolution\\DataGridBundle\\Walker\\MysqlWalker'); $query->setHint("mysqlWalker.count", true); $items = $query->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY); $this->prepareTotalCount(); // $oids = array(); // foreach ($items as $item) { // // $oid = ObjectIdentity::fromDomainObject($item); // $oids[] = $oid; // } // // $this->aclProvider->findAcls($oids); // preload Acls from database // // foreach ($items as $item) { // if (false === $this->securityContext->isGranted('VIEW', $item)) { // // denied // throw new AccessDeniedException(); // } // } // var_dump($items); $repository = $this->manager->getRepository($this->entityName); // Force the primary field to get the entity in the manipulatorRow $primaryColumnId = null; foreach ($columns as $column) { if ($column->isPrimary()) { $primaryColumnId = $column->getId(); break; } } // hydrate result $result = new Rows(); foreach ($items as $item) { $row = new Row(); foreach ($item as $key => $value) { $key = str_replace('::', '.', $key); if (in_array($key, $serializeColumns) && is_string($value)) { //echo $value."\n"; @($value = unserialize($value)); } $row->setField($key, $value); } $row->setPrimaryField($primaryColumnId); //Setting the representative repository for entity retrieving $row->setRepository($repository); //call overridden prepareRow or associated closure if (($modifiedRow = $this->prepareRow($row)) != null) { $result->addRow($modifiedRow); } } return $result; }
/** * Adds additional where/having clauses for given dataset's column * * @param QueryBuilder $queryBuilder * @param ColumnInterface $column * @param ConditionInterface $condition */ protected function addColumnConditionToQueryBuilder(QueryBuilder $queryBuilder, ColumnInterface $column, ConditionInterface $condition) { $source = $column->getSource(); $alias = $column->getAlias(); $operator = $condition->getOperator(); $identifier = sprintf('%s_%s', $condition->getIdentifier(), $this->paramIteration++); if ($column->isAggregated()) { $expression = $queryBuilder->expr()->{$operator}($alias, ':' . $identifier); $queryBuilder->andHaving($expression); } else { $expression = $queryBuilder->expr()->{$operator}($source, ':' . $identifier); $queryBuilder->andWhere($expression); } $queryBuilder->setParameter($identifier, $condition->getValue()); }
/** * @param \APY\DataGridBundle\Grid\Column\Column[] $columns * @param int $page Page Number * @param int $limit Rows Per Page * @param int $gridDataJunction Grid data junction * @return \APY\DataGridBundle\Grid\Rows */ public function execute($columns, $page = 0, $limit = 0, $maxResults = null, $gridDataJunction = Column::DATA_CONJUNCTION) { $this->query = $this->getQueryBuilder(); $this->querySelectfromSource = clone $this->query; $bindIndex = 123; $serializeColumns = array(); $where = $gridDataJunction === Column::DATA_CONJUNCTION ? $this->query->expr()->andx() : $this->query->expr()->orx(); foreach ($columns as $column) { // If a column is a manual field, ie a.col*b.col as myfield, it is added to select from user. if ($column->getIsManualField() === false) { $fieldName = $this->getFieldName($column, true); $this->query->addSelect($fieldName); $this->querySelectfromSource->addSelect($fieldName); } if ($column->isSorted()) { $this->query->orderBy($this->getFieldName($column), $column->getOrder()); } if ($column->isFiltered()) { // Some attributes of the column can be changed in this function $filters = $column->getFilters('entity'); $isDisjunction = $column->getDataJunction() === Column::DATA_DISJUNCTION; $hasHavingClause = $column->hasDQLFunction() || $column->getIsAggregate(); $sub = $isDisjunction ? $this->query->expr()->orx() : ($hasHavingClause ? $this->query->expr()->andx() : $where); foreach ($filters as $filter) { $operator = $this->normalizeOperator($filter->getOperator()); $q = $this->query->expr()->{$operator}($this->getFieldName($column, false), "?{$bindIndex}"); if ($filter->getOperator() == Column::OPERATOR_NLIKE) { $q = $this->query->expr()->not($q); } $sub->add($q); if ($filter->getValue() !== null) { $this->query->setParameter($bindIndex++, $this->normalizeValue($filter->getOperator(), $filter->getValue())); } } if ($hasHavingClause) { $this->query->andHaving($sub); } elseif ($isDisjunction) { $where->add($sub); } } if ($column->getType() === 'array') { $serializeColumns[] = $column->getId(); } } if ($where->count() > 0) { //Using ->andWhere here to make sure we preserve any other where clauses present in the query builder //the other where clauses may have come from an external builder $this->query->andWhere($where); } foreach ($this->joins as $alias => $field) { if (null !== $field['type'] && strtolower($field['type']) === 'inner') { $join = 'join'; } else { $join = 'leftJoin'; } $this->query->{$join}($field['field'], $alias); $this->querySelectfromSource->{$join}($field['field'], $alias); } if ($page > 0) { $this->query->setFirstResult($page * $limit); } if ($limit > 0) { if ($maxResults !== null && $maxResults - $page * $limit < $limit) { $limit = $maxResults - $page * $limit; } $this->query->setMaxResults($limit); } elseif ($maxResults !== null) { $this->query->setMaxResults($maxResults); } if (!empty($this->groupBy)) { $this->query->resetDQLPart('groupBy'); $this->querySelectfromSource->resetDQLPart('groupBy'); foreach ($this->groupBy as $field) { $this->query->addGroupBy($this->getGroupByFieldName($field)); $this->querySelectfromSource->addGroupBy($this->getGroupByFieldName($field)); } } //call overridden prepareQuery or associated closure $this->prepareQuery($this->query); $query = $this->query->getQuery(); foreach ($this->hints as $hintKey => $hintValue) { $query->setHint($hintKey, $hintValue); } $items = $query->getResult(); $repository = $this->manager->getRepository($this->entityName); // Force the primary field to get the entity in the manipulatorRow $primaryColumnId = null; foreach ($columns as $column) { if ($column->isPrimary()) { $primaryColumnId = $column->getId(); break; } } // hydrate result $result = new Rows(); foreach ($items as $item) { $row = new Row(); foreach ($item as $key => $value) { $key = str_replace('::', '.', $key); if (in_array($key, $serializeColumns) && is_string($value)) { $value = unserialize($value); } $row->setField($key, $value); } $row->setPrimaryField($primaryColumnId); //Setting the representative repository for entity retrieving $row->setRepository($repository); //call overridden prepareRow or associated closure if (($modifiedRow = $this->prepareRow($row)) != null) { $result->addRow($modifiedRow); } } return $result; }
/** * @param Table $table * @param Request $request * @param \Doctrine\ORM\QueryBuilder $queryBuilder */ private function addSearch(Table $table, Request $request, \Doctrine\ORM\QueryBuilder $queryBuilder) { $queryParams = $request->get($table->getFormId()); // @todo handle all kind of filters foreach ($table->getAllFilters() as $filter) { if (isset($queryParams[$filter->getName()])) { $searchParamRaw = trim($queryParams[$filter->getName()]); list($operator, $searchParam) = $filter->getOperatorAndValue($searchParamRaw); if ((string) $searchParam != '') { list($searchOperator, $formattedSearch) = $filter->getFormattedInput($operator, $searchParam); $sql = false; // depending on operator switch ($searchOperator) { case Filter::TYPE_GREATER: case Filter::TYPE_GREATER_OR_EQUAL: case Filter::TYPE_LESS: case Filter::TYPE_LESS_OR_EQUAL: case Filter::TYPE_NOT_EQUAL: $sql = $filter->getField() . " {$searchOperator} :filter_" . $filter->getName(); $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch); break; case Filter::TYPE_EQUAL_STRICT: $sql = $filter->getField() . ' = :filter_' . $filter->getName(); $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch); break; case Filter::TYPE_EQUAL: $sql = $filter->getField() . ' like :filter_' . $filter->getName(); $queryBuilder->setParameter('filter_' . $filter->getName(), $formattedSearch); break; case Filter::TYPE_NOT_LIKE: $sql = $filter->getField() . ' not like :filter_' . $filter->getName(); $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%'); break; case Filter::TYPE_NULL: $sql = $filter->getField() . ' IS NULL'; break; case Filter::TYPE_NOT_NULL: $sql = $filter->getField() . ' IS NOT NULL'; break; case Filter::TYPE_IN: $sql = $filter->getField() . ' IN (:filter_' . $filter->getName() . ')'; $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%'); break; case Filter::TYPE_NOT_IN: $sql = $filter->getField() . ' NOT IN (:filter_' . $filter->getName() . ')'; $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%'); break; default: case Filter::TYPE_LIKE: $sql = $filter->getField() . ' like :filter_' . $filter->getName(); $queryBuilder->setParameter('filter_' . $filter->getName(), '%' . $formattedSearch . '%'); break; } if (!is_null($sql)) { if ($filter->getHaving()) { $queryBuilder->andHaving($sql); } else { $queryBuilder->andWhere($sql); } } } } } }
/** * Traite les conditions de type HAVING de la requête. * * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder * @param array $conditions Conditions de la recherche * @return \Doctrine\ORM\QueryBuilder QueryBuilder à jour */ private function processQueryBuilderHavings(QueryBuilder $queryBuilder, array $conditions) { foreach ($conditions as $conditionPropriete => $conditionValeur) { if ($this->conditionIsHaving($conditionPropriete)) { $queryBuilder->andHaving($this->processQueryBuilderHaving($queryBuilder, $conditionPropriete, $conditionValeur)); } } return $queryBuilder; }