/**
* @param QueryBuilder $qb
* @param string $dqlAlias
*/
public function modify(QueryBuilder $qb, $dqlAlias)
{
if ($this->dqlAlias !== null) {
$dqlAlias = $this->dqlAlias;
}
$qb->groupBy(sprintf('%s.%s', $dqlAlias, $this->field));
}
/**
* @return \Doctrine\ORM\QueryBuilder
*/
private function createQueryBuilder()
{
$this->queryBuilder = $this->em->createQueryBuilder()->from($this->entityName, $this->entityName);
foreach ($this->getJoins() as $entity => $join) {
switch ($join->getJoinMethod()) {
case DoctrineJoinDescriptor::JOIN_METHOD_LEFT:
$this->queryBuilder->leftJoin($join->getJoin(), $entity, $join->getJoinConditionMethod(), $join->getJoinCondition());
break;
case DoctrineJoinDescriptor::JOIN_METHOD_INNER:
$this->queryBuilder->innerJoin($join->getJoin(), $entity, $join->getJoinConditionMethod(), $join->getJoinCondition());
break;
}
}
// set where
if (!empty($this->whereFields)) {
$this->addWheres($this->whereFields, $this->whereValues, self::WHERE_COMPARATOR_EQUAL);
}
// set where not
if (!empty($this->whereNotFields)) {
$this->addWheres($this->whereNotFields, $this->whereNotValues, self::WHERE_COMPARATOR_UNEQUAL);
}
if (!empty($this->groupByFields)) {
foreach ($this->groupByFields as $fields) {
$this->queryBuilder->groupBy($fields->getSelect());
}
}
// set in
if (!empty($this->inFields)) {
$this->addIns($this->inFields, $this->inValues);
}
// set between
if (!empty($this->betweenFields)) {
$this->addBetweens($this->betweenFields, $this->betweenValues);
}
if ($this->search != null) {
$searchParts = [];
foreach ($this->searchFields as $searchField) {
$searchParts[] = $searchField->getSelect() . ' LIKE :search';
}
$this->queryBuilder->andWhere('(' . implode(' OR ', $searchParts) . ')');
$this->queryBuilder->setParameter('search', '%' . $this->search . '%');
}
return $this->queryBuilder;
}
/**
* @param QueryBuilder $builder
* @param AbstractFilter $filter
* @param $groupBy
*
* @return array|Paginator
*/
public function returnFilteredEntities(QueryBuilder $builder, AbstractFilter $filter, $groupBy)
{
$pagination = $filter->getPagination();
if ($pagination->getLimit()) {
$builder->setMaxResults($pagination->getLimit());
}
$builder->setFirstResult($pagination->getOffset());
if ($pagination->isEnabled()) {
$query = $builder->getQuery();
$query->setHydrationMode($filter->getHydrationMode());
$paginator = new Paginator($query, true);
$pagination->setTotalResults(count($paginator));
return $paginator;
}
$builder->groupBy($groupBy);
$query = $builder->getQuery();
$entities = $query->getResult($filter->getHydrationMode());
return $entities;
}
/**
* Query results after filtering.
*
* @param integer $rootEntityIdentifier
* @param bool $buildQuery
*
* @return int
*/
private function getCountFilteredResults($rootEntityIdentifier, $buildQuery = true)
{
if (true === $buildQuery) {
$qb = $this->em->createQueryBuilder();
$qb->select('count(distinct ' . $this->tableName . '.' . $rootEntityIdentifier . ')');
$qb->from($this->entity, $this->tableName);
$this->setLeftJoins($qb);
$this->setWhere($qb);
$this->setWhereAllCallback($qb);
return (int) $qb->getQuery()->getSingleScalarResult();
} else {
$this->qb->setFirstResult(null)->setMaxResults(null)->select('count(distinct ' . $this->tableName . '.' . $rootEntityIdentifier . ')');
if (true === $this->isPostgreSQLConnection) {
$this->qb->groupBy($this->tableName . '.' . $rootEntityIdentifier);
return count($this->qb->getQuery()->getResult());
} else {
return (int) $this->qb->getQuery()->getSingleScalarResult();
}
}
}
/**
* Build the data query
*
* @return QueryBuilder
*/
public function getDataQuery()
{
$this->buildFilteredQuery();
if ($this->offset !== null) {
$this->currentQueryBuilder->setFirstResult($this->offset);
}
if ($this->length !== null) {
$this->currentQueryBuilder->setMaxResults($this->length);
}
if ($this->orders !== null) {
foreach ($this->orders as $order) {
list($column, $dir) = $order;
$this->currentQueryBuilder->addOrderBy($column->sourceSort, $dir);
}
}
if (isset($this->conf->group)) {
$this->currentQueryBuilder->groupBy($this->conf->group);
}
return $this->currentQueryBuilder;
}
/**
* @param array $constraints
* @param $queryBuilder
* @param $alias
*/
public function groupBy(array &$constraints, QueryBuilder $queryBuilder, $alias)
{
$this->ensuresHasArrayValue($constraints, 'grouping_columns');
$groupedColumns = $constraints['grouping_columns'];
foreach ($groupedColumns as $column) {
$queryBuilder->groupBy($alias . '.' . $column);
}
unset($constraints['grouping_columns']);
}
/**
* Sets group by fields to querybuilder.
*
* @param QueryBuilder $queryBuilder
*/
protected function assignGroupBy($queryBuilder)
{
if (!empty($this->groupByFields)) {
foreach ($this->groupByFields as $fields) {
$queryBuilder->groupBy($fields->getSelect());
}
}
}
/**
* set query group
*
* @param string $group
*
* @return Datatable
*/
public function setGroupBy($group)
{
$this->queryBuilder->groupBy($group);
return $this;
}
private function applyGroupBy(QueryBuilderFilter $filter, QueryBuilder $queryBuilder)
{
if ($filter->getGroupBy()) {
$queryBuilder->groupBy($filter->getGroupBy());
}
}
/**
* Group By
* @param \Doctrine\ORM\QueryBuilder $queryBuilder
* @param array $request
*/
private function getGroupByStatement(\Doctrine\ORM\QueryBuilder $queryBuilder)
{
if ($this->groupBy !== '') {
$queryBuilder->groupBy($this->groupBy);
}
}
/**
* Prepare certain search condition
*
* @param \Doctrine\ORM\QueryBuilder $queryBuilder Query builder to prepare
* @param string $value Parameter for GROUP BY
*
* @return void
*/
protected function prepareCndPaGroupBy(\Doctrine\ORM\QueryBuilder $queryBuilder, $value)
{
$queryBuilder->groupBy($value);
}
/**
* {@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;
}
