Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
Newer
Older
100644 241 lines (185 sloc) 9.293 kb
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
1 DQL User Defined Functions
2 ==========================
3
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
4 .. sectionauthor:: Benjamin Eberlei <kontakt@beberlei.de>
5
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
6 By default DQL supports a limited subset of all the vendor-specific
7 SQL functions common between all the vendors. However in many cases
8 once you have decided on a specific database vendor, you will never
9 change it during the life of your project. This decision for a
10 specific vendor potentially allows you to make use of powerful SQL
11 features that are unique to the vendor.
12
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
13 It is worth to mention that Doctrine 2 also allows you to handwrite
14 your SQL instead of extending the DQL parser. Extending DQL is sort of an
15 advanced extension point. You can map arbitrary SQL to your objects
16 and gain access to vendor specific functionalities using the
17 ``EntityManager#createNativeQuery()`` API as described in
18 the :doc:`Native Query <../reference/native-sql>` chapter.
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
19
20
21 The DQL Parser has hooks to register functions that can then be
22 used in your DQL queries and transformed into SQL, allowing to
23 extend Doctrines Query capabilities to the vendors strength. This
24 post explains the Used-Defined Functions API (UDF) of the Dql
25 Parser and shows some examples to give you some hints how you would
26 extend DQL.
27
28 There are three types of functions in DQL, those that return a
29 numerical value, those that return a string and those that return a
30 Date. Your custom method has to be registered as either one of
31 those. The return type information is used by the DQL parser to
32 check possible syntax errors during the parsing process, for
33 example using a string function return value in a math expression.
34
35 Registering your own DQL functions
36 ----------------------------------
37
38 You can register your functions adding them to the ORM
39 configuration:
40
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
41 .. code-block:: php
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
42
43 <?php
44 $config = new \Doctrine\ORM\Configuration();
45 $config->addCustomStringFunction($name, $class);
46 $config->addCustomNumericFunction($name, $class);
47 $config->addCustomDatetimeFunction($name, $class);
48
49 $em = EntityManager::create($dbParams, $config);
50
51 The ``$name`` is the name the function will be referred to in the
52 DQL query. ``$class`` is a string of a class-name which has to
53 extend ``Doctrine\ORM\Query\Node\FunctionNode``. This is a class
54 that offers all the necessary API and methods to implement a UDF.
55
56 In this post we will implement some MySql specific Date calculation
57 methods, which are quite handy in my opinion:
58
59 Date Diff
60 ---------
61
62 `Mysql's DateDiff function <http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_datediff>`_
63 takes two dates as argument and calculates the difference in days
64 with ``date1-date2``.
65
66 The DQL parser is a top-down recursive descent parser to generate
67 the Abstract-Syntax Tree (AST) and uses a TreeWalker approach to
68 generate the appropriate SQL from the AST. This makes reading the
69 Parser/TreeWalker code manageable in a finite amount of time.
70
71 The ``FunctionNode`` class I referred to earlier requires you to
72 implement two methods, one for the parsing process (obviously)
73 called ``parse`` and one for the TreeWalker process called
74 ``getSql()``. I show you the code for the DateDiff method and
75 discuss it step by step:
76
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
77 .. code-block:: php
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
78
79 <?php
80 /**
81 * DateDiffFunction ::= "DATEDIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
82 */
83 class DateDiff extends FunctionNode
84 {
85 // (1)
86 public $firstDateExpression = null;
87 public $secondDateExpression = null;
88
89 public function parse(\Doctrine\ORM\Query\Parser $parser)
90 {
91 $parser->match(Lexer::T_IDENTIFIER); // (2)
92 $parser->match(Lexer::T_OPEN_PARENTHESIS); // (3)
93 $this->firstDateExpression = $parser->ArithmeticPrimary(); // (4)
94 $parser->match(Lexer::T_COMMA); // (5)
95 $this->secondDateExpression = $parser->ArithmeticPrimary(); // (6)
96 $parser->match(Lexer::T_CLOSE_PARENTHESIS); // (3)
97 }
98
99 public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
100 {
101 return 'DATEDIFF(' .
102 $this->firstDateExpression->dispatch($sqlWalker) . ', ' .
103 $this->secondDateExpression->dispatch($sqlWalker) .
104 ')'; // (7)
105 }
106 }
107
108 The Parsing process of the DATEDIFF function is going to find two
109 expressions the date1 and the date2 values, whose AST Node
110 representations will be saved in the variables of the DateDiff
111 FunctionNode instance at (1).
112
113 The parse() method has to cut the function call "DATEDIFF" and its
114 argument into pieces. Since the parser detects the function using a
115 lookahead the T\_IDENTIFIER of the function name has to be taken
116 from the stack (2), followed by a detection of the arguments in
117 (4)-(6). The opening and closing parenthesis have to be detected
118 also. This happens during the Parsing process and leads to the
119 generation of a DateDiff FunctionNode somewhere in the AST of the
120 dql statement.
121
122 The ``ArithmeticPrimary`` method call is the most common
123 denominator of valid EBNF tokens taken from the
124 `DQL EBNF grammar <http://www.doctrine-project.org/documentation/manual/2_0/en/dql-doctrine-query-language#ebnf>`_
125 that matches our requirements for valid input into the DateDiff Dql
126 function. Picking the right tokens for your methods is a tricky
127 business, but the EBNF grammar is pretty helpful finding it, as is
128 looking at the Parser source code.
129
130 Now in the TreeWalker process we have to pick up this node and
131 generate SQL from it, which apparently is quite easy looking at the
132 code in (7). Since we don't know which type of AST Node the first
133 and second Date expression are we are just dispatching them back to
134 the SQL Walker to generate SQL from and then wrap our DATEDIFF
135 function call around this output.
136
137 Now registering this DateDiff FunctionNode with the ORM using:
138
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
139 .. code-block:: php
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
140
141 <?php
142 $config = new \Doctrine\ORM\Configuration();
143 $config->addCustomStringFunction('DATEDIFF', 'DoctrineExtensions\Query\MySql\DateDiff');
144
145 We can do fancy stuff like:
146
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
147 .. code-block:: sql
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
148
149 SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATEDIFF(CURRENT_TIME(), p.created) < 7
150
151 Date Add
152 --------
153
154 Often useful it the ability to do some simple date calculations in
155 your DQL query using
156 `MySql's DATE\_ADD function <http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add>`_.
157
158 I'll skip the blah and show the code for this function:
159
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
160 .. code-block:: php
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
161
162 <?php
163 /**
164 * DateAddFunction ::=
165 * "DATE_ADD" "(" ArithmeticPrimary ", INTERVAL" ArithmeticPrimary Identifier ")"
166 */
167 class DateAdd extends FunctionNode
168 {
169 public $firstDateExpression = null;
170 public $intervalExpression = null;
171 public $unit = null;
172
173 public function parse(\Doctrine\ORM\Query\Parser $parser)
174 {
175 $parser->match(Lexer::T_IDENTIFIER);
176 $parser->match(Lexer::T_OPEN_PARENTHESIS);
177
178 $this->firstDateExpression = $parser->ArithmeticPrimary();
179
180 $parser->match(Lexer::T_COMMA);
181 $parser->match(Lexer::T_IDENTIFIER);
182
183 $this->intervalExpression = $parser->ArithmeticPrimary();
184
185 $parser->match(Lexer::T_IDENTIFIER);
186
187 /* @var $lexer Lexer */
188 $lexer = $parser->getLexer();
189 $this->unit = $lexer->token['value'];
190
191 $parser->match(Lexer::T_CLOSE_PARENTHESIS);
192 }
193
194 public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
195 {
196 return 'DATE_ADD(' .
197 $this->firstDateExpression->dispatch($sqlWalker) . ', INTERVAL ' .
198 $this->intervalExpression->dispatch($sqlWalker) . ' ' . $this->unit .
199 ')';
200 }
201 }
202
203 The only difference compared to the DATEDIFF here is, we
204 additionally need the ``Lexer`` to access the value of the
205 ``T_IDENTIFIER`` token for the Date Interval unit, for example the
206 MONTH in:
207
4698346 @beberlei Finialized ReST doc changes, merged changes from latest Markdown docs.
beberlei authored
208 .. code-block:: sql
1bfeaf3 @beberlei Initial conversion from Markdown to ReST - Finalized Cookbook
beberlei authored
209
210 SELECT p FROM DoctrineExtensions\Query\BlogPost p WHERE DATE_ADD(CURRENT_TIME(), INTERVAL 4 MONTH) > p.created
211
212 The above method now only supports the specification using
213 ``INTERVAL``, to also allow a real date in DATE\_ADD we need to add
214 some decision logic to the parsing process (makes up for a nice
215 exercise).
216
217 Now as you see, the Parsing process doesn't catch all the possible
218 SQL errors, here we don't match for all the valid inputs for the
219 interval unit. However where necessary we rely on the database
220 vendors SQL parser to show us further errors in the parsing
221 process, for example if the Unit would not be one of the supported
222 values by MySql.
223
224 Conclusion
225 ----------
226
227 Now that you all know how you can implement vendor specific SQL
228 functionalities in DQL, we would be excited to see user extensions
229 that add vendor specific function packages, for example more math
230 functions, XML + GIS Support, Hashing functions and so on.
231
232 For 2.0 we will come with the current set of functions, however for
233 a future version we will re-evaluate if we can abstract even more
234 vendor sql functions and extend the DQL languages scope.
235
236 Code for this Extension to DQL and other Doctrine Extensions can be
237 found
238 `in my Github DoctrineExtensions repository <http://github.com/beberlei/DoctrineExtensions>`_.
239
240
Something went wrong with that request. Please try again.