Custom DQL functions in doctrine2

Question

How do you use custom DQL functions in partials to hydrate query result.

Looking for a way to use DQL functions in createQuery or any other doctrine way (nativeSql, QueryBuilder) its not necessary to use partials but it should hydrate my array based on the relationship of tables and it should select only selective fields

Following query works fine:

$q = $em->createQuery("select "
                . "partial t.{id, description}, "
                . "partial ut.{id, firstName, lastName, email}, "
                . "DATE_FORMAT(ut.created, '%m-%d-Y') "
                . "from PMIUserBundle:Task t LEFT JOIN t.users ut");

DATE_FORMAT(ut.created, '%m-%d-Y') works fine when its out of partial.

DATE_FORMAT is already registered as custom function in config.yml

config.yml:

dql:
            datetime_functions:
                DATE_FORMAT: PMI\UserBundle\DoctrineFunctions\DateFormat

Following query creates issue:

 $q = $em->createQuery("select "
                . "partial t.{id, description}, "
                . "partial ut.{id, firstName, lastName, email, DATE_FORMAT(created, '%m-%d-Y')}, "
                . "DATE_FORMAT(ut.created, '%m-%d-Y') "
                . "from PMIUserBundle:Task t LEFT JOIN t.users ut");

gives error:

[Syntax Error] line 0, col 91: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_CURLY_BRACE, got '(' 

Following is my DateFormat class:

class DateFormat extends \Doctrine\ORM\Query\AST\Functions\FunctionNode {



    protected $dateExpression;

    protected $formatChar;

    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker) {

         return 'DATE_FORMAT('.
                $sqlWalker->walkArithmeticExpression($this->dateExpression) .
                ','.
                $sqlWalker->walkStringPrimary($this->formatChar).
                ')';



    }

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

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

        $this->dateExpression = $parser->ArithmeticExpression();
        $parser->match(Lexer::T_COMMA);


        $this->formatChar = $parser->StringPrimary();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);


    }

}

EDIT:

Ok, so Partial will not allow me to use DQL functions. Thanks to FuzzyTree for the solution to use ut->getFormattedCreatedDate() inside my entity class. But i am still curious what if we are grouping child table and calculating some amount using mysql Aggregate functions.

How to achieve following criteria in doctrine2 using createQuery, queryBuilder, nativeSQl or any other way:

1. Select only selective fields
2. Group by child table entity using Aggregate Functions
3. Hydrate array based on table relations, Aggregate functions results should be in child array.

every time if we use DQL functions, do we have to create classes for that like i created for DateFormat ? what if we are using nested MySql functions like

GROUP_CONCAT(DISTINCT CONCAT(tags.id,',',tags.displayName)

Thank you for reading my question.

Answer 1

How do you use custom DQL functions in partials to hydrate query result.

The partial keyword tells doctrine to return entities that are only partially hydrated and allows you to specify which fields you want to be hydrated.

When retrieving partial entities you are limited to selecting the fields of the entity (because otherwise where would doctrine 'store' the field you're selecting?), so it's not possible to specify a DQL function as a field of a partial object.