/** * @param QueryBuilder $qb * @param string $dqlAlias */ public function modify(QueryBuilder $qb, $dqlAlias) { if ($this->dqlAlias !== null) { $dqlAlias = $this->dqlAlias; } $qb->addGroupBy(sprintf('%s.%s', $dqlAlias, $this->field)); }
/** * Prepare query builder to get modules list * * @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare * * @return void */ protected function addGroupByCondition(\Doctrine\ORM\QueryBuilder $queryBuilder) { $queryBuilder->addGroupBy('m.name')->addGroupBy('m.author'); }
/** * * @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; }
/** * Add 'Group By product_id' expression * * @param \Doctrine\ORM\QueryBuilder $qb Query builder routine * * @return void */ protected function addGroupByProductId(\Doctrine\ORM\QueryBuilder $qb) { $qb->addGroupBy('p.product_id'); }
/** * @param DateTime $start * @param DateTime $end * @param QueryBuilder $qb * @param $entityField */ public function addDatePartsSelect(DateTime $start, DateTime $end, QueryBuilder $qb, $entityField) { switch ($this->getFormatStrings($start, $end)['viewType']) { case 'year': $qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField))); $qb->addGroupBy('yearCreated'); break; case 'month': $qb->addSelect(sprintf('%s as yearCreated', $this->getEnforcedTimezoneFunction('YEAR', $entityField))); $qb->addSelect(sprintf('%s as monthCreated', $this->getEnforcedTimezoneFunction('MONTH', $entityField))); $qb->addGroupBy('yearCreated'); $qb->addGroupBy('monthCreated'); break; case 'date': $qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField))); $qb->addSelect(sprintf('%s as weekCreated', $this->getEnforcedTimezoneFunction('WEEK', $entityField))); $qb->addGroupBy('yearCreated'); $qb->addGroupBy('weekCreated'); break; case 'day': $qb->addSelect(sprintf("%s as yearCreated", $this->getEnforcedTimezoneFunction('YEAR', $entityField))); $qb->addSelect(sprintf("%s as monthCreated", $this->getEnforcedTimezoneFunction('MONTH', $entityField))); $qb->addSelect(sprintf("%s as dayCreated", $this->getEnforcedTimezoneFunction('DAY', $entityField))); $qb->addGroupBy('yearCreated'); $qb->addGroupBy('monthCreated'); $qb->addGroupBy('dayCreated'); break; case 'time': $qb->addSelect(sprintf('%s as dateCreated', $this->getEnforcedTimezoneFunction('DATE', $entityField))); $qb->addSelect(sprintf('%s as hourCreated', $this->getEnforcedTimezoneFunction('HOUR', $entityField))); $qb->addGroupBy('dateCreated'); $qb->addGroupBy('hourCreated'); break; } }
/** * @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 \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->manager->createQueryBuilder($this->class); $this->query->from($this->class, self::TABLE_ALIAS); $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) { $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) { $operator = $this->normalizeOperator($filter->getOperator()); $q = $this->query->expr()->{$operator}($this->getFieldName($column, false, $hasHavingClause), "?{$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->having($sub); } elseif ($isDisjunction) { $where->add($sub); } } if ($column->getType() === 'array') { $serializeColumns[] = $column->getId(); } } if ($where->count() > 0) { $this->query->where($where); } foreach ($this->joins as $alias => $field) { $this->query->leftJoin($field, $alias); $this->querySelectfromSource->leftJoin($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(); // 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); } //call overridden prepareRow or associated closure if (($modifiedRow = $this->prepareRow($row)) != null) { $result->addRow($modifiedRow); } } return $result; }
/** * Traite les extras pour la requête. * * @param \Doctrine\ORM\QueryBuilder $queryBuilder QueryBuilder * @param array $extras Extras * @return \Doctrine\ORM\QueryBuilder QueryBuilder à jour */ private function processQueryBuilderExtras(QueryBuilder $queryBuilder, array $extras) { if (isset($extras[self::SELECTS])) { foreach ($extras[self::SELECTS] as $select => $selectAlias) { if (self::SELECT_JOIN == $selectAlias) { $queryBuilder->addSelect($select); } else { $queryBuilder->addSelect($this->getCompleteProperty($select) . ' AS ' . $selectAlias); } } } if (isset($extras[self::LEFT_JOINS])) { foreach ($extras[self::LEFT_JOINS] as $leftJoin => $leftJoinAlias) { $queryBuilder->leftJoin($this->getCompleteProperty($leftJoin), $leftJoinAlias); } } if (isset($extras[self::INNER_JOINS])) { foreach ($extras[self::INNER_JOINS] as $innerJoin => $innerJoinAlias) { $queryBuilder->innerJoin($this->getCompleteProperty($innerJoin), $innerJoinAlias); } } if (isset($extras[self::GROUP_BYS])) { foreach ($extras[self::GROUP_BYS] as $groupBy) { if (isset($extras[self::SELECTS]) && in_array($groupBy, array_values($extras[self::SELECTS]))) { $queryBuilder->addGroupBy($groupBy); } else { $queryBuilder->addGroupBy($this->getCompleteProperty($groupBy)); } } } return $queryBuilder; }