dql-user-defined-functions.rst 9.1 KB

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