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