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