dql-custom-walkers.rst 7.8 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217
  1. Extending DQL in Doctrine 2: Custom AST Walkers
  2. ===============================================
  3. .. sectionauthor:: Benjamin Eberlei <kontakt@beberlei.de>
  4. The Doctrine Query Language (DQL) is a proprietary sql-dialect that
  5. substitutes tables and columns for Entity names and their fields.
  6. Using DQL you write a query against the database using your
  7. entities. With the help of the metadata you can write very concise,
  8. compact and powerful queries that are then translated into SQL by
  9. the Doctrine ORM.
  10. In Doctrine 1 the DQL language was not implemented using a real
  11. parser. This made modifications of the DQL by the user impossible.
  12. Doctrine 2 in contrast has a real parser for the DQL language,
  13. which transforms the DQL statement into an
  14. `Abstract Syntax Tree <http://en.wikipedia.org/wiki/Abstract_syntax_tree>`_
  15. and generates the appropriate SQL statement for it. Since this
  16. process is deterministic Doctrine heavily caches the SQL that is
  17. generated from any given DQL query, which reduces the performance
  18. overhead of the parsing process to zero.
  19. You can modify the Abstract syntax tree by hooking into DQL parsing
  20. process by adding a Custom Tree Walker. A walker is an interface
  21. that walks each node of the Abstract syntax tree, thereby
  22. generating the SQL statement.
  23. There are two types of custom tree walkers that you can hook into
  24. the DQL parser:
  25. - An output walker. This one actually generates the SQL, and there
  26. is only ever one of them. We implemented the default SqlWalker
  27. implementation for it.
  28. - A tree walker. There can be many tree walkers, they cannot
  29. generate the sql, however they can modify the AST before its
  30. rendered to sql.
  31. Now this is all awfully technical, so let me come to some use-cases
  32. fast to keep you motivated. Using walker implementation you can for
  33. example:
  34. - Modify the AST to generate a Count Query to be used with a
  35. paginator for any given DQL query.
  36. - Modify the Output Walker to generate vendor-specific SQL
  37. (instead of ANSI).
  38. - Modify the AST to add additional where clauses for specific
  39. entities (example ACL, country-specific content...)
  40. - Modify the Output walker to pretty print the SQL for debugging
  41. purposes.
  42. In this cookbook-entry I will show examples on the first two
  43. points. There are probably much more use-cases.
  44. Generic count query for pagination
  45. ----------------------------------
  46. Say you have a blog and posts all with one category and one author.
  47. A query for the front-page or any archive page might look something
  48. like:
  49. .. code-block:: sql
  50. SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...
  51. Now in this query the blog post is the root entity, meaning its the
  52. one that is hydrated directly from the query and returned as an
  53. array of blog posts. In contrast the comment and author are loaded
  54. for deeper use in the object tree.
  55. A pagination for this query would want to approximate the number of
  56. posts that match the WHERE clause of this query to be able to
  57. predict the number of pages to show to the user. A draft of the DQL
  58. query for pagination would look like:
  59. .. code-block:: sql
  60. SELECT count(DISTINCT p.id) FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...
  61. Now you could go and write each of these queries by hand, or you
  62. can use a tree walker to modify the AST for you. Lets see how the
  63. API would look for this use-case:
  64. .. code-block:: php
  65. <?php
  66. $pageNum = 1;
  67. $query = $em->createQuery($dql);
  68. $query->setFirstResult( ($pageNum-1) * 20)->setMaxResults(20);
  69. $totalResults = Paginate::count($query);
  70. $results = $query->getResult();
  71. The ``Paginate::count(Query $query)`` looks like:
  72. .. code-block:: php
  73. <?php
  74. class Paginate
  75. {
  76. static public function count(Query $query)
  77. {
  78. /* @var $countQuery Query */
  79. $countQuery = clone $query;
  80. $countQuery->setHint(Query::HINT_CUSTOM_TREE_WALKERS, array('DoctrineExtensions\Paginate\CountSqlWalker'));
  81. $countQuery->setFirstResult(null)->setMaxResults(null);
  82. return $countQuery->getSingleScalarResult();
  83. }
  84. }
  85. It clones the query, resets the limit clause first and max results
  86. and registers the ``CountSqlWalker`` customer tree walker which
  87. will modify the AST to execute a count query. The walkers
  88. implementation is:
  89. .. code-block:: php
  90. <?php
  91. class CountSqlWalker extends TreeWalkerAdapter
  92. {
  93. /**
  94. * Walks down a SelectStatement AST node, thereby generating the appropriate SQL.
  95. *
  96. * @return string The SQL.
  97. */
  98. public function walkSelectStatement(SelectStatement $AST)
  99. {
  100. $parent = null;
  101. $parentName = null;
  102. foreach ($this->_getQueryComponents() AS $dqlAlias => $qComp) {
  103. if ($qComp['parent'] === null && $qComp['nestingLevel'] == 0) {
  104. $parent = $qComp;
  105. $parentName = $dqlAlias;
  106. break;
  107. }
  108. }
  109. $pathExpression = new PathExpression(
  110. PathExpression::TYPE_STATE_FIELD | PathExpression::TYPE_SINGLE_VALUED_ASSOCIATION, $parentName,
  111. $parent['metadata']->getSingleIdentifierFieldName()
  112. );
  113. $pathExpression->type = PathExpression::TYPE_STATE_FIELD;
  114. $AST->selectClause->selectExpressions = array(
  115. new SelectExpression(
  116. new AggregateExpression('count', $pathExpression, true), null
  117. )
  118. );
  119. }
  120. }
  121. This will delete any given select expressions and replace them with
  122. a distinct count query for the root entities primary key. This will
  123. only work if your entity has only one identifier field (composite
  124. keys won't work).
  125. Modify the Output Walker to generate Vendor specific SQL
  126. --------------------------------------------------------
  127. Most RMDBS have vendor-specific features for optimizing select
  128. query execution plans. You can write your own output walker to
  129. introduce certain keywords using the Query Hint API. A query hint
  130. can be set via ``Query::setHint($name, $value)`` as shown in the
  131. previous example with the ``HINT_CUSTOM_TREE_WALKERS`` query hint.
  132. We will implement a custom Output Walker that allows to specify the
  133. SQL\_NO\_CACHE query hint.
  134. .. code-block:: php
  135. <?php
  136. $dql = "SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ...";
  137. $query = $m->createQuery($dql);
  138. $query->setHint(Query::HINT_CUSTOM_OUTPUT_WALKER, 'DoctrineExtensions\Query\MysqlWalker');
  139. $query->setHint("mysqlWalker.sqlNoCache", true);
  140. $results = $query->getResult();
  141. Our ``MysqlWalker`` will extend the default ``SqlWalker``. We will
  142. modify the generation of the SELECT clause, adding the
  143. SQL\_NO\_CACHE on those queries that need it:
  144. .. code-block:: php
  145. <?php
  146. class MysqlWalker extends SqlWalker
  147. {
  148. /**
  149. * Walks down a SelectClause AST node, thereby generating the appropriate SQL.
  150. *
  151. * @param $selectClause
  152. * @return string The SQL.
  153. */
  154. public function walkSelectClause($selectClause)
  155. {
  156. $sql = parent::walkSelectClause($selectClause);
  157. if ($this->getQuery()->getHint('mysqlWalker.sqlNoCache') === true) {
  158. if ($selectClause->isDistinct) {
  159. $sql = str_replace('SELECT DISTINCT', 'SELECT DISTINCT SQL_NO_CACHE', $sql);
  160. } else {
  161. $sql = str_replace('SELECT', 'SELECT SQL_NO_CACHE', $sql);
  162. }
  163. }
  164. return $sql;
  165. }
  166. }
  167. Writing extensions to the Output Walker requires a very deep
  168. understanding of the DQL Parser and Walkers, but may offer your
  169. huge benefits with using vendor specific features. This would still
  170. allow you write DQL queries instead of NativeQueries to make use of
  171. vendor specific features.