using custom DQL function in queries in Symfony2

Question

I have added the following query extension:

<?php

/*
 * DoctrineExtensions Mysql Function Pack
 *
 * LICENSE
 *
 * This source file is subject to the new BSD license that is bundled
 * with this package in the file LICENSE.txt.
 * If you did not receive a copy of the license and are unable to
 * obtain it through the world-wide-web, please send an email
 * to [email protected] so I can send you a copy immediately.
 */

namespace MyApp\MainBundle\DQL;

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

/**
 * "DAY" "(" SimpleArithmeticExpression ")". Modified from DoctrineExtensions\Query\Mysql\Year
 *
 * @category    DoctrineExtensions
 * @package     DoctrineExtensions\Query\Mysql
 * @author      Rafael Kassner <[email protected]>
 * @author      Sarjono Mukti Aji <[email protected]>
 * @license     MIT License
 */
class Day extends FunctionNode
{
    public $date;

    /**
     * @override
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return "DAY(" . $sqlWalker->walkArithmeticPrimary($this->date) . ")";
    }

    /**
     * @override
     */
    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);

        $this->date = $parser->ArithmeticPrimary();

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

and I wanted to use this in a group by so in a query I did:

->addGroupBy('DAY(v.created)')

However it always gives me an error of:

[Semantical Error] line 0, col 90 near 'DAY(v.create': Error: Cannot group by undefined identification or result variable.

why is this?

Udan · Accepted Answer · 2013-10-29 08:54:59Z

1

Since this commit: https://github.com/doctrine/doctrine2/commit/2642daa43851878688d01625f272ff5874cac7b2

This issue is solved through the SelectExpression + Hidden + ResultVariable refer.

SELECT 
    ..., DAY(t.scheduledDate) AS myDay
FROM Task t
    ...
WHERE
    ...
GROUP BY
    myDay

answered Oct 29, 2013 at 8:54

Udan

5,6192 gold badges31 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pécé · Accepted Answer · 2013-10-29 20:00:48Z

1

You have to add the calculated value in the SELECT part if you want to GROUP BY (or ORDER BY) on it. Use keyword HIDDEN to ignore this field in the fetched results !

$qb
    // select...
    ->addSelect('DAY(v.created) AS HIDDEN myGroupByField')
    // ...
    ->groupBy('myGroupByField')
;

edited Oct 29, 2013 at 20:00

answered Oct 29, 2013 at 8:56

pécé

12.7k5 gold badges51 silver badges55 bronze badges

Collectives™ on Stack Overflow

using custom DQL function in queries in Symfony2

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related