query-builder.rst 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541
  1. The QueryBuilder
  2. ================
  3. A ``QueryBuilder`` provides an API that is designed for
  4. conditionally constructing a DQL query in several steps.
  5. It provides a set of classes and methods that is able to
  6. programmatically build queries, and also provides a fluent API.
  7. This means that you can change between one methodology to the other
  8. as you want, and also pick one if you prefer.
  9. Constructing a new QueryBuilder object
  10. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  11. The same way you build a normal Query, you build a ``QueryBuilder``
  12. object, just providing the correct method name. Here is an example
  13. how to build a ``QueryBuilder`` object:
  14. .. code-block:: php
  15. <?php
  16. // $em instanceof EntityManager
  17. // example1: creating a QueryBuilder instance
  18. $qb = $em->createQueryBuilder();
  19. Once you have created an instance of QueryBuilder, it provides a
  20. set of useful informative functions that you can use. One good
  21. example is to inspect what type of object the ``QueryBuilder`` is.
  22. .. code-block:: php
  23. <?php
  24. // $qb instanceof QueryBuilder
  25. // example2: retrieving type of QueryBuilder
  26. echo $qb->getType(); // Prints: 0
  27. There're currently 3 possible return values for ``getType()``:
  28. - ``QueryBuilder::SELECT``, which returns value 0
  29. - ``QueryBuilder::DELETE``, returning value 1
  30. - ``QueryBuilder::UPDATE``, which returns value 2
  31. It is possible to retrieve the associated ``EntityManager`` of the
  32. current ``QueryBuilder``, its DQL and also a ``Query`` object when
  33. you finish building your DQL.
  34. .. code-block:: php
  35. <?php
  36. // $qb instanceof QueryBuilder
  37. // example3: retrieve the associated EntityManager
  38. $em = $qb->getEntityManager();
  39. // example4: retrieve the DQL string of what was defined in QueryBuilder
  40. $dql = $qb->getDql();
  41. // example5: retrieve the associated Query object with the processed DQL
  42. $q = $qb->getQuery();
  43. Internally, ``QueryBuilder`` works with a DQL cache to increase
  44. performance. Any changes that may affect the generated DQL actually
  45. modifies the state of ``QueryBuilder`` to a stage we call
  46. STATE\_DIRTY. One ``QueryBuilder`` can be in two different states:
  47. - ``QueryBuilder::STATE_CLEAN``, which means DQL haven't been
  48. altered since last retrieval or nothing were added since its
  49. instantiation
  50. - ``QueryBuilder::STATE_DIRTY``, means DQL query must (and will)
  51. be processed on next retrieval
  52. Working with QueryBuilder
  53. ~~~~~~~~~~~~~~~~~~~~~~~~~
  54. High level API methods
  55. ^^^^^^^^^^^^^^^^^^^^^^
  56. To simplify even more the way you build a query in Doctrine, we can take
  57. advantage of what we call Helper methods. For all base code, there
  58. is a set of useful methods to simplify a programmer's life. To
  59. illustrate how to work with them, here is the same example 6
  60. re-written using ``QueryBuilder`` helper methods:
  61. .. code-block:: php
  62. <?php
  63. // $qb instanceof QueryBuilder
  64. $qb->select('u')
  65. ->from('User', 'u')
  66. ->where('u.id = ?1')
  67. ->orderBy('u.name', 'ASC');
  68. ``QueryBuilder`` helper methods are considered the standard way to
  69. build DQL queries. Although it is supported, it should be avoided
  70. to use string based queries and greatly encouraged to use
  71. ``$qb->expr()->*`` methods. Here is a converted example 8 to
  72. suggested standard way to build queries:
  73. .. code-block:: php
  74. <?php
  75. // $qb instanceof QueryBuilder
  76. $qb->select(array('u')) // string 'u' is converted to array internally
  77. ->from('User', 'u')
  78. ->where($qb->expr()->orX(
  79. $qb->expr()->eq('u.id', '?1'),
  80. $qb->expr()->like('u.nickname', '?2')
  81. ))
  82. ->orderBy('u.surname', 'ASC'));
  83. Here is a complete list of helper methods available in ``QueryBuilder``:
  84. .. code-block:: php
  85. <?php
  86. class QueryBuilder
  87. {
  88. // Example - $qb->select('u')
  89. // Example - $qb->select(array('u', 'p'))
  90. // Example - $qb->select($qb->expr()->select('u', 'p'))
  91. public function select($select = null);
  92. // Example - $qb->delete('User', 'u')
  93. public function delete($delete = null, $alias = null);
  94. // Example - $qb->update('Group', 'g')
  95. public function update($update = null, $alias = null);
  96. // Example - $qb->set('u.firstName', $qb->expr()->literal('Arnold'))
  97. // Example - $qb->set('u.numChilds', 'u.numChilds + ?1')
  98. // Example - $qb->set('u.numChilds', $qb->expr()->sum('u.numChilds', '?1'))
  99. public function set($key, $value);
  100. // Example - $qb->from('Phonenumber', 'p')
  101. public function from($from, $alias = null);
  102. // Example - $qb->innerJoin('u.Group', 'g', Expr\Join::WITH, $qb->expr()->eq('u.status_id', '?1'))
  103. // Example - $qb->innerJoin('u.Group', 'g', 'WITH', 'u.status = ?1')
  104. public function innerJoin($join, $alias = null, $conditionType = null, $condition = null);
  105. // Example - $qb->leftJoin('u.Phonenumbers', 'p', Expr\Join::WITH, $qb->expr()->eq('p.area_code', 55))
  106. // Example - $qb->leftJoin('u.Phonenumbers', 'p', 'WITH', 'p.area_code = 55')
  107. public function leftJoin($join, $alias = null, $conditionType = null, $condition = null);
  108. // NOTE: ->where() overrides all previously set conditions
  109. //
  110. // Example - $qb->where('u.firstName = ?1', $qb->expr()->eq('u.surname', '?2'))
  111. // Example - $qb->where($qb->expr()->andX($qb->expr()->eq('u.firstName', '?1'), $qb->expr()->eq('u.surname', '?2')))
  112. // Example - $qb->where('u.firstName = ?1 AND u.surname = ?2')
  113. public function where($where);
  114. // Example - $qb->andWhere($qb->expr()->orX($qb->expr()->lte('u.age', 40), 'u.numChild = 0'))
  115. public function andWhere($where);
  116. // Example - $qb->orWhere($qb->expr()->between('u.id', 1, 10));
  117. public function orWhere($where);
  118. // NOTE: -> groupBy() overrides all previously set grouping conditions
  119. //
  120. // Example - $qb->groupBy('u.id')
  121. public function groupBy($groupBy);
  122. // Example - $qb->addGroupBy('g.name')
  123. public function addGroupBy($groupBy);
  124. // NOTE: -> having() overrides all previously set having conditions
  125. //
  126. // Example - $qb->having('u.salary >= ?1')
  127. // Example - $qb->having($qb->expr()->gte('u.salary', '?1'))
  128. public function having($having);
  129. // Example - $qb->andHaving($qb->expr()->gt($qb->expr()->count('u.numChild'), 0))
  130. public function andHaving($having);
  131. // Example - $qb->orHaving($qb->expr()->lte('g.managerLevel', '100'))
  132. public function orHaving($having);
  133. // NOTE: -> orderBy() overrides all previously set ordering conditions
  134. //
  135. // Example - $qb->orderBy('u.surname', 'DESC')
  136. public function orderBy($sort, $order = null);
  137. // Example - $qb->addOrderBy('u.firstName')
  138. public function addOrderBy($sort, $order = null); // Default $order = 'ASC'
  139. }
  140. Binding parameters to your query
  141. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  142. Doctrine supports dynamic binding of parameters to your query,
  143. similar to preparing queries. You can use both strings and numbers
  144. as placeholders, although both have a slightly different syntax.
  145. Additionally, you must make your choice: Mixing both styles is not
  146. allowed. Binding parameters can simply be achieved as follows:
  147. .. code-block:: php
  148. <?php
  149. // $qb instanceof QueryBuilder
  150. $qb->select('u')
  151. ->from('User u')
  152. ->where('u.id = ?1')
  153. ->orderBy('u.name', 'ASC');
  154. ->setParameter(1, 100); // Sets ?1 to 100, and thus we will fetch a user with u.id = 100
  155. You are not forced to enumerate your placeholders as the
  156. alternative syntax is available:
  157. .. code-block:: php
  158. <?php
  159. // $qb instanceof QueryBuilder
  160. $qb->select('u')
  161. ->from('User u')
  162. ->where('u.id = :identifier')
  163. ->orderBy('u.name', 'ASC');
  164. ->setParameter('identifier', 100); // Sets :identifier to 100, and thus we will fetch a user with u.id = 100
  165. Note that numeric placeholders start with a ? followed by a number
  166. while the named placeholders start with a : followed by a string.
  167. Calling ``setParameter()`` automatically infers which type you are setting as
  168. value. This works for integers, arrays of strings/integers, DateTime instances
  169. and for managed entities. If you want to set a type explicitly you can call
  170. the third argument to ``setParameter()`` explicitly. It accepts either a PDO
  171. type or a DBAL Type name for conversion.
  172. If you've got several parameters to bind to your query, you can
  173. also use setParameters() instead of setParameter() with the
  174. following syntax:
  175. .. code-block:: php
  176. <?php
  177. // $qb instanceof QueryBuilder
  178. // Query here...
  179. $qb->setParameters(array(1 => 'value for ?1', 2 => 'value for ?2'));
  180. Getting already bound parameters is easy - simply use the above
  181. mentioned syntax with "getParameter()" or "getParameters()":
  182. .. code-block:: php
  183. <?php
  184. // $qb instanceof QueryBuilder
  185. // See example above
  186. $params = $qb->getParameters();
  187. // $params instanceof \Doctrine\Common\Collections\ArrayCollection
  188. // Equivalent to
  189. $param = $qb->getParameter(1);
  190. // $param instanceof \Doctrine\ORM\Query\Parameter
  191. Note: If you try to get a parameter that was not bound yet,
  192. getParameter() simply returns NULL.
  193. The API of a Query Parameter is:
  194. .. code-block:: php
  195. namespace Doctrine\ORM\Query;
  196. class Parameter
  197. {
  198. public function getName();
  199. public function getValue();
  200. public function getType();
  201. public function setValue($value, $type = null);
  202. }
  203. Limiting the Result
  204. ^^^^^^^^^^^^^^^^^^^
  205. To limit a result the query builder has some methods in common with
  206. the Query object which can be retrieved from ``EntityManager#createQuery()``.
  207. .. code-block:: php
  208. <?php
  209. // $qb instanceof QueryBuilder
  210. $offset = (int)$_GET['offset'];
  211. $limit = (int)$_GET['limit'];
  212. $qb->add('select', 'u')
  213. ->add('from', 'User u')
  214. ->add('orderBy', 'u.name ASC')
  215. ->setFirstResult( $offset )
  216. ->setMaxResults( $limit );
  217. Executing a Query
  218. ^^^^^^^^^^^^^^^^^
  219. The QueryBuilder is a builder object only, it has no means of actually
  220. executing the Query. Additionally a set of parameters such as query hints
  221. cannot be set on the QueryBuilder itself. This is why you always have to convert
  222. a querybuilder instance into a Query object:
  223. .. code-block:: php
  224. <?php
  225. // $qb instanceof QueryBuilder
  226. $query = $qb->getQuery();
  227. // Set additional Query options
  228. $query->setQueryHint('foo', 'bar');
  229. $query->useResultCache('my_cache_id');
  230. // Execute Query
  231. $result = $query->getResult();
  232. $single = $query->getSingleResult();
  233. $array = $query->getArrayResult();
  234. $scalar = $query->getScalarResult();
  235. $singleScalar = $query->getSingleScalarResult();
  236. The Expr class
  237. ^^^^^^^^^^^^^^
  238. To workaround some of the issues that ``add()`` method may cause,
  239. Doctrine created a class that can be considered as a helper for
  240. building expressions. This class is called ``Expr``, which provides a
  241. set of useful methods to help build expressions:
  242. .. code-block:: php
  243. <?php
  244. // $qb instanceof QueryBuilder
  245. // example8: QueryBuilder port of: "SELECT u FROM User u WHERE u.id = ? OR u.nickname LIKE ? ORDER BY u.surname DESC" using Expr class
  246. $qb->add('select', new Expr\Select(array('u')))
  247. ->add('from', new Expr\From('User', 'u'))
  248. ->add('where', $qb->expr()->orX(
  249. $qb->expr()->eq('u.id', '?1'),
  250. $qb->expr()->like('u.nickname', '?2')
  251. ))
  252. ->add('orderBy', new Expr\OrderBy('u.name', 'ASC'));
  253. Although it still sounds complex, the ability to programmatically
  254. create conditions are the main feature of ``Expr``. Here it is a
  255. complete list of supported helper methods available:
  256. .. code-block:: php
  257. <?php
  258. class Expr
  259. {
  260. /** Conditional objects **/
  261. // Example - $qb->expr()->andX($cond1 [, $condN])->add(...)->...
  262. public function andX($x = null); // Returns Expr\AndX instance
  263. // Example - $qb->expr()->orX($cond1 [, $condN])->add(...)->...
  264. public function orX($x = null); // Returns Expr\OrX instance
  265. /** Comparison objects **/
  266. // Example - $qb->expr()->eq('u.id', '?1') => u.id = ?1
  267. public function eq($x, $y); // Returns Expr\Comparison instance
  268. // Example - $qb->expr()->neq('u.id', '?1') => u.id <> ?1
  269. public function neq($x, $y); // Returns Expr\Comparison instance
  270. // Example - $qb->expr()->lt('u.id', '?1') => u.id < ?1
  271. public function lt($x, $y); // Returns Expr\Comparison instance
  272. // Example - $qb->expr()->lte('u.id', '?1') => u.id <= ?1
  273. public function lte($x, $y); // Returns Expr\Comparison instance
  274. // Example - $qb->expr()->gt('u.id', '?1') => u.id > ?1
  275. public function gt($x, $y); // Returns Expr\Comparison instance
  276. // Example - $qb->expr()->gte('u.id', '?1') => u.id >= ?1
  277. public function gte($x, $y); // Returns Expr\Comparison instance
  278. // Example - $qb->expr()->isNull('u.id') => u.id IS NULL
  279. public function isNull($x); // Returns string
  280. // Example - $qb->expr()->isNotNull('u.id') => u.id IS NOT NULL
  281. public function isNotNull($x); // Returns string
  282. /** Arithmetic objects **/
  283. // Example - $qb->expr()->prod('u.id', '2') => u.id * 2
  284. public function prod($x, $y); // Returns Expr\Math instance
  285. // Example - $qb->expr()->diff('u.id', '2') => u.id - 2
  286. public function diff($x, $y); // Returns Expr\Math instance
  287. // Example - $qb->expr()->sum('u.id', '2') => u.id + 2
  288. public function sum($x, $y); // Returns Expr\Math instance
  289. // Example - $qb->expr()->quot('u.id', '2') => u.id / 2
  290. public function quot($x, $y); // Returns Expr\Math instance
  291. /** Pseudo-function objects **/
  292. // Example - $qb->expr()->exists($qb2->getDql())
  293. public function exists($subquery); // Returns Expr\Func instance
  294. // Example - $qb->expr()->all($qb2->getDql())
  295. public function all($subquery); // Returns Expr\Func instance
  296. // Example - $qb->expr()->some($qb2->getDql())
  297. public function some($subquery); // Returns Expr\Func instance
  298. // Example - $qb->expr()->any($qb2->getDql())
  299. public function any($subquery); // Returns Expr\Func instance
  300. // Example - $qb->expr()->not($qb->expr()->eq('u.id', '?1'))
  301. public function not($restriction); // Returns Expr\Func instance
  302. // Example - $qb->expr()->in('u.id', array(1, 2, 3))
  303. // Make sure that you do NOT use something similar to $qb->expr()->in('value', array('stringvalue')) as this will cause Doctrine to throw an Exception.
  304. // Instead, use $qb->expr()->in('value', array('?1')) and bind your parameter to ?1 (see section above)
  305. public function in($x, $y); // Returns Expr\Func instance
  306. // Example - $qb->expr()->notIn('u.id', '2')
  307. public function notIn($x, $y); // Returns Expr\Func instance
  308. // Example - $qb->expr()->like('u.firstname', $qb->expr()->literal('Gui%'))
  309. public function like($x, $y); // Returns Expr\Comparison instance
  310. // Example - $qb->expr()->between('u.id', '1', '10')
  311. public function between($val, $x, $y); // Returns Expr\Func
  312. /** Function objects **/
  313. // Example - $qb->expr()->trim('u.firstname')
  314. public function trim($x); // Returns Expr\Func
  315. // Example - $qb->expr()->concat('u.firstname', $qb->expr()->concat($qb->expr()->literal(' '), 'u.lastname'))
  316. public function concat($x, $y); // Returns Expr\Func
  317. // Example - $qb->expr()->substr('u.firstname', 0, 1)
  318. public function substr($x, $from, $len); // Returns Expr\Func
  319. // Example - $qb->expr()->lower('u.firstname')
  320. public function lower($x); // Returns Expr\Func
  321. // Example - $qb->expr()->upper('u.firstname')
  322. public function upper($x); // Returns Expr\Func
  323. // Example - $qb->expr()->length('u.firstname')
  324. public function length($x); // Returns Expr\Func
  325. // Example - $qb->expr()->avg('u.age')
  326. public function avg($x); // Returns Expr\Func
  327. // Example - $qb->expr()->max('u.age')
  328. public function max($x); // Returns Expr\Func
  329. // Example - $qb->expr()->min('u.age')
  330. public function min($x); // Returns Expr\Func
  331. // Example - $qb->expr()->abs('u.currentBalance')
  332. public function abs($x); // Returns Expr\Func
  333. // Example - $qb->expr()->sqrt('u.currentBalance')
  334. public function sqrt($x); // Returns Expr\Func
  335. // Example - $qb->expr()->count('u.firstname')
  336. public function count($x); // Returns Expr\Func
  337. // Example - $qb->expr()->countDistinct('u.surname')
  338. public function countDistinct($x); // Returns Expr\Func
  339. }
  340. Low Level API
  341. ^^^^^^^^^^^^^
  342. Now we have describe the low level (thought of as the
  343. hardcore method) of creating queries. It may be useful to work at
  344. this level for optimization purposes, but most of the time it is
  345. preferred to work at a higher level of abstraction.
  346. All helper methods in ``QueryBuilder`` actually rely on a single
  347. one: ``add()``. This method is responsible of building every piece
  348. of DQL. It takes 3 parameters: ``$dqlPartName``, ``$dqlPart`` and
  349. ``$append`` (default=false)
  350. - ``$dqlPartName``: Where the ``$dqlPart`` should be placed.
  351. Possible values: select, from, where, groupBy, having, orderBy
  352. - ``$dqlPart``: What should be placed in ``$dqlPartName``. Accepts
  353. a string or any instance of ``Doctrine\ORM\Query\Expr\*``
  354. - ``$append``: Optional flag (default=false) if the ``$dqlPart``
  355. should override all previously defined items in ``$dqlPartName`` or
  356. not (no effect on the ``where`` and ``having`` DQL query parts,
  357. which always override all previously defined items)
  358. -
  359. .. code-block:: php
  360. <?php
  361. // $qb instanceof QueryBuilder
  362. // example6: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder string support
  363. $qb->add('select', 'u')
  364. ->add('from', 'User u')
  365. ->add('where', 'u.id = ?1')
  366. ->add('orderBy', 'u.name ASC');
  367. Expr\* classes
  368. ^^^^^^^^^^^^^^
  369. When you call ``add()`` with string, it internally evaluates to an
  370. instance of ``Doctrine\ORM\Query\Expr\Expr\*`` class. Here is the
  371. same query of example 6 written using
  372. ``Doctrine\ORM\Query\Expr\Expr\*`` classes:
  373. .. code-block:: php
  374. <?php
  375. // $qb instanceof QueryBuilder
  376. // example7: how to define: "SELECT u FROM User u WHERE u.id = ? ORDER BY u.name ASC" using QueryBuilder using Expr\* instances
  377. $qb->add('select', new Expr\Select(array('u')))
  378. ->add('from', new Expr\From('User', 'u'))
  379. ->add('where', new Expr\Comparison('u.id', '=', '?1'))
  380. ->add('orderBy', new Expr\OrderBy('u.name', 'ASC'));
  381. Of course this is the hardest way to build a DQL query in Doctrine.
  382. To simplify some of these efforts, we introduce what we call as
  383. ``Expr`` helper class.