CODEWITHSUNDEEP
symfonydoctrine
user2818060
user2818060

Reputation: 845

How to use Group Concat in DQL

Problem im Facing:

code config.yml

 orm:
    auto_generate_proxy_classes: %kernel.debug%
    auto_mapping: true
    entity_managers:
    default:
     dql:
        string_functions:
                GROUP_CONCAT: bundlename\Query\Mysql\GroupConcat
        string_functions:
            test_string: Acme\HelloBundle\DQL\StringFunction
            second_string: Acme\HelloBundle\DQL\SecondStringFunction
        numeric_functions:
            test_numeric: Acme\HelloBundle\DQL\NumericFunction
        datetime_functions:
            test_datetime: Acme\HelloBundle\DQL\DatetimeFunction

here is my code of controller

   $from = 'Entities\Product product';
    $qb->select(" GROUP_CONCAT(product.name) ")
      ->add('from', $from)
      ->innerjoin('Entities\ProductMapping','product_mapping','with','product_mapping.product=product.id' )
      ->where("product_mapping.company in ($company_id) ")
      ->setMaxResults(6)
      ->setFirstResult($i);
     $query = $qb->getQuery();
     $product = $query->getResult();
     print_r($product);

Upvotes: 0

Views: 2818

Answers (1)

Thomas K
Thomas K

Reputation: 6206

You have defined the string_functions node under orm: default: dql: twice. The second one will override the first one.

Please change to:

 orm:
    auto_generate_proxy_classes: %kernel.debug%
    auto_mapping: true
    entity_managers:
    default:
     dql:
        string_functions:
            test_string: Acme\HelloBundle\DQL\StringFunction
            second_string: Acme\HelloBundle\DQL\SecondStringFunction
            GROUP_CONCAT: bundlename\Query\Mysql\GroupConcat
        numeric_functions:
            test_numeric: Acme\HelloBundle\DQL\NumericFunction
        datetime_functions:
            test_datetime: Acme\HelloBundle\DQL\DatetimeFunction

Also make sure to add the actual group concat class. Here is mine:

<?php

namespace Tap\Bundle\CoreBundle\Doctrine\Extension\Query;

use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\AST\Functions\FunctionNode;

class GroupConcat extends FunctionNode
{
    public $isDistinct = false;
    public $expression = null;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return 'GROUP_CONCAT(' .
            ($this->isDistinct ? 'DISTINCT ' : '') .
            $this->expression->dispatch($sqlWalker) .
        ')';
    }

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {

        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $lexer = $parser->getLexer();
        if ($lexer->isNextToken(Lexer::T_DISTINCT)) {
            $parser->match(Lexer::T_DISTINCT);

            $this->isDistinct = true;
        }

        $this->expression = $parser->SingleValuedPathExpression();

        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

Make sure to edit the namespaces.

Upvotes: 1

Related Questions