GROUP_CONCAT zfcDatagrid

Doctrine2.php

<?php
namespace ZfcDatagrid\DataSource;

use ZfcDatagrid\DataSource\Doctrine2\Paginator as PaginatorAdapter;
use ZfcDatagrid\Column;
use Doctrine\ORM;
use Doctrine\ORM\Query\Expr;
use Doctrine\ORM\QueryBuilder;

class Doctrine2 extends AbstractDataSource
{

    /**
     *
     * @var ORM\QueryBuilder
     */
    private $qb;

    /**
     * Data source
     *
     * @param mixed $data            
     */
    public function __construct($data)
    {
        if ($data instanceof ORM\QueryBuilder) {
            $this->qb = $data;
        } else {
            $return = $data;
            if (is_object($data)) {
                $return = get_class($return);
            }
            throw new \InvalidArgumentException("Unknown data input..." . $return);
        }
    }

    /**
     *
     * @return ORM\QueryBuilder
     */
    public function getData()
    {
        return $this->qb;
    }

    public function execute()
    {
        $qb = $this->getData();
        
        /*
         * Step 1) Apply needed columns
         */
        $selectColumns = array();
        foreach ($this->getColumns() as $column) {
            if ($column instanceof Column\Select) {
                $colString = $column->getSelectPart1();
                if ($colString instanceof QueryBuilder) {
                    $colString = $this->handleQueryBuilder($colString);
                }
                if ($column->getSelectPart2() != '') {
                    $colString .= '.' . $column->getSelectPart2();
                }
                $colString .= ' ' . $column->getUniqueId();
                
                $selectColumns[] = $colString;
            }
        }
        $qb->resetDQLPart('select');
        $qb->select($selectColumns);
        
        /*
         * Step 2) Apply sorting
         */
        if (count($this->getSortConditions()) > 0) {
            // Minimum one sort condition given -> so reset the default orderBy
            $qb->resetDQLPart('orderBy');
            
            foreach ($this->getSortConditions() as $key => $sortCondition) {
                /* @var $column \ZfcDatagrid\Column\AbstractColumn */
                $column = $sortCondition['column'];
                
                $colString = $column->getSelectPart1();
                if ($column->getSelectPart2() != '') {
                    $colString .= '.' . $column->getSelectPart2();
                }
                
                if ($column->getType() === 'number') {
                    $qb->addSelect('ABS(' . $colString . ') sortColumn' . $key);
                    $qb->add('orderBy', new Expr\OrderBy('sortColumn' . $key, $sortCondition['sortDirection']), true);
                } else {
                    $qb->add('orderBy', new Expr\OrderBy($column->getUniqueId(), $sortCondition['sortDirection']), true);
                }
            }
        }
        
        /*
         * Step 3) Apply filters
         */
        $filterColumn = new Doctrine2\Filter($qb);
        foreach ($this->getFilters() as $filter) {
            /* @var $filter \ZfcDatagrid\Filter */
            if ($filter->isColumnFilter() === true) {
                $filterColumn->applyFilter($filter);
            }
        }
        
        /*
         * Step 4) Pagination
         */
        $this->setPaginatorAdapter(new PaginatorAdapter($qb));
    }

    /**
     *
     * @param QueryBuilder $qb            
     * @return \Doctrine\ORM\Query\Expr\Select
     */
    private function handleQueryBuilder(QueryBuilder $qb)
    {
        return new Expr\Select('(' . $qb->getDQL() . ')');
    }
}

Filter.php

<?php
namespace ZfcDatagrid\DataSource\Doctrine2;

use ZfcDatagrid\Filter as DatagridFilter;
use ZfcDatagrid\Column;
use Doctrine\ORM\QueryBuilder;
use Doctrine\ORM\Query\Expr;

class Filter
{

    /**
     *
     * @var QueryBuilder
     */
    private $qb;

    public function __construct(QueryBuilder $qb)
    {
        $this->qb = $qb;
    }

    /**
     *
     * @return \Doctrine\ORM\QueryBuilder
     */
    public function getQueryBuilder()
    {
        return $this->qb;
    }

    /**
     *
     * @param DatagridFilter $filter            
     * @throws \InvalidArgumentException
     */
    public function applyFilter(DatagridFilter $filter)
    {
        $qb = $this->getQueryBuilder();
        $expr = new Expr();
        
        $column = $filter->getColumn();
        $colString = $column->getSelectPart1();
        if ($colString instanceof QueryBuilder) {
            $colString = $this->handleQueryBuilder($colString);
        }
        if ($column->getSelectPart2() != '') {
            $colString .= '.' . $column->getSelectPart2();
        }
        if ($column instanceof Column\Select && $column->hasFilterSelectExpression()) {
            $colString = sprintf($column->getFilterSelectExpression(), $colString);
        }
        $values = $filter->getValues();
        
        $wheres = array();
        foreach ($values as $key => $value) {
            $valueParameterName = ':' . str_replace('.', '', $column->getUniqueId() . $key);
            
            switch ($filter->getOperator()) {
                
                case DatagridFilter::LIKE:
                    $wheres[] = $expr->like($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, '%' . $value . '%');
                    break;
                
                case DatagridFilter::LIKE_LEFT:
                    $wheres[] = $expr->like($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, '%' . $value);
                    break;
                
                case DatagridFilter::LIKE_RIGHT:
                    $wheres[] = $expr->like($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value . '%');
                    break;
                
                case DatagridFilter::NOT_LIKE:
                    $wheres[] = $expr->notLike($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, '%' . $value . '%');
                    break;
                
                case DatagridFilter::NOT_LIKE_LEFT:
                    $wheres[] = $expr->notLike($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, '%' . $value);
                    break;
                
                case DatagridFilter::NOT_LIKE_RIGHT:
                    $wheres[] = $expr->notLike($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value . '%');
                    break;
                
                case DatagridFilter::EQUAL:
                    $wheres[] = $expr->eq($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::NOT_EQUAL:
                    $wheres[] = $expr->neq($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::GREATER_EQUAL:
                    $wheres[] = $expr->gte($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::GREATER:
                    $wheres[] = $expr->gt($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::LESS_EQUAL:
                    $wheres[] = $expr->lte($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::LESS:
                    $wheres[] = $expr->lt($colString, $valueParameterName);
                    $qb->setParameter($valueParameterName, $value);
                    break;
                
                case DatagridFilter::BETWEEN:
                    $minParameterName = ':' . str_replace('.', '', $colString . '0');
                    $maxParameterName = ':' . str_replace('.', '', $colString . '1');
                    
                    $wheres[] = $expr->between($colString, $minParameterName, $maxParameterName);
                    
                    $qb->setParameter($minParameterName, $values[0]);
                    $qb->setParameter($maxParameterName, $values[1]);
                    break 2;
                
                default:
                    throw new \InvalidArgumentException('This operator is currently not supported: ' . $filter->getOperator());
                    break;
            }
        }
        
        if (count($wheres) > 0) {
            $orWhere = $qb->expr()->orX();
            $orWhere->addMultiple($wheres);
            
            $qb->andWhere($orWhere);
        }
    }

    /**
     *
     * @param QueryBuilder $qb            
     * @return \Doctrine\ORM\Query\Expr\Select
     */
    private function handleQueryBuilder(QueryBuilder $qb)
    {
        $aliases = [];
        
        $froms = $qb->getDQLPart('from');
        $joins = $qb->getDQLPart('join');
        
        foreach ($froms as $from) {
            /* @var $from \Doctrine\ORM\Query\Expr\From */
            $aliases[] = $from->getAlias();
        }
        
        foreach ($joins as $join) {
            foreach ($join as $joinType) {
                /* @var $joinType \Doctrine\ORM\Query\Expr\Join */
                $aliases[] = $joinType->getAlias();
            }
        }
        
        $dql = $qb->getDQL();
        
        foreach ($aliases as $alias) {
            // thx for this wonderful tool: http://www.phpliveregex.com/
            // preg_replace("/[^.](descendant)/", " blubb", $input_lines);
            $dql = preg_replace("/[^.](" . $alias . ")/", ' ' . $alias.'Filter', $dql);
        }
        
        return new Expr\Select('(' . $dql . ')');
    }
}

your.php

<?php
$qb = new QueryBuilder($entityManager);

$col = new Column\Select($qb, 'alias');
$col->setWidth(30);
$col->setFilterDefaultOperation(Filter::EQUAL);
$grid->addColumn($col);