title: Custom DQL functions in doctrine2
body: <p>How do you use custom DQL functions in partials to hydrate query result.</p>
  
  <p>Looking for a way to use DQL functions in createQuery or <strong>any other doctrine way</strong> (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</p>
  
  <p>Following query works fine:</p>
  
  <pre><code>$q = $em-&gt;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");
  </code></pre>
  
  <p>DATE_FORMAT(ut.created, '%m-%d-Y') works fine when its out of partial.</p>
  
  <p><code>DATE_FORMAT</code> is already registered as custom function in config.yml</p>
  
  <p>config.yml:</p>
  
  <pre><code>dql:
              datetime_functions:
                  DATE_FORMAT: PMI\UserBundle\DoctrineFunctions\DateFormat
  </code></pre>
  
  <p>Following query creates issue:</p>
  
  <pre><code> $q = $em-&gt;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");
  </code></pre>
  
  <p>gives error:</p>
  
  <pre><code>[Syntax Error] line 0, col 91: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_CURLY_BRACE, got '(' 
  </code></pre>
  
  <p>Following is my DateFormat class:</p>
  
  <pre><code>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-&gt;walkArithmeticExpression($this-&gt;dateExpression) .
                  ','.
                  $sqlWalker-&gt;walkStringPrimary($this-&gt;formatChar).
                  ')';
  
  
  
      }
  
      public function parse(\Doctrine\ORM\Query\Parser $parser) {
  
          $parser-&gt;match(Lexer::T_IDENTIFIER);
          $parser-&gt;match(Lexer::T_OPEN_PARENTHESIS);
  
          $this-&gt;dateExpression = $parser-&gt;ArithmeticExpression();
          $parser-&gt;match(Lexer::T_COMMA);
  
  
          $this-&gt;formatChar = $parser-&gt;StringPrimary();
          $parser-&gt;match(Lexer::T_CLOSE_PARENTHESIS);
  
  
      }
  
  }
  </code></pre>
  
  <p>EDIT:</p>
  
  <p>Ok, so Partial will not allow me to use DQL functions. Thanks to <a href="https://stackoverflow.com/a/24860308/805437">FuzzyTree</a> for the solution to use <code>ut-&gt;getFormattedCreatedDate()</code> inside my entity class. But i am still curious what if we are grouping child table and calculating some amount using mysql Aggregate functions. </p>
  
  <p>How to achieve following criteria in doctrine2 using createQuery, queryBuilder, nativeSQl or any other way:</p>
  
  <ol>
  <li>Select only selective fields</li>
  <li>Group by child table entity using Aggregate Functions</li>
  <li>Hydrate array based on table relations, Aggregate functions results
  should be in child array.</li>
  </ol>
  
  <p>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 </p>
  
  <pre><code>GROUP_CONCAT(DISTINCT CONCAT(tags.id,',',tags.displayName)
  </code></pre>
  
  <p>Thank you for reading my question.</p>