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.

Grafton

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.

Coke

@Grafton can you add a method getFormattedCreatedDate to your users entity that handles formatting on the php side? then you can use partial ut.{id, firstName, lastName, email, created} and call ut->getFormattedCreatedDate() – Coke

Answer 2

What about this?

    $qb = $this->_em->createQueryBuilder()
            ->select( 't.id, t.description, '
                    . 'ut.id, ut.firstName, '
                    . 'ut.lastName, ut.email, '
                    . 'DATE_FORMAT( ut.created, \'%m-%d-Y\' )' )
            ->from( 'PMIUserBundle:Task', 't' )
            ->leftjoin( 't.users', 'ut' );

    $result = $qb->getQuery()->getResult();

Assuming that you are using this in a Repository class (that's why the call to $this->_em ).

I have noticed 2 other things regarding the custom function:

1. I have personally it defined as a "string_functions:" instead in the config.yml file
2. I know at least one Bundle that implements this custom function: https://github.com/beberlei/DoctrineExtensions

Hope This helps,

Tam

Arnaldo

Answer 3

Not supported

PARTIAL ut.{id, firstName, lastName, email, DATE_FORMAT(created, '%m-%d-Y')}

Using DQL functions inside the selection of partial entities like this is not supported.

It doesn't make much sense either. In this case you've (probably) mapped the field created as a datetime field, so you'll expect a full DateTime value in there. But now you're suddenly just using the date part. And it get worse: you've formatted the value in such a way a DateTime object cannot be created (it expects Y-m-d H:i:s, and the date type expects Y-m-d).

For any partial selection: You cannot, and should not, change the value of a field.

Alternative

In stead of hydrating into partial entities, you could just use arrays. Use $q->getArrayResult() or $q->getScalarResult(), whichever suits your purpose better.

If you like, you can group/nest the results in the Repository method. Example:

public function getSomeData()
{
    $result = array();

    $dql = <<<EOQ
SELECT
t.id AS t_id,
t.description AS t_description,
u.id AS u_id,
u.firstName AS u_firstName,
u.lastName AS u_lastName,
u.email AS u_email,
DATE_FORMAT(u.created, '%m-%d-Y') AS u_created
FROM PMIUserBundle:Task t
LEFT JOIN t.users u
EOQ;

    $q = $this->getEntityManager()->createQuery($dql);

    foreach ($q->getScalarResult() as $row) {
        if (!isset($result[$row['t_id']])) {
            $result[$row['t_id']] = array(
                'id'          => $row['t_id'],
                'description' => $row['t_description'],
                'users'       => array()
            );

            $result[$row['t_id']]['users'][] = array(
                'u.id'        => $row['u_id'],
                'u.firstName' => $row['u_firstName'],
                'u.lastName'  => $row['u_lastName'],
                'u.email'     => $row['u_email'],
                'u.created'   => $row['u_created']
            );
        }
    }

    return $result;
}

Nesting DQL functions

Nesting DQL functions should be no problem (if you wrote them correctly). And DISTINCT is supported out of the box.

Nightwalker

Thank you for the reply. will $q->getArrayResult() or $q->getScalarResult() hydrate array based on table relations. – Grafton

This isn't an answer; but an idea. Could you copy the relevant data to a temporary table, then do your partial select against that? – Ikeda

I've added an example for grouping/nesting the result when using getScalarResult(). – Nightwalker