/usr/share/doc/php-doctrine-orm/html/cookbook/dql-custom-walkers.html is in doctrine-orm-doc 2.4.6-1+deb8u1.
This file is owned by root:root, with mode 0o644.
The actual contents of the file can be viewed below.
1 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 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 | <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Extending DQL in Doctrine 2: Custom AST Walkers — Doctrine 2 ORM 2 documentation</title>
<link rel="stylesheet" href="../_static/bootstrap/css/bootstrap.min.css" type="text/css" />
<link rel="stylesheet" href="../_static/default.css" type="text/css" />
<link rel="stylesheet" href="../_static/pygments.css" type="text/css" />
<link rel="stylesheet" href="../_static/layout.css" type="text/css" />
<link rel="stylesheet" href="../_static/configurationblock.css" type="text/css" />
<script type="text/javascript">
var DOCUMENTATION_OPTIONS = {
URL_ROOT: '../',
VERSION: '2',
COLLAPSE_MODINDEX: false,
FILE_SUFFIX: '.html',
HAS_SOURCE: true
};
</script>
<script type="text/javascript" src="../_static/jquery.js"></script>
<script type="text/javascript" src="../_static/configurationblock.js"></script>
<script type="text/javascript" src="../_static/underscore.js"></script>
<script type="text/javascript" src="../_static/configurationblock.js"></script>
<script type="text/javascript" src="../_static/doctools.js"></script>
<script type="text/javascript" src="../_static/configurationblock.js"></script>
<script src="../_static/bootstrap/js/bootstrap.min.js"></script>
<script type="text/javascript">
<!--
$(document).ready(function() {
$("#versions").change(function() {
var docsUrl = $(this).val();
window.location.href = docsUrl;
});
});
-->
</script>
<link rel="shortcut icon" href="../_static/doctrine.ico"/>
<link rel="top" title="Doctrine 2 ORM 2 documentation" href="../index.html" />
<link rel="next" title="DQL User Defined Functions" href="dql-user-defined-functions.html" />
<link rel="prev" title="Persisting the Decorator Pattern" href="decorator-pattern.html" />
</head>
<body>
<div id="wrapper">
<div id="header">
<h1 id="h1title"></h1>
<div id="logo">
<a href="http://www.doctrine-project.org/">Doctrine - PHP Database Libraries</a>
</div>
</div>
<div id="nav" class="cls">
<div class="tl cls">
<ul>
<li><a target="_top" href="/">home</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/about">about</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/projects.html">projects</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/projects/orm">orm</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/projects/dbal">dbal</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/blog">blog</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/jira">development</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/contribute">contribute</a></li>
<li><a class="" target="_top" href="http://www.doctrine-project.org/community">community</a></li>
</ul>
</div>
</div>
<div id="content" class="cls">
<div class="related">
<h3>Navigation</h3>
<ul>
<li class="right" style="margin-right: 10px">
<a href="../genindex.html" title="General Index"
accesskey="I">index</a></li>
<li class="right" >
<a href="dql-user-defined-functions.html" title="DQL User Defined Functions"
accesskey="N">next</a> |</li>
<li class="right" >
<a href="decorator-pattern.html" title="Persisting the Decorator Pattern"
accesskey="P">previous</a> |</li>
<li><a href="/">Doctrine Homepage</a> »</li>
<li><a href="../index.html">Doctrine 2 ORM 2 documentation</a> »</li>
</ul>
</div>
<div class="document">
<div class="documentwrapper">
<div class="bodywrapper">
<div class="body" >
<div class="section" id="extending-dql-in-doctrine-2-custom-ast-walkers">
<h1>Extending DQL in Doctrine 2: Custom AST Walkers<a class="headerlink" href="#extending-dql-in-doctrine-2-custom-ast-walkers" title="Permalink to this headline">¶</a></h1>
<p><em>Section author: Benjamin Eberlei <<a class="reference external" href="mailto:kontakt%40beberlei.de">kontakt<span>@</span>beberlei<span>.</span>de</a>></em></p>
<p>The Doctrine Query Language (DQL) is a proprietary sql-dialect that
substitutes tables and columns for Entity names and their fields.
Using DQL you write a query against the database using your
entities. With the help of the metadata you can write very concise,
compact and powerful queries that are then translated into SQL by
the Doctrine ORM.</p>
<p>In Doctrine 1 the DQL language was not implemented using a real
parser. This made modifications of the DQL by the user impossible.
Doctrine 2 in contrast has a real parser for the DQL language,
which transforms the DQL statement into an
<a class="reference external" href="http://en.wikipedia.org/wiki/Abstract_syntax_tree">Abstract Syntax Tree</a>
and generates the appropriate SQL statement for it. Since this
process is deterministic Doctrine heavily caches the SQL that is
generated from any given DQL query, which reduces the performance
overhead of the parsing process to zero.</p>
<p>You can modify the Abstract syntax tree by hooking into DQL parsing
process by adding a Custom Tree Walker. A walker is an interface
that walks each node of the Abstract syntax tree, thereby
generating the SQL statement.</p>
<p>There are two types of custom tree walkers that you can hook into
the DQL parser:</p>
<ul class="simple">
<li>An output walker. This one actually generates the SQL, and there
is only ever one of them. We implemented the default SqlWalker
implementation for it.</li>
<li>A tree walker. There can be many tree walkers, they cannot
generate the sql, however they can modify the AST before its
rendered to sql.</li>
</ul>
<p>Now this is all awfully technical, so let me come to some use-cases
fast to keep you motivated. Using walker implementation you can for
example:</p>
<ul class="simple">
<li>Modify the AST to generate a Count Query to be used with a
paginator for any given DQL query.</li>
<li>Modify the Output Walker to generate vendor-specific SQL
(instead of ANSI).</li>
<li>Modify the AST to add additional where clauses for specific
entities (example ACL, country-specific content...)</li>
<li>Modify the Output walker to pretty print the SQL for debugging
purposes.</li>
</ul>
<p>In this cookbook-entry I will show examples on the first two
points. There are probably much more use-cases.</p>
<div class="section" id="generic-count-query-for-pagination">
<h2>Generic count query for pagination<a class="headerlink" href="#generic-count-query-for-pagination" title="Permalink to this headline">¶</a></h2>
<p>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:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="n">p</span><span class="p">,</span> <span class="k">c</span><span class="p">,</span> <span class="n">a</span> <span class="k">FROM</span> <span class="n">BlogPost</span> <span class="n">p</span> <span class="k">JOIN</span> <span class="n">p</span><span class="p">.</span><span class="n">category</span> <span class="k">c</span> <span class="k">JOIN</span> <span class="n">p</span><span class="p">.</span><span class="n">author</span> <span class="n">a</span> <span class="k">WHERE</span> <span class="p">...</span>
</pre></div>
</div>
<p>Now in this query the blog post is the root entity, meaning its 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.</p>
<p>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:</p>
<div class="highlight-sql"><div class="highlight"><pre><span class="k">SELECT</span> <span class="k">count</span><span class="p">(</span><span class="k">DISTINCT</span> <span class="n">p</span><span class="p">.</span><span class="n">id</span><span class="p">)</span> <span class="k">FROM</span> <span class="n">BlogPost</span> <span class="n">p</span> <span class="k">JOIN</span> <span class="n">p</span><span class="p">.</span><span class="n">category</span> <span class="k">c</span> <span class="k">JOIN</span> <span class="n">p</span><span class="p">.</span><span class="n">author</span> <span class="n">a</span> <span class="k">WHERE</span> <span class="p">...</span>
</pre></div>
</div>
<p>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. Lets see how the
API would look for this use-case:</p>
<div class="highlight-php"><div class="highlight"><pre><span class="cp"><?php</span>
<span class="nv">$pageNum</span> <span class="o">=</span> <span class="mi">1</span><span class="p">;</span>
<span class="nv">$query</span> <span class="o">=</span> <span class="nv">$em</span><span class="o">-></span><span class="na">createQuery</span><span class="p">(</span><span class="nv">$dql</span><span class="p">);</span>
<span class="nv">$query</span><span class="o">-></span><span class="na">setFirstResult</span><span class="p">(</span> <span class="p">(</span><span class="nv">$pageNum</span><span class="o">-</span><span class="mi">1</span><span class="p">)</span> <span class="o">*</span> <span class="mi">20</span><span class="p">)</span><span class="o">-></span><span class="na">setMaxResults</span><span class="p">(</span><span class="mi">20</span><span class="p">);</span>
<span class="nv">$totalResults</span> <span class="o">=</span> <span class="nx">Paginate</span><span class="o">::</span><span class="na">count</span><span class="p">(</span><span class="nv">$query</span><span class="p">);</span>
<span class="nv">$results</span> <span class="o">=</span> <span class="nv">$query</span><span class="o">-></span><span class="na">getResult</span><span class="p">();</span>
</pre></div>
</div>
<p>The <tt class="docutils literal"><span class="pre">Paginate::count(Query</span> <span class="pre">$query)</span></tt> looks like:</p>
<div class="highlight-php"><div class="highlight"><pre><span class="cp"><?php</span>
<span class="k">class</span> <span class="nc">Paginate</span>
<span class="p">{</span>
<span class="k">static</span> <span class="k">public</span> <span class="k">function</span> <span class="nf">count</span><span class="p">(</span><span class="nx">Query</span> <span class="nv">$query</span><span class="p">)</span>
<span class="p">{</span>
<span class="cm">/* @var $countQuery Query */</span>
<span class="nv">$countQuery</span> <span class="o">=</span> <span class="k">clone</span> <span class="nv">$query</span><span class="p">;</span>
<span class="nv">$countQuery</span><span class="o">-></span><span class="na">setHint</span><span class="p">(</span><span class="nx">Query</span><span class="o">::</span><span class="na">HINT_CUSTOM_TREE_WALKERS</span><span class="p">,</span> <span class="k">array</span><span class="p">(</span><span class="s1">'DoctrineExtensions\Paginate\CountSqlWalker'</span><span class="p">));</span>
<span class="nv">$countQuery</span><span class="o">-></span><span class="na">setFirstResult</span><span class="p">(</span><span class="k">null</span><span class="p">)</span><span class="o">-></span><span class="na">setMaxResults</span><span class="p">(</span><span class="k">null</span><span class="p">);</span>
<span class="k">return</span> <span class="nv">$countQuery</span><span class="o">-></span><span class="na">getSingleScalarResult</span><span class="p">();</span>
<span class="p">}</span>
<span class="p">}</span>
</pre></div>
</div>
<p>It clones the query, resets the limit clause first and max results
and registers the <tt class="docutils literal"><span class="pre">CountSqlWalker</span></tt> customer tree walker which
will modify the AST to execute a count query. The walkers
implementation is:</p>
<div class="highlight-php"><div class="highlight"><pre><span class="cp"><?php</span>
<span class="k">class</span> <span class="nc">CountSqlWalker</span> <span class="k">extends</span> <span class="nx">TreeWalkerAdapter</span>
<span class="p">{</span>
<span class="sd">/**</span>
<span class="sd"> * Walks down a SelectStatement AST node, thereby generating the appropriate SQL.</span>
<span class="sd"> *</span>
<span class="sd"> * @return string The SQL.</span>
<span class="sd"> */</span>
<span class="k">public</span> <span class="k">function</span> <span class="nf">walkSelectStatement</span><span class="p">(</span><span class="nx">SelectStatement</span> <span class="nv">$AST</span><span class="p">)</span>
<span class="p">{</span>
<span class="nv">$parent</span> <span class="o">=</span> <span class="k">null</span><span class="p">;</span>
<span class="nv">$parentName</span> <span class="o">=</span> <span class="k">null</span><span class="p">;</span>
<span class="k">foreach</span> <span class="p">(</span><span class="nv">$this</span><span class="o">-></span><span class="na">_getQueryComponents</span><span class="p">()</span> <span class="k">AS</span> <span class="nv">$dqlAlias</span> <span class="o">=></span> <span class="nv">$qComp</span><span class="p">)</span> <span class="p">{</span>
<span class="k">if</span> <span class="p">(</span><span class="nv">$qComp</span><span class="p">[</span><span class="s1">'parent'</span><span class="p">]</span> <span class="o">===</span> <span class="k">null</span> <span class="o">&&</span> <span class="nv">$qComp</span><span class="p">[</span><span class="s1">'nestingLevel'</span><span class="p">]</span> <span class="o">==</span> <span class="mi">0</span><span class="p">)</span> <span class="p">{</span>
<span class="nv">$parent</span> <span class="o">=</span> <span class="nv">$qComp</span><span class="p">;</span>
<span class="nv">$parentName</span> <span class="o">=</span> <span class="nv">$dqlAlias</span><span class="p">;</span>
<span class="k">break</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="nv">$pathExpression</span> <span class="o">=</span> <span class="k">new</span> <span class="nx">PathExpression</span><span class="p">(</span>
<span class="nx">PathExpression</span><span class="o">::</span><span class="na">TYPE_STATE_FIELD</span> <span class="o">|</span> <span class="nx">PathExpression</span><span class="o">::</span><span class="na">TYPE_SINGLE_VALUED_ASSOCIATION</span><span class="p">,</span> <span class="nv">$parentName</span><span class="p">,</span>
<span class="nv">$parent</span><span class="p">[</span><span class="s1">'metadata'</span><span class="p">]</span><span class="o">-></span><span class="na">getSingleIdentifierFieldName</span><span class="p">()</span>
<span class="p">);</span>
<span class="nv">$pathExpression</span><span class="o">-></span><span class="na">type</span> <span class="o">=</span> <span class="nx">PathExpression</span><span class="o">::</span><span class="na">TYPE_STATE_FIELD</span><span class="p">;</span>
<span class="nv">$AST</span><span class="o">-></span><span class="na">selectClause</span><span class="o">-></span><span class="na">selectExpressions</span> <span class="o">=</span> <span class="k">array</span><span class="p">(</span>
<span class="k">new</span> <span class="nx">SelectExpression</span><span class="p">(</span>
<span class="k">new</span> <span class="nx">AggregateExpression</span><span class="p">(</span><span class="s1">'count'</span><span class="p">,</span> <span class="nv">$pathExpression</span><span class="p">,</span> <span class="k">true</span><span class="p">),</span> <span class="k">null</span>
<span class="p">)</span>
<span class="p">);</span>
<span class="p">}</span>
<span class="p">}</span>
</pre></div>
</div>
<p>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).</p>
</div>
<div class="section" id="modify-the-output-walker-to-generate-vendor-specific-sql">
<h2>Modify the Output Walker to generate Vendor specific SQL<a class="headerlink" href="#modify-the-output-walker-to-generate-vendor-specific-sql" title="Permalink to this headline">¶</a></h2>
<p>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 <tt class="docutils literal"><span class="pre">Query::setHint($name,</span> <span class="pre">$value)</span></tt> as shown in the
previous example with the <tt class="docutils literal"><span class="pre">HINT_CUSTOM_TREE_WALKERS</span></tt> query hint.</p>
<p>We will implement a custom Output Walker that allows to specify the
SQL_NO_CACHE query hint.</p>
<div class="highlight-php"><div class="highlight"><pre><span class="cp"><?php</span>
<span class="nv">$dql</span> <span class="o">=</span> <span class="s2">"SELECT p, c, a FROM BlogPost p JOIN p.category c JOIN p.author a WHERE ..."</span><span class="p">;</span>
<span class="nv">$query</span> <span class="o">=</span> <span class="nv">$m</span><span class="o">-></span><span class="na">createQuery</span><span class="p">(</span><span class="nv">$dql</span><span class="p">);</span>
<span class="nv">$query</span><span class="o">-></span><span class="na">setHint</span><span class="p">(</span><span class="nx">Query</span><span class="o">::</span><span class="na">HINT_CUSTOM_OUTPUT_WALKER</span><span class="p">,</span> <span class="s1">'DoctrineExtensions\Query\MysqlWalker'</span><span class="p">);</span>
<span class="nv">$query</span><span class="o">-></span><span class="na">setHint</span><span class="p">(</span><span class="s2">"mysqlWalker.sqlNoCache"</span><span class="p">,</span> <span class="k">true</span><span class="p">);</span>
<span class="nv">$results</span> <span class="o">=</span> <span class="nv">$query</span><span class="o">-></span><span class="na">getResult</span><span class="p">();</span>
</pre></div>
</div>
<p>Our <tt class="docutils literal"><span class="pre">MysqlWalker</span></tt> will extend the default <tt class="docutils literal"><span class="pre">SqlWalker</span></tt>. We will
modify the generation of the SELECT clause, adding the
SQL_NO_CACHE on those queries that need it:</p>
<div class="highlight-php"><div class="highlight"><pre><span class="cp"><?php</span>
<span class="k">class</span> <span class="nc">MysqlWalker</span> <span class="k">extends</span> <span class="nx">SqlWalker</span>
<span class="p">{</span>
<span class="sd">/**</span>
<span class="sd"> * Walks down a SelectClause AST node, thereby generating the appropriate SQL.</span>
<span class="sd"> *</span>
<span class="sd"> * @param $selectClause</span>
<span class="sd"> * @return string The SQL.</span>
<span class="sd"> */</span>
<span class="k">public</span> <span class="k">function</span> <span class="nf">walkSelectClause</span><span class="p">(</span><span class="nv">$selectClause</span><span class="p">)</span>
<span class="p">{</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="k">parent</span><span class="o">::</span><span class="na">walkSelectClause</span><span class="p">(</span><span class="nv">$selectClause</span><span class="p">);</span>
<span class="k">if</span> <span class="p">(</span><span class="nv">$this</span><span class="o">-></span><span class="na">getQuery</span><span class="p">()</span><span class="o">-></span><span class="na">getHint</span><span class="p">(</span><span class="s1">'mysqlWalker.sqlNoCache'</span><span class="p">)</span> <span class="o">===</span> <span class="k">true</span><span class="p">)</span> <span class="p">{</span>
<span class="k">if</span> <span class="p">(</span><span class="nv">$selectClause</span><span class="o">-></span><span class="na">isDistinct</span><span class="p">)</span> <span class="p">{</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="nb">str_replace</span><span class="p">(</span><span class="s1">'SELECT DISTINCT'</span><span class="p">,</span> <span class="s1">'SELECT DISTINCT SQL_NO_CACHE'</span><span class="p">,</span> <span class="nv">$sql</span><span class="p">);</span>
<span class="p">}</span> <span class="k">else</span> <span class="p">{</span>
<span class="nv">$sql</span> <span class="o">=</span> <span class="nb">str_replace</span><span class="p">(</span><span class="s1">'SELECT'</span><span class="p">,</span> <span class="s1">'SELECT SQL_NO_CACHE'</span><span class="p">,</span> <span class="nv">$sql</span><span class="p">);</span>
<span class="p">}</span>
<span class="p">}</span>
<span class="k">return</span> <span class="nv">$sql</span><span class="p">;</span>
<span class="p">}</span>
<span class="p">}</span>
</pre></div>
</div>
<p>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.</p>
</div>
</div>
</div>
</div>
</div>
<div class="sphinxsidebar">
<div class="sphinxsidebarwrapper">
<div id="searchbox" style="">
<h3>Search</h3>
<form class="search" action="http://readthedocs.org/search/project/" method="get">
<input type="text" name="q" size="18">
<input type="submit" value="Go">
<input type="hidden" name="selected_facets" value="project:">
</form>
</div>
<h3><a href="../index.html">Table Of Contents</a></h3>
<ul>
<li><a class="reference internal" href="#">Extending DQL in Doctrine 2: Custom AST Walkers</a><ul>
<li><a class="reference internal" href="#generic-count-query-for-pagination">Generic count query for pagination</a></li>
<li><a class="reference internal" href="#modify-the-output-walker-to-generate-vendor-specific-sql">Modify the Output Walker to generate Vendor specific SQL</a></li>
</ul>
</li>
</ul>
<h4>Previous topic</h4>
<p class="topless"><a href="decorator-pattern.html"
title="previous chapter">Persisting the Decorator Pattern</a></p>
<h4>Next topic</h4>
<p class="topless"><a href="dql-user-defined-functions.html"
title="next chapter">DQL User Defined Functions</a></p>
<h3>This Page</h3>
<ul class="this-page-menu">
<li><a href="../_sources/cookbook/dql-custom-walkers.txt"
rel="nofollow">Show Source</a></li>
</ul>
</div>
</div>
<div class="clearer"></div>
</div>
<div class="footer">
© Copyright 2010-12, Doctrine Project Team.
Created using <a href="http://sphinx.pocoo.org/">Sphinx</a> 1.2.3.
<br/>
<a target="_BLANK" href="http://www.servergrove.com"><img src="../_static/servergrove.jpg" /></a> <br/><br/>
<form action="https://www.paypal.com/cgi-bin/webscr" method="post">
<input type="hidden" name="cmd" value="_s-xclick" />
<input type="hidden" name="hosted_button_id" value="BAE2E3XANQ77Y" />
<input type="Submit" value="Donate via PayPal" />
</form>
</div>
</div>
<div id="bot-rcnr">
<div class="tl"><!-- corner --></div>
</div>
</div>
<a class="githublink" href="http://github.com/doctrine">Fork me on GitHub</a>
</body>
</html>
|