Extending DQL in Doctrine ORM: Custom AST Walkers

Generic count query for pagination

Say you have a blog and posts all with one category and one author. A query for the front-page or any archive page might look something like:

1
SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...

Now in this query the blog post is the root entity, meaning it's the one that is hydrated directly from the query and returned as an array of blog posts. In contrast the comment and author are loaded for deeper use in the object tree.

A pagination for this query would want to approximate the number of posts that match the WHERE clause of this query to be able to predict the number of pages to show to the user. A draft of the DQL query for pagination would look like:

1
SELECT count(DISTINCT p.id) FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...

Now you could go and write each of these queries by hand, or you can use a tree walker to modify the AST for you. Let's see how the API would look for this use-case:

1
<?php
$pageNum = 1;
$query = $em->createQuery($dql);
$query->setFirstResult( ($pageNum-1) * 20)->setMaxResults(20);

$totalResults = Paginate::count($query);
$results = $query->getResult();
2
3
4
5
6
7

The Paginate::count(Query $query) looks like:

1
<?php
class Paginate
{
    static public function count(Query $query)
    {
        /** @var Query $countQuery */
        $countQuery = clone $query;

        $countQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, array('DoctrineExtensions\Paginate\CountSqlWalker'));
        $countQuery->setFirstResult(null)->setMaxResults(null);

        return $countQuery->getSingleScalarResult();
    }
}
2
3
4
5
6
7
8
9
10
11
12
13
14

It clones the query, resets the limit clause first and max results and registers the CountSqlWalker custom tree walker which will modify the AST to execute a count query. The walkers implementation is:

1
<?php
class CountSqlWalker extends TreeWalkerAdapter
{
    /**
     * Walks down a SelectStatement AST node, thereby generating the appropriate SQL.
     *
     * @return string The SQL.
     */
    public function walkSelectStatement(SelectStatement $AST)
    {
        $parent = null;
        $parentName = null;
        foreach ($this->_getQueryComponents() as $dqlAlias => $qComp) {
            if ($qComp['parent'] === null && $qComp['nestingLevel'] == 0) {
                $parent = $qComp;
                $parentName = $dqlAlias;
                break;
            }
        }

        $pathExpression = new PathExpression(
            PathExpression::TYPE_STATE_FIELD | PathExpression::TYPE_SINGLE_VALUED_ASSOCIATION, $parentName,
            $parent['metadata']->getSingleIdentifierFieldName()
        );
        $pathExpression->type = PathExpression::TYPE_STATE_FIELD;

        $AST->selectClause->selectExpressions = array(
            new SelectExpression(
                new AggregateExpression('count', $pathExpression, true), null
            )
        );
    }
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

This will delete any given select expressions and replace them with a distinct count query for the root entities primary key. This will only work if your entity has only one identifier field (composite keys won't work).

Modify the Output Walker to generate Vendor specific SQL

Most RMDBS have vendor-specific features for optimizing select query execution plans. You can write your own output walker to introduce certain keywords using the Query Hint API. A query hint can be set via Query::setHint($name, $value) as shown in the previous example with the HINT_CUSTOM_TREE_WALKERS query hint.

We will implement a custom Output Walker that allows to specify the SQL_NO_CACHE query hint.

1
<?php
$dql = "SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...";
$query = $m->createQuery($dql);
$query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'DoctrineExtensions\Query\MysqlWalker');
$query->setHint("mysqlWalker.sqlNoCache", true);
$results = $query->getResult();
2
3
4
5
6

Our MysqlWalker will extend the default SqlWalker. We will modify the generation of the SELECT clause, adding the SQL_NO_CACHE on those queries that need it:

1
<?php
class MysqlWalker extends SqlWalker
{
     /**
     * Walks down a SelectClause AST node, thereby generating the appropriate SQL.
     *
     * @param $selectClause
     * @return string The SQL.
     */
    public function walkSelectClause($selectClause)
    {
        $sql = parent::walkSelectClause($selectClause);

        if ($this->getQuery()->getHint('mysqlWalker.sqlNoCache') === true) {
            if ($selectClause->isDistinct) {
                $sql = str_replace('SELECT DISTINCT', 'SELECT DISTINCT SQL_NO_CACHE', $sql);
            } else {
                $sql = str_replace('SELECT', 'SELECT SQL_NO_CACHE', $sql);
            }
        }

        return $sql;
    }
}
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

Writing extensions to the Output Walker requires a very deep understanding of the DQL Parser and Walkers, but may offer your huge benefits with using vendor specific features. This would still allow you write DQL queries instead of NativeQueries to make use of vendor specific features.