dql-doctrine-query-language.rst 57 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672
  1. Doctrine Query Language
  2. ===========================
  3. DQL stands for Doctrine Query Language and is an Object
  4. Query Language derivate that is very similar to the Hibernate
  5. Query Language (HQL) or the Java Persistence Query Language (JPQL).
  6. In essence, DQL provides powerful querying capabilities over your
  7. object model. Imagine all your objects lying around in some storage
  8. (like an object database). When writing DQL queries, think about
  9. querying that storage to pick a certain subset of your objects.
  10. .. note::
  11. A common mistake for beginners is to mistake DQL for
  12. being just some form of SQL and therefore trying to use table names
  13. and column names or join arbitrary tables together in a query. You
  14. need to think about DQL as a query language for your object model,
  15. not for your relational schema.
  16. DQL is case in-sensitive, except for namespace, class and field
  17. names, which are case sensitive.
  18. Types of DQL queries
  19. --------------------
  20. DQL as a query language has SELECT, UPDATE and DELETE constructs
  21. that map to their corresponding SQL statement types. INSERT
  22. statements are not allowed in DQL, because entities and their
  23. relations have to be introduced into the persistence context
  24. through ``EntityManager#persist()`` to ensure consistency of your
  25. object model.
  26. DQL SELECT statements are a very powerful way of retrieving parts
  27. of your domain model that are not accessible via associations.
  28. Additionally they allow to retrieve entities and their associations
  29. in one single SQL select statement which can make a huge difference
  30. in performance in contrast to using several queries.
  31. DQL UPDATE and DELETE statements offer a way to execute bulk
  32. changes on the entities of your domain model. This is often
  33. necessary when you cannot load all the affected entities of a bulk
  34. update into memory.
  35. SELECT queries
  36. --------------
  37. DQL SELECT clause
  38. ~~~~~~~~~~~~~~~~~
  39. The select clause of a DQL query specifies what appears in the
  40. query result. The composition of all the expressions in the select
  41. clause also influences the nature of the query result.
  42. Here is an example that selects all users with an age > 20:
  43. .. code-block:: php
  44. <?php
  45. $query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.age > 20');
  46. $users = $query->getResult();
  47. Lets examine the query:
  48. - ``u`` is a so called identification variable or alias that
  49. refers to the ``MyProject\Model\User`` class. By placing this alias
  50. in the SELECT clause we specify that we want all instances of the
  51. User class that are matched by this query to appear in the query
  52. result.
  53. - The FROM keyword is always followed by a fully-qualified class
  54. name which in turn is followed by an identification variable or
  55. alias for that class name. This class designates a root of our
  56. query from which we can navigate further via joins (explained
  57. later) and path expressions.
  58. - The expression ``u.age`` in the WHERE clause is a path
  59. expression. Path expressions in DQL are easily identified by the
  60. use of the '.' operator that is used for constructing paths. The
  61. path expression ``u.age`` refers to the ``age`` field on the User
  62. class.
  63. The result of this query would be a list of User objects where all
  64. users are older than 20.
  65. The SELECT clause allows to specify both class identification
  66. variables that signal the hydration of a complete entity class or
  67. just fields of the entity using the syntax ``u.name``. Combinations
  68. of both are also allowed and it is possible to wrap both fields and
  69. identification values into aggregation and DQL functions. Numerical
  70. fields can be part of computations using mathematical operations.
  71. See the sub-section on `Functions, Operators, Aggregates`_ for
  72. more information.
  73. Joins
  74. ~~~~~
  75. A SELECT query can contain joins. There are 2 types of JOINs:
  76. "Regular" Joins and "Fetch" Joins.
  77. **Regular Joins**: Used to limit the results and/or compute
  78. aggregate values.
  79. **Fetch Joins**: In addition to the uses of regular joins: Used to
  80. fetch related entities and include them in the hydrated result of a
  81. query.
  82. There is no special DQL keyword that distinguishes a regular join
  83. from a fetch join. A join (be it an inner or outer join) becomes a
  84. "fetch join" as soon as fields of the joined entity appear in the
  85. SELECT part of the DQL query outside of an aggregate function.
  86. Otherwise its a "regular join".
  87. Example:
  88. Regular join of the address:
  89. .. code-block:: php
  90. <?php
  91. $query = $em->createQuery("SELECT u FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
  92. $users = $query->getResult();
  93. Fetch join of the address:
  94. .. code-block:: php
  95. <?php
  96. $query = $em->createQuery("SELECT u, a FROM User u JOIN u.address a WHERE a.city = 'Berlin'");
  97. $users = $query->getResult();
  98. When Doctrine hydrates a query with fetch-join it returns the class
  99. in the FROM clause on the root level of the result array. In the
  100. previous example an array of User instances is returned and the
  101. address of each user is fetched and hydrated into the
  102. ``User#address`` variable. If you access the address Doctrine does
  103. not need to lazy load the association with another query.
  104. .. note::
  105. Doctrine allows you to walk all the associations between
  106. all the objects in your domain model. Objects that were not already
  107. loaded from the database are replaced with lazy load proxy
  108. instances. Non-loaded Collections are also replaced by lazy-load
  109. instances that fetch all the contained objects upon first access.
  110. However relying on the lazy-load mechanism leads to many small
  111. queries executed against the database, which can significantly
  112. affect the performance of your application. **Fetch Joins** are the
  113. solution to hydrate most or all of the entities that you need in a
  114. single SELECT query.
  115. Named and Positional Parameters
  116. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  117. DQL supports both named and positional parameters, however in
  118. contrast to many SQL dialects positional parameters are specified
  119. with numbers, for example "?1", "?2" and so on. Named parameters
  120. are specified with ":name1", ":name2" and so on.
  121. When referencing the parameters in ``Query#setParameter($param, $value)``
  122. both named and positional parameters are used **without** their prefixes.
  123. DQL SELECT Examples
  124. ~~~~~~~~~~~~~~~~~~~
  125. This section contains a large set of DQL queries and some
  126. explanations of what is happening. The actual result also depends
  127. on the hydration mode.
  128. Hydrate all User entities:
  129. .. code-block:: php
  130. <?php
  131. $query = $em->createQuery('SELECT u FROM MyProject\Model\User u');
  132. $users = $query->getResult(); // array of User objects
  133. Retrieve the IDs of all CmsUsers:
  134. .. code-block:: php
  135. <?php
  136. $query = $em->createQuery('SELECT u.id FROM CmsUser u');
  137. $ids = $query->getResult(); // array of CmsUser ids
  138. Retrieve the IDs of all users that have written an article:
  139. .. code-block:: php
  140. <?php
  141. $query = $em->createQuery('SELECT DISTINCT u.id FROM CmsArticle a JOIN a.user u');
  142. $ids = $query->getResult(); // array of CmsUser ids
  143. Retrieve all articles and sort them by the name of the articles
  144. users instance:
  145. .. code-block:: php
  146. <?php
  147. $query = $em->createQuery('SELECT a FROM CmsArticle a JOIN a.user u ORDER BY u.name ASC');
  148. $articles = $query->getResult(); // array of CmsArticle objects
  149. Retrieve the Username and Name of a CmsUser:
  150. .. code-block:: php
  151. <?php
  152. $query = $em->createQuery('SELECT u.username, u.name FROM CmsUser u');
  153. $users = $query->getResult(); // array of CmsUser username and name values
  154. echo $users[0]['username'];
  155. Retrieve a ForumUser and his single associated entity:
  156. .. code-block:: php
  157. <?php
  158. $query = $em->createQuery('SELECT u, a FROM ForumUser u JOIN u.avatar a');
  159. $users = $query->getResult(); // array of ForumUser objects with the avatar association loaded
  160. echo get_class($users[0]->getAvatar());
  161. Retrieve a CmsUser and fetch join all the phonenumbers he has:
  162. .. code-block:: php
  163. <?php
  164. $query = $em->createQuery('SELECT u, p FROM CmsUser u JOIN u.phonenumbers p');
  165. $users = $query->getResult(); // array of CmsUser objects with the phonenumbers association loaded
  166. $phonenumbers = $users[0]->getPhonenumbers();
  167. Hydrate a result in Ascending:
  168. .. code-block:: php
  169. <?php
  170. $query = $em->createQuery('SELECT u FROM ForumUser u ORDER BY u.id ASC');
  171. $users = $query->getResult(); // array of ForumUser objects
  172. Or in Descending Order:
  173. .. code-block:: php
  174. <?php
  175. $query = $em->createQuery('SELECT u FROM ForumUser u ORDER BY u.id DESC');
  176. $users = $query->getResult(); // array of ForumUser objects
  177. Using Aggregate Functions:
  178. .. code-block:: php
  179. <?php
  180. $query = $em->createQuery('SELECT COUNT(u.id) FROM Entities\User u');
  181. $count = $query->getSingleScalarResult();
  182. $query = $em->createQuery('SELECT u, count(g.id) FROM Entities\User u JOIN u.groups g GROUP BY u.id');
  183. $result = $query->getResult();
  184. With WHERE Clause and Positional Parameter:
  185. .. code-block:: php
  186. <?php
  187. $query = $em->createQuery('SELECT u FROM ForumUser u WHERE u.id = ?1');
  188. $query->setParameter(1, 321);
  189. $users = $query->getResult(); // array of ForumUser objects
  190. With WHERE Clause and Named Parameter:
  191. .. code-block:: php
  192. <?php
  193. $query = $em->createQuery('SELECT u FROM ForumUser u WHERE u.username = :name');
  194. $query->setParameter('name', 'Bob');
  195. $users = $query->getResult(); // array of ForumUser objects
  196. With Nested Conditions in WHERE Clause:
  197. .. code-block:: php
  198. <?php
  199. $query = $em->createQuery('SELECT u from ForumUser u WHERE (u.username = :name OR u.username = :name2) AND u.id = :id');
  200. $query->setParameters(array(
  201. 'name' => 'Bob',
  202. 'name2' => 'Alice',
  203. 'id' => 321,
  204. ));
  205. $users = $query->getResult(); // array of ForumUser objects
  206. With COUNT DISTINCT:
  207. .. code-block:: php
  208. <?php
  209. $query = $em->createQuery('SELECT COUNT(DISTINCT u.name) FROM CmsUser');
  210. $users = $query->getResult(); // array of ForumUser objects
  211. With Arithmetic Expression in WHERE clause:
  212. .. code-block:: php
  213. <?php
  214. $query = $em->createQuery('SELECT u FROM CmsUser u WHERE ((u.id + 5000) * u.id + 3) < 10000000');
  215. $users = $query->getResult(); // array of ForumUser objects
  216. Using a LEFT JOIN to hydrate all user-ids and optionally associated
  217. article-ids:
  218. .. code-block:: php
  219. <?php
  220. $query = $em->createQuery('SELECT u.id, a.id as article_id FROM CmsUser u LEFT JOIN u.articles a');
  221. $results = $query->getResult(); // array of user ids and every article_id for each user
  222. Restricting a JOIN clause by additional conditions:
  223. .. code-block:: php
  224. <?php
  225. $query = $em->createQuery("SELECT u FROM CmsUser u LEFT JOIN u.articles a WITH a.topic LIKE :foo");
  226. $query->setParameter('foo', '%foo%');
  227. $users = $query->getResult();
  228. Using several Fetch JOINs:
  229. .. code-block:: php
  230. <?php
  231. $query = $em->createQuery('SELECT u, a, p, c FROM CmsUser u JOIN u.articles a JOIN u.phonenumbers p JOIN a.comments c');
  232. $users = $query->getResult();
  233. BETWEEN in WHERE clause:
  234. .. code-block:: php
  235. <?php
  236. $query = $em->createQuery('SELECT u.name FROM CmsUser u WHERE u.id BETWEEN ?1 AND ?2');
  237. $query->setParameter(1, 123);
  238. $query->setParameter(2, 321);
  239. $usernames = $query->getResult();
  240. DQL Functions in WHERE clause:
  241. .. code-block:: php
  242. <?php
  243. $query = $em->createQuery("SELECT u.name FROM CmsUser u WHERE TRIM(u.name) = 'someone'");
  244. $usernames = $query->getResult();
  245. IN() Expression:
  246. .. code-block:: php
  247. <?php
  248. $query = $em->createQuery('SELECT u.name FROM CmsUser u WHERE u.id IN(46)');
  249. $usernames = $query->getResult();
  250. $query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.id IN (1, 2)');
  251. $users = $query->getResult();
  252. $query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.id NOT IN (1)');
  253. $users = $query->getResult();
  254. CONCAT() DQL Function:
  255. .. code-block:: php
  256. <?php
  257. $query = $em->createQuery("SELECT u.id FROM CmsUser u WHERE CONCAT(u.name, 's') = ?1");
  258. $query->setParameter(1, 'Jess');
  259. $ids = $query->getResult();
  260. $query = $em->createQuery('SELECT CONCAT(u.id, u.name) FROM CmsUser u WHERE u.id = ?1');
  261. $query->setParameter(1, 321);
  262. $idUsernames = $query->getResult();
  263. EXISTS in WHERE clause with correlated Subquery
  264. .. code-block:: php
  265. <?php
  266. $query = $em->createQuery('SELECT u.id FROM CmsUser u WHERE EXISTS (SELECT p.phonenumber FROM CmsPhonenumber p WHERE p.user = u.id)');
  267. $ids = $query->getResult();
  268. Get all users who are members of $group.
  269. .. code-block:: php
  270. <?php
  271. $query = $em->createQuery('SELECT u.id FROM CmsUser u WHERE :groupId MEMBER OF u.groups');
  272. $query->setParameter('groupId', $group);
  273. $ids = $query->getResult();
  274. Get all users that have more than 1 phonenumber
  275. .. code-block:: php
  276. <?php
  277. $query = $em->createQuery('SELECT u FROM CmsUser u WHERE SIZE(u.phonenumbers) > 1');
  278. $users = $query->getResult();
  279. Get all users that have no phonenumber
  280. .. code-block:: php
  281. <?php
  282. $query = $em->createQuery('SELECT u FROM CmsUser u WHERE u.phonenumbers IS EMPTY');
  283. $users = $query->getResult();
  284. Get all instances of a specific type, for use with inheritance
  285. hierarchies:
  286. .. versionadded:: 2.1
  287. .. code-block:: php
  288. <?php
  289. $query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF Doctrine\Tests\Models\Company\CompanyEmployee');
  290. $query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u INSTANCE OF ?1');
  291. $query = $em->createQuery('SELECT u FROM Doctrine\Tests\Models\Company\CompanyPerson u WHERE u NOT INSTANCE OF ?1');
  292. Get all users visible on a given website that have chosen certain gender:
  293. .. versionadded:: 2.2
  294. .. code-block:: php
  295. <?php
  296. $query = $em->createQuery('SELECT u FROM User u WHERE u.gender IN (SELECT IDENTITY(agl.gender) FROM Site s JOIN s.activeGenderList agl WHERE s.id = ?1)');
  297. Starting with 2.4, the IDENTITY() DQL function also works for composite primary keys:
  298. .. code-block:: php
  299. <?php
  300. $query = $em->createQuery('SELECT IDENTITY(c.location, 'latitude') AS latitude, IDENTITY(c.location, 'longitude') AS longitude FROM Checkpoint c WHERE c.user = ?1');
  301. Partial Object Syntax
  302. ^^^^^^^^^^^^^^^^^^^^^
  303. By default when you run a DQL query in Doctrine and select only a
  304. subset of the fields for a given entity, you do not receive objects
  305. back. Instead, you receive only arrays as a flat rectangular result
  306. set, similar to how you would if you were just using SQL directly
  307. and joining some data.
  308. If you want to select partial objects you can use the ``partial``
  309. DQL keyword:
  310. .. code-block:: php
  311. <?php
  312. $query = $em->createQuery('SELECT partial u.{id, username} FROM CmsUser u');
  313. $users = $query->getResult(); // array of partially loaded CmsUser objects
  314. You use the partial syntax when joining as well:
  315. .. code-block:: php
  316. <?php
  317. $query = $em->createQuery('SELECT partial u.{id, username}, partial a.{id, name} FROM CmsUser u JOIN u.articles a');
  318. $users = $query->getResult(); // array of partially loaded CmsUser objects
  319. "NEW" Operator Syntax
  320. ^^^^^^^^^^^^^^^^^^^^^
  321. .. versionadded:: 2.4
  322. Using the ``NEW`` operator you can construct Data Transfer Objects (DTOs) directly from DQL queries.
  323. - When using ``SELECT NEW`` you don't need to specify a mapped entity.
  324. - You can specify any PHP class, it's only require that the constructor of this class matches the ``NEW`` statement.
  325. - This approach involves determining exactly which columns you really need,
  326. and instantiating data-transfer object that containing a constructor with those arguments.
  327. If you want to select data-transfer objects you should create a class:
  328. .. code-block:: php
  329. <?php
  330. class CustomerDTO
  331. {
  332. public function __construct($name, $email, $city, $value = null)
  333. {
  334. // Bind values to the object properties.
  335. }
  336. }
  337. And then use the ``NEW`` DQL keyword :
  338. .. code-block:: php
  339. <?php
  340. $query = $em->createQuery('SELECT NEW CustomerDTO(c.name, e.email, a.city) FROM Customer c JOIN c.email e JOIN c.address a');
  341. $users = $query->getResult(); // array of CustomerDTO
  342. .. code-block:: php
  343. <?php
  344. $query = $em->createQuery('SELECT NEW CustomerDTO(c.name, e.email, a.city, SUM(o.value)) FROM Customer c JOIN c.email e JOIN c.address a JOIN c.orders o GROUP BY c');
  345. $users = $query->getResult(); // array of CustomerDTO
  346. Using INDEX BY
  347. ~~~~~~~~~~~~~~
  348. The INDEX BY construct is nothing that directly translates into SQL
  349. but that affects object and array hydration. After each FROM and
  350. JOIN clause you specify by which field this class should be indexed
  351. in the result. By default a result is incremented by numerical keys
  352. starting with 0. However with INDEX BY you can specify any other
  353. column to be the key of your result, it really only makes sense
  354. with primary or unique fields though:
  355. .. code-block:: sql
  356. SELECT u.id, u.status, upper(u.name) nameUpper FROM User u INDEX BY u.id
  357. JOIN u.phonenumbers p INDEX BY p.phonenumber
  358. Returns an array of the following kind, indexed by both user-id
  359. then phonenumber-id:
  360. .. code-block:: php
  361. array
  362. 0 =>
  363. array
  364. 1 =>
  365. object(stdClass)[299]
  366. public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
  367. public 'id' => int 1
  368. ..
  369. 'nameUpper' => string 'ROMANB' (length=6)
  370. 1 =>
  371. array
  372. 2 =>
  373. object(stdClass)[298]
  374. public '__CLASS__' => string 'Doctrine\Tests\Models\CMS\CmsUser' (length=33)
  375. public 'id' => int 2
  376. ...
  377. 'nameUpper' => string 'JWAGE' (length=5)
  378. UPDATE queries
  379. --------------
  380. DQL not only allows to select your Entities using field names, you
  381. can also execute bulk updates on a set of entities using an
  382. DQL-UPDATE query. The Syntax of an UPDATE query works as expected,
  383. as the following example shows:
  384. .. code-block:: sql
  385. UPDATE MyProject\Model\User u SET u.password = 'new' WHERE u.id IN (1, 2, 3)
  386. References to related entities are only possible in the WHERE
  387. clause and using sub-selects.
  388. .. warning::
  389. DQL UPDATE statements are ported directly into a
  390. Database UPDATE statement and therefore bypass any locking scheme, events
  391. and do not increment the version column. Entities that are already
  392. loaded into the persistence context will *NOT* be synced with the
  393. updated database state. It is recommended to call
  394. ``EntityManager#clear()`` and retrieve new instances of any
  395. affected entity.
  396. DELETE queries
  397. --------------
  398. DELETE queries can also be specified using DQL and their syntax is
  399. as simple as the UPDATE syntax:
  400. .. code-block:: sql
  401. DELETE MyProject\Model\User u WHERE u.id = 4
  402. The same restrictions apply for the reference of related entities.
  403. .. warning::
  404. DQL DELETE statements are ported directly into a
  405. Database DELETE statement and therefore bypass any events and checks for the
  406. version column if they are not explicitly added to the WHERE clause
  407. of the query. Additionally Deletes of specifies entities are *NOT*
  408. cascaded to related entities even if specified in the metadata.
  409. Functions, Operators, Aggregates
  410. --------------------------------
  411. DQL Functions
  412. ~~~~~~~~~~~~~
  413. The following functions are supported in SELECT, WHERE and HAVING
  414. clauses:
  415. - IDENTITY(single\_association\_path\_expression [, fieldMapping]) - Retrieve the foreign key column of association of the owning side
  416. - ABS(arithmetic\_expression)
  417. - CONCAT(str1, str2)
  418. - CURRENT\_DATE() - Return the current date
  419. - CURRENT\_TIME() - Returns the current time
  420. - CURRENT\_TIMESTAMP() - Returns a timestamp of the current date
  421. and time.
  422. - LENGTH(str) - Returns the length of the given string
  423. - LOCATE(needle, haystack [, offset]) - Locate the first
  424. occurrence of the substring in the string.
  425. - LOWER(str) - returns the string lowercased.
  426. - MOD(a, b) - Return a MOD b.
  427. - SIZE(collection) - Return the number of elements in the
  428. specified collection
  429. - SQRT(q) - Return the square-root of q.
  430. - SUBSTRING(str, start [, length]) - Return substring of given
  431. string.
  432. - TRIM([LEADING \| TRAILING \| BOTH] ['trchar' FROM] str) - Trim
  433. the string by the given trim char, defaults to whitespaces.
  434. - UPPER(str) - Return the upper-case of the given string.
  435. - DATE_ADD(date, days, unit) - Add the number of days to a given date. (Supported units are DAY, MONTH)
  436. - DATE_SUB(date, days, unit) - Substract the number of days from a given date. (Supported units are DAY, MONTH)
  437. - DATE_DIFF(date1, date2) - Calculate the difference in days between date1-date2.
  438. Arithmetic operators
  439. ~~~~~~~~~~~~~~~~~~~~
  440. You can do math in DQL using numeric values, for example:
  441. .. code-block:: sql
  442. SELECT person.salary * 1.5 FROM CompanyPerson person WHERE person.salary < 100000
  443. Aggregate Functions
  444. ~~~~~~~~~~~~~~~~~~~
  445. The following aggregate functions are allowed in SELECT and GROUP
  446. BY clauses: AVG, COUNT, MIN, MAX, SUM
  447. Other Expressions
  448. ~~~~~~~~~~~~~~~~~
  449. DQL offers a wide-range of additional expressions that are known
  450. from SQL, here is a list of all the supported constructs:
  451. - ``ALL/ANY/SOME`` - Used in a WHERE clause followed by a
  452. sub-select this works like the equivalent constructs in SQL.
  453. - ``BETWEEN a AND b`` and ``NOT BETWEEN a AND b`` can be used to
  454. match ranges of arithmetic values.
  455. - ``IN (x1, x2, ...)`` and ``NOT IN (x1, x2, ..)`` can be used to
  456. match a set of given values.
  457. - ``LIKE ..`` and ``NOT LIKE ..`` match parts of a string or text
  458. using % as a wildcard.
  459. - ``IS NULL`` and ``IS NOT NULL`` to check for null values
  460. - ``EXISTS`` and ``NOT EXISTS`` in combination with a sub-select
  461. Adding your own functions to the DQL language
  462. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  463. By default DQL comes with functions that are part of a large basis
  464. of underlying databases. However you will most likely choose a
  465. database platform at the beginning of your project and most likely
  466. never change it. For this cases you can easily extend the DQL
  467. parser with own specialized platform functions.
  468. You can register custom DQL functions in your ORM Configuration:
  469. .. code-block:: php
  470. <?php
  471. $config = new \Doctrine\ORM\Configuration();
  472. $config->addCustomStringFunction($name, $class);
  473. $config->addCustomNumericFunction($name, $class);
  474. $config->addCustomDatetimeFunction($name, $class);
  475. $em = EntityManager::create($dbParams, $config);
  476. The functions have to return either a string, numeric or datetime
  477. value depending on the registered function type. As an example we
  478. will add a MySQL specific FLOOR() functionality. All the given
  479. classes have to implement the base class :
  480. .. code-block:: php
  481. <?php
  482. namespace MyProject\Query\AST;
  483. use \Doctrine\ORM\Query\AST\Functions\FunctionNode;
  484. use \Doctrine\ORM\Query\Lexer;
  485. class MysqlFloor extends FunctionNode
  486. {
  487. public $simpleArithmeticExpression;
  488. public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
  489. {
  490. return 'FLOOR(' . $sqlWalker->walkSimpleArithmeticExpression(
  491. $this->simpleArithmeticExpression
  492. ) . ')';
  493. }
  494. public function parse(\Doctrine\ORM\Query\Parser $parser)
  495. {
  496. $lexer = $parser->getLexer();
  497. $parser->match(Lexer::T_IDENTIFIER);
  498. $parser->match(Lexer::T_OPEN_PARENTHESIS);
  499. $this->simpleArithmeticExpression = $parser->SimpleArithmeticExpression();
  500. $parser->match(Lexer::T_CLOSE_PARENTHESIS);
  501. }
  502. }
  503. We will register the function by calling and can then use it:
  504. .. code-block:: php
  505. <?php
  506. $config = $em->getConfiguration();
  507. $config->registerNumericFunction('FLOOR', 'MyProject\Query\MysqlFloor');
  508. $dql = "SELECT FLOOR(person.salary * 1.75) FROM CompanyPerson person";
  509. Querying Inherited Classes
  510. --------------------------
  511. This section demonstrates how you can query inherited classes and
  512. what type of results to expect.
  513. Single Table
  514. ~~~~~~~~~~~~
  515. `Single Table Inheritance <http://martinfowler.com/eaaCatalog/singleTableInheritance.html>`_
  516. is an inheritance mapping strategy where all classes of a hierarchy
  517. are mapped to a single database table. In order to distinguish
  518. which row represents which type in the hierarchy a so-called
  519. discriminator column is used.
  520. First we need to setup an example set of entities to use. In this
  521. scenario it is a generic Person and Employee example:
  522. .. code-block:: php
  523. <?php
  524. namespace Entities;
  525. /**
  526. * @Entity
  527. * @InheritanceType("SINGLE_TABLE")
  528. * @DiscriminatorColumn(name="discr", type="string")
  529. * @DiscriminatorMap({"person" = "Person", "employee" = "Employee"})
  530. */
  531. class Person
  532. {
  533. /**
  534. * @Id @Column(type="integer")
  535. * @GeneratedValue
  536. */
  537. protected $id;
  538. /**
  539. * @Column(type="string", length=50)
  540. */
  541. protected $name;
  542. // ...
  543. }
  544. /**
  545. * @Entity
  546. */
  547. class Employee extends Person
  548. {
  549. /**
  550. * @Column(type="string", length=50)
  551. */
  552. private $department;
  553. // ...
  554. }
  555. First notice that the generated SQL to create the tables for these
  556. entities looks like the following:
  557. .. code-block:: sql
  558. CREATE TABLE Person (
  559. id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
  560. name VARCHAR(50) NOT NULL,
  561. discr VARCHAR(255) NOT NULL,
  562. department VARCHAR(50) NOT NULL
  563. )
  564. Now when persist a new ``Employee`` instance it will set the
  565. discriminator value for us automatically:
  566. .. code-block:: php
  567. <?php
  568. $employee = new \Entities\Employee();
  569. $employee->setName('test');
  570. $employee->setDepartment('testing');
  571. $em->persist($employee);
  572. $em->flush();
  573. Now lets run a simple query to retrieve the ``Employee`` we just
  574. created:
  575. .. code-block:: sql
  576. SELECT e FROM Entities\Employee e WHERE e.name = 'test'
  577. If we check the generated SQL you will notice it has some special
  578. conditions added to ensure that we will only get back ``Employee``
  579. entities:
  580. .. code-block:: sql
  581. SELECT p0_.id AS id0, p0_.name AS name1, p0_.department AS department2,
  582. p0_.discr AS discr3 FROM Person p0_
  583. WHERE (p0_.name = ?) AND p0_.discr IN ('employee')
  584. Class Table Inheritance
  585. ~~~~~~~~~~~~~~~~~~~~~~~
  586. `Class Table Inheritance <http://martinfowler.com/eaaCatalog/classTableInheritance.html>`_
  587. is an inheritance mapping strategy where each class in a hierarchy
  588. is mapped to several tables: its own table and the tables of all
  589. parent classes. The table of a child class is linked to the table
  590. of a parent class through a foreign key constraint. Doctrine 2
  591. implements this strategy through the use of a discriminator column
  592. in the topmost table of the hierarchy because this is the easiest
  593. way to achieve polymorphic queries with Class Table Inheritance.
  594. The example for class table inheritance is the same as single
  595. table, you just need to change the inheritance type from
  596. ``SINGLE_TABLE`` to ``JOINED``:
  597. .. code-block:: php
  598. <?php
  599. /**
  600. * @Entity
  601. * @InheritanceType("JOINED")
  602. * @DiscriminatorColumn(name="discr", type="string")
  603. * @DiscriminatorMap({"person" = "Person", "employee" = "Employee"})
  604. */
  605. class Person
  606. {
  607. // ...
  608. }
  609. Now take a look at the SQL which is generated to create the table,
  610. you'll notice some differences:
  611. .. code-block:: sql
  612. CREATE TABLE Person (
  613. id INT AUTO_INCREMENT NOT NULL,
  614. name VARCHAR(50) NOT NULL,
  615. discr VARCHAR(255) NOT NULL,
  616. PRIMARY KEY(id)
  617. ) ENGINE = InnoDB;
  618. CREATE TABLE Employee (
  619. id INT NOT NULL,
  620. department VARCHAR(50) NOT NULL,
  621. PRIMARY KEY(id)
  622. ) ENGINE = InnoDB;
  623. ALTER TABLE Employee ADD FOREIGN KEY (id) REFERENCES Person(id) ON DELETE CASCADE
  624. - The data is split between two tables
  625. - A foreign key exists between the two tables
  626. Now if were to insert the same ``Employee`` as we did in the
  627. ``SINGLE_TABLE`` example and run the same example query it will
  628. generate different SQL joining the ``Person`` information
  629. automatically for you:
  630. .. code-block:: sql
  631. SELECT p0_.id AS id0, p0_.name AS name1, e1_.department AS department2,
  632. p0_.discr AS discr3
  633. FROM Employee e1_ INNER JOIN Person p0_ ON e1_.id = p0_.id
  634. WHERE p0_.name = ?
  635. The Query class
  636. ---------------
  637. An instance of the ``Doctrine\ORM\Query`` class represents a DQL
  638. query. You create a Query instance be calling
  639. ``EntityManager#createQuery($dql)``, passing the DQL query string.
  640. Alternatively you can create an empty ``Query`` instance and invoke
  641. ``Query#setDql($dql)`` afterwards. Here are some examples:
  642. .. code-block:: php
  643. <?php
  644. // $em instanceof EntityManager
  645. // example1: passing a DQL string
  646. $q = $em->createQuery('select u from MyProject\Model\User u');
  647. // example2: using setDql
  648. $q = $em->createQuery();
  649. $q->setDql('select u from MyProject\Model\User u');
  650. Query Result Formats
  651. ~~~~~~~~~~~~~~~~~~~~
  652. The format in which the result of a DQL SELECT query is returned
  653. can be influenced by a so-called ``hydration mode``. A hydration
  654. mode specifies a particular way in which a SQL result set is
  655. transformed. Each hydration mode has its own dedicated method on
  656. the Query class. Here they are:
  657. - ``Query#getResult()``: Retrieves a collection of objects. The
  658. result is either a plain collection of objects (pure) or an array
  659. where the objects are nested in the result rows (mixed).
  660. - ``Query#getSingleResult()``: Retrieves a single object. If the
  661. result contains more than one or no object, an exception is thrown. The
  662. pure/mixed distinction does not apply.
  663. - ``Query#getOneOrNullResult()``: Retrieve a single object. If no
  664. object is found null will be returned.
  665. - ``Query#getArrayResult()``: Retrieves an array graph (a nested
  666. array) that is largely interchangeable with the object graph
  667. generated by ``Query#getResult()`` for read-only purposes.
  668. .. note::
  669. An array graph can differ from the corresponding object
  670. graph in certain scenarios due to the difference of the identity
  671. semantics between arrays and objects.
  672. - ``Query#getScalarResult()``: Retrieves a flat/rectangular result
  673. set of scalar values that can contain duplicate data. The
  674. pure/mixed distinction does not apply.
  675. - ``Query#getSingleScalarResult()``: Retrieves a single scalar
  676. value from the result returned by the dbms. If the result contains
  677. more than a single scalar value, an exception is thrown. The
  678. pure/mixed distinction does not apply.
  679. Instead of using these methods, you can alternatively use the
  680. general-purpose method
  681. ``Query#execute(array $params = array(), $hydrationMode = Query::HYDRATE_OBJECT)``.
  682. Using this method you can directly supply the hydration mode as the
  683. second parameter via one of the Query constants. In fact, the
  684. methods mentioned earlier are just convenient shortcuts for the
  685. execute method. For example, the method ``Query#getResult()``
  686. internally invokes execute, passing in ``Query::HYDRATE_OBJECT`` as
  687. the hydration mode.
  688. The use of the methods mentioned earlier is generally preferred as
  689. it leads to more concise code.
  690. Pure and Mixed Results
  691. ~~~~~~~~~~~~~~~~~~~~~~
  692. The nature of a result returned by a DQL SELECT query retrieved
  693. through ``Query#getResult()`` or ``Query#getArrayResult()`` can be
  694. of 2 forms: **pure** and **mixed**. In the previous simple
  695. examples, you already saw a "pure" query result, with only objects.
  696. By default, the result type is **pure** but
  697. **as soon as scalar values, such as aggregate values or other scalar values that do not belong to an entity, appear in the SELECT part of the DQL query, the result becomes mixed**.
  698. A mixed result has a different structure than a pure result in
  699. order to accommodate for the scalar values.
  700. A pure result usually looks like this:
  701. .. code-block:: php
  702. $dql = "SELECT u FROM User u";
  703. array
  704. [0] => Object
  705. [1] => Object
  706. [2] => Object
  707. ...
  708. A mixed result on the other hand has the following general
  709. structure:
  710. .. code-block:: php
  711. $dql = "SELECT u, 'some scalar string', count(u.groups) AS num FROM User u JOIN u.groups g GROUP BY u.id";
  712. array
  713. [0]
  714. [0] => Object
  715. [1] => "some scalar string"
  716. ['num'] => 42
  717. // ... more scalar values, either indexed numerically or with a name
  718. [1]
  719. [0] => Object
  720. [1] => "some scalar string"
  721. ['num'] => 42
  722. // ... more scalar values, either indexed numerically or with a name
  723. To better understand mixed results, consider the following DQL
  724. query:
  725. .. code-block:: sql
  726. SELECT u, UPPER(u.name) nameUpper FROM MyProject\Model\User u
  727. This query makes use of the ``UPPER`` DQL function that returns a
  728. scalar value and because there is now a scalar value in the SELECT
  729. clause, we get a mixed result.
  730. Conventions for mixed results are as follows:
  731. - The object fetched in the FROM clause is always positioned with the key '0'.
  732. - Every scalar without a name is numbered in the order given in the query, starting with 1.
  733. - Every aliased scalar is given with its alias-name as the key. The case of the name is kept.
  734. - If several objects are fetched from the FROM clause they alternate every row.
  735. Here is how the result could look like:
  736. .. code-block:: php
  737. array
  738. array
  739. [0] => User (Object)
  740. ['nameUpper'] => "ROMAN"
  741. array
  742. [0] => User (Object)
  743. ['nameUpper'] => "JONATHAN"
  744. ...
  745. And here is how you would access it in PHP code:
  746. .. code-block:: php
  747. <?php
  748. foreach ($results as $row) {
  749. echo "Name: " . $row[0]->getName();
  750. echo "Name UPPER: " . $row['nameUpper'];
  751. }
  752. Fetching Multiple FROM Entities
  753. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  754. If you fetch multiple entities that are listed in the FROM clause then the hydration
  755. will return the rows iterating the different top-level entities.
  756. .. code-block:: php
  757. $dql = "SELECT u, g FROM User u, Group g";
  758. array
  759. [0] => Object (User)
  760. [1] => Object (Group)
  761. [2] => Object (User)
  762. [3] => Object (Group)
  763. Hydration Modes
  764. ~~~~~~~~~~~~~~~
  765. Each of the Hydration Modes makes assumptions about how the result
  766. is returned to user land. You should know about all the details to
  767. make best use of the different result formats:
  768. The constants for the different hydration modes are:
  769. - Query::HYDRATE\_OBJECT
  770. - Query::HYDRATE\_ARRAY
  771. - Query::HYDRATE\_SCALAR
  772. - Query::HYDRATE\_SINGLE\_SCALAR
  773. Object Hydration
  774. ^^^^^^^^^^^^^^^^
  775. Object hydration hydrates the result set into the object graph:
  776. .. code-block:: php
  777. <?php
  778. $query = $em->createQuery('SELECT u FROM CmsUser u');
  779. $users = $query->getResult(Query::HYDRATE_OBJECT);
  780. Array Hydration
  781. ^^^^^^^^^^^^^^^
  782. You can run the same query with array hydration and the result set
  783. is hydrated into an array that represents the object graph:
  784. .. code-block:: php
  785. <?php
  786. $query = $em->createQuery('SELECT u FROM CmsUser u');
  787. $users = $query->getResult(Query::HYDRATE_ARRAY);
  788. You can use the ``getArrayResult()`` shortcut as well:
  789. .. code-block:: php
  790. <?php
  791. $users = $query->getArrayResult();
  792. Scalar Hydration
  793. ^^^^^^^^^^^^^^^^
  794. If you want to return a flat rectangular result set instead of an
  795. object graph you can use scalar hydration:
  796. .. code-block:: php
  797. <?php
  798. $query = $em->createQuery('SELECT u FROM CmsUser u');
  799. $users = $query->getResult(Query::HYDRATE_SCALAR);
  800. echo $users[0]['u_id'];
  801. The following assumptions are made about selected fields using
  802. Scalar Hydration:
  803. 1. Fields from classes are prefixed by the DQL alias in the result.
  804. A query of the kind 'SELECT u.name ..' returns a key 'u\_name' in
  805. the result rows.
  806. Single Scalar Hydration
  807. ^^^^^^^^^^^^^^^^^^^^^^^
  808. If you a query which returns just a single scalar value you can use
  809. single scalar hydration:
  810. .. code-block:: php
  811. <?php
  812. $query = $em->createQuery('SELECT COUNT(a.id) FROM CmsUser u LEFT JOIN u.articles a WHERE u.username = ?1 GROUP BY u.id');
  813. $query->setParameter(1, 'jwage');
  814. $numArticles = $query->getResult(Query::HYDRATE_SINGLE_SCALAR);
  815. You can use the ``getSingleScalarResult()`` shortcut as well:
  816. .. code-block:: php
  817. <?php
  818. $numArticles = $query->getSingleScalarResult();
  819. Custom Hydration Modes
  820. ^^^^^^^^^^^^^^^^^^^^^^
  821. You can easily add your own custom hydration modes by first
  822. creating a class which extends ``AbstractHydrator``:
  823. .. code-block:: php
  824. <?php
  825. namespace MyProject\Hydrators;
  826. use Doctrine\ORM\Internal\Hydration\AbstractHydrator;
  827. class CustomHydrator extends AbstractHydrator
  828. {
  829. protected function _hydrateAll()
  830. {
  831. return $this->_stmt->fetchAll(PDO::FETCH_ASSOC);
  832. }
  833. }
  834. Next you just need to add the class to the ORM configuration:
  835. .. code-block:: php
  836. <?php
  837. $em->getConfiguration()->addCustomHydrationMode('CustomHydrator', 'MyProject\Hydrators\CustomHydrator');
  838. Now the hydrator is ready to be used in your queries:
  839. .. code-block:: php
  840. <?php
  841. $query = $em->createQuery('SELECT u FROM CmsUser u');
  842. $results = $query->getResult('CustomHydrator');
  843. Iterating Large Result Sets
  844. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  845. There are situations when a query you want to execute returns a
  846. very large result-set that needs to be processed. All the
  847. previously described hydration modes completely load a result-set
  848. into memory which might not be feasible with large result sets. See
  849. the `Batch Processing <batch-processing>`_ section on details how
  850. to iterate large result sets.
  851. Functions
  852. ~~~~~~~~~
  853. The following methods exist on the ``AbstractQuery`` which both
  854. ``Query`` and ``NativeQuery`` extend from.
  855. Parameters
  856. ^^^^^^^^^^
  857. Prepared Statements that use numerical or named wildcards require
  858. additional parameters to be executable against the database. To
  859. pass parameters to the query the following methods can be used:
  860. - ``AbstractQuery::setParameter($param, $value)`` - Set the
  861. numerical or named wildcard to the given value.
  862. - ``AbstractQuery::setParameters(array $params)`` - Set an array
  863. of parameter key-value pairs.
  864. - ``AbstractQuery::getParameter($param)``
  865. - ``AbstractQuery::getParameters()``
  866. Both named and positional parameters are passed to these methods without their ? or : prefix.
  867. Cache related API
  868. ^^^^^^^^^^^^^^^^^
  869. You can cache query results based either on all variables that
  870. define the result (SQL, Hydration Mode, Parameters and Hints) or on
  871. user-defined cache keys. However by default query results are not
  872. cached at all. You have to enable the result cache on a per query
  873. basis. The following example shows a complete workflow using the
  874. Result Cache API:
  875. .. code-block:: php
  876. <?php
  877. $query = $em->createQuery('SELECT u FROM MyProject\Model\User u WHERE u.id = ?1');
  878. $query->setParameter(1, 12);
  879. $query->setResultCacheDriver(new ApcCache());
  880. $query->useResultCache(true)
  881. ->setResultCacheLifeTime($seconds = 3600);
  882. $result = $query->getResult(); // cache miss
  883. $query->expireResultCache(true);
  884. $result = $query->getResult(); // forced expire, cache miss
  885. $query->setResultCacheId('my_query_result');
  886. $result = $query->getResult(); // saved in given result cache id.
  887. // or call useResultCache() with all parameters:
  888. $query->useResultCache(true, $seconds = 3600, 'my_query_result');
  889. $result = $query->getResult(); // cache hit!
  890. // Introspection
  891. $queryCacheProfile = $query->getQueryCacheProfile();
  892. $cacheDriver = $query->getResultCacheDriver();
  893. $lifetime = $query->getLifetime();
  894. $key = $query->getCacheKey();
  895. .. note::
  896. You can set the Result Cache Driver globally on the
  897. ``Doctrine\ORM\Configuration`` instance so that it is passed to
  898. every ``Query`` and ``NativeQuery`` instance.
  899. Query Hints
  900. ^^^^^^^^^^^
  901. You can pass hints to the query parser and hydrators by using the
  902. ``AbstractQuery::setHint($name, $value)`` method. Currently there
  903. exist mostly internal query hints that are not be consumed in
  904. userland. However the following few hints are to be used in
  905. userland:
  906. - Query::HINT\_FORCE\_PARTIAL\_LOAD - Allows to hydrate objects
  907. although not all their columns are fetched. This query hint can be
  908. used to handle memory consumption problems with large result-sets
  909. that contain char or binary data. Doctrine has no way of implicitly
  910. reloading this data. Partially loaded objects have to be passed to
  911. ``EntityManager::refresh()`` if they are to be reloaded fully from
  912. the database.
  913. - Query::HINT\_REFRESH - This query is used internally by
  914. ``EntityManager::refresh()`` and can be used in userland as well.
  915. If you specify this hint and a query returns the data for an entity
  916. that is already managed by the UnitOfWork, the fields of the
  917. existing entity will be refreshed. In normal operation a result-set
  918. that loads data of an already existing entity is discarded in favor
  919. of the already existing entity.
  920. - Query::HINT\_CUSTOM\_TREE\_WALKERS - An array of additional
  921. ``Doctrine\ORM\Query\TreeWalker`` instances that are attached to
  922. the DQL query parsing process.
  923. Query Cache (DQL Query Only)
  924. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  925. Parsing a DQL query and converting it into a SQL query against the
  926. underlying database platform obviously has some overhead in
  927. contrast to directly executing Native SQL queries. That is why
  928. there is a dedicated Query Cache for caching the DQL parser
  929. results. In combination with the use of wildcards you can reduce
  930. the number of parsed queries in production to zero.
  931. The Query Cache Driver is passed from the
  932. ``Doctrine\ORM\Configuration`` instance to each
  933. ``Doctrine\ORM\Query`` instance by default and is also enabled by
  934. default. This also means you don't regularly need to fiddle with
  935. the parameters of the Query Cache, however if you do there are
  936. several methods to interact with it:
  937. - ``Query::setQueryCacheDriver($driver)`` - Allows to set a Cache
  938. instance
  939. - ``Query::setQueryCacheLifeTime($seconds = 3600)`` - Set lifetime
  940. of the query caching.
  941. - ``Query::expireQueryCache($bool)`` - Enforce the expiring of the
  942. query cache if set to true.
  943. - ``Query::getExpireQueryCache()``
  944. - ``Query::getQueryCacheDriver()``
  945. - ``Query::getQueryCacheLifeTime()``
  946. First and Max Result Items (DQL Query Only)
  947. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  948. You can limit the number of results returned from a DQL query as
  949. well as specify the starting offset, Doctrine then uses a strategy
  950. of manipulating the select query to return only the requested
  951. number of results:
  952. - ``Query::setMaxResults($maxResults)``
  953. - ``Query::setFirstResult($offset)``
  954. .. note::
  955. If your query contains a fetch-joined collection
  956. specifying the result limit methods are not working as you would
  957. expect. Set Max Results restricts the number of database result
  958. rows, however in the case of fetch-joined collections one root
  959. entity might appear in many rows, effectively hydrating less than
  960. the specified number of results.
  961. .. _dql-temporarily-change-fetch-mode:
  962. Temporarily change fetch mode in DQL
  963. ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  964. While normally all your associations are marked as lazy or extra lazy you will have cases where you are using DQL and don't want to
  965. fetch join a second, third or fourth level of entities into your result, because of the increased cost of the SQL JOIN. You
  966. can mark a many-to-one or one-to-one association as fetched temporarily to batch fetch these entities using a WHERE .. IN query.
  967. .. code-block:: php
  968. <?php
  969. $query = $em->createQuery("SELECT u FROM MyProject\User u");
  970. $query->setFetchMode("MyProject\User", "address", "EAGER");
  971. $query->execute();
  972. Given that there are 10 users and corresponding addresses in the database the executed queries will look something like:
  973. .. code-block:: sql
  974. SELECT * FROM users;
  975. SELECT * FROM address WHERE id IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);
  976. EBNF
  977. ----
  978. The following context-free grammar, written in an EBNF variant,
  979. describes the Doctrine Query Language. You can consult this grammar
  980. whenever you are unsure about what is possible with DQL or what the
  981. correct syntax for a particular query should be.
  982. Document syntax:
  983. ~~~~~~~~~~~~~~~~
  984. - non-terminals begin with an upper case character
  985. - terminals begin with a lower case character
  986. - parentheses (...) are used for grouping
  987. - square brackets [...] are used for defining an optional part,
  988. e.g. zero or one time
  989. - curly brackets {...} are used for repetition, e.g. zero or more
  990. times
  991. - double quotation marks "..." define a terminal string a vertical
  992. bar \| represents an alternative
  993. Terminals
  994. ~~~~~~~~~
  995. - identifier (name, email, ...)
  996. - string ('foo', 'bar''s house', '%ninja%', ...)
  997. - char ('/', '\\', ' ', ...)
  998. - integer (-1, 0, 1, 34, ...)
  999. - float (-0.23, 0.007, 1.245342E+8, ...)
  1000. - boolean (false, true)
  1001. Query Language
  1002. ~~~~~~~~~~~~~~
  1003. .. code-block:: php
  1004. QueryLanguage ::= SelectStatement | UpdateStatement | DeleteStatement
  1005. Statements
  1006. ~~~~~~~~~~
  1007. .. code-block:: php
  1008. SelectStatement ::= SelectClause FromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause]
  1009. UpdateStatement ::= UpdateClause [WhereClause]
  1010. DeleteStatement ::= DeleteClause [WhereClause]
  1011. Identifiers
  1012. ~~~~~~~~~~~
  1013. .. code-block:: php
  1014. /* Alias Identification usage (the "u" of "u.name") */
  1015. IdentificationVariable ::= identifier
  1016. /* Alias Identification declaration (the "u" of "FROM User u") */
  1017. AliasIdentificationVariable :: = identifier
  1018. /* identifier that must be a class name (the "User" of "FROM User u") */
  1019. AbstractSchemaName ::= identifier
  1020. /* identifier that must be a field (the "name" of "u.name") */
  1021. /* This is responsible to know if the field exists in Object, no matter if it's a relation or a simple field */
  1022. FieldIdentificationVariable ::= identifier
  1023. /* identifier that must be a collection-valued association field (to-many) (the "Phonenumbers" of "u.Phonenumbers") */
  1024. CollectionValuedAssociationField ::= FieldIdentificationVariable
  1025. /* identifier that must be a single-valued association field (to-one) (the "Group" of "u.Group") */
  1026. SingleValuedAssociationField ::= FieldIdentificationVariable
  1027. /* identifier that must be an embedded class state field (for the future) */
  1028. EmbeddedClassStateField ::= FieldIdentificationVariable
  1029. /* identifier that must be a simple state field (name, email, ...) (the "name" of "u.name") */
  1030. /* The difference between this and FieldIdentificationVariable is only semantical, because it points to a single field (not mapping to a relation) */
  1031. SimpleStateField ::= FieldIdentificationVariable
  1032. /* Alias ResultVariable declaration (the "total" of "COUNT(*) AS total") */
  1033. AliasResultVariable = identifier
  1034. /* ResultVariable identifier usage of mapped field aliases (the "total" of "COUNT(*) AS total") */
  1035. ResultVariable = identifier
  1036. Path Expressions
  1037. ~~~~~~~~~~~~~~~~
  1038. .. code-block:: php
  1039. /* "u.Group" or "u.Phonenumbers" declarations */
  1040. JoinAssociationPathExpression ::= IdentificationVariable "." (CollectionValuedAssociationField | SingleValuedAssociationField)
  1041. /* "u.Group" or "u.Phonenumbers" usages */
  1042. AssociationPathExpression ::= CollectionValuedPathExpression | SingleValuedAssociationPathExpression
  1043. /* "u.name" or "u.Group" */
  1044. SingleValuedPathExpression ::= StateFieldPathExpression | SingleValuedAssociationPathExpression
  1045. /* "u.name" or "u.Group.name" */
  1046. StateFieldPathExpression ::= IdentificationVariable "." StateField
  1047. /* "u.Group" */
  1048. SingleValuedAssociationPathExpression ::= IdentificationVariable "." SingleValuedAssociationField
  1049. /* "u.Group.Permissions" */
  1050. CollectionValuedPathExpression ::= IdentificationVariable "." CollectionValuedAssociationField
  1051. /* "name" */
  1052. StateField ::= {EmbeddedClassStateField "."}* SimpleStateField
  1053. Clauses
  1054. ~~~~~~~
  1055. .. code-block:: php
  1056. SelectClause ::= "SELECT" ["DISTINCT"] SelectExpression {"," SelectExpression}*
  1057. SimpleSelectClause ::= "SELECT" ["DISTINCT"] SimpleSelectExpression
  1058. UpdateClause ::= "UPDATE" AbstractSchemaName ["AS"] AliasIdentificationVariable "SET" UpdateItem {"," UpdateItem}*
  1059. DeleteClause ::= "DELETE" ["FROM"] AbstractSchemaName ["AS"] AliasIdentificationVariable
  1060. FromClause ::= "FROM" IdentificationVariableDeclaration {"," IdentificationVariableDeclaration}*
  1061. SubselectFromClause ::= "FROM" SubselectIdentificationVariableDeclaration {"," SubselectIdentificationVariableDeclaration}*
  1062. WhereClause ::= "WHERE" ConditionalExpression
  1063. HavingClause ::= "HAVING" ConditionalExpression
  1064. GroupByClause ::= "GROUP" "BY" GroupByItem {"," GroupByItem}*
  1065. OrderByClause ::= "ORDER" "BY" OrderByItem {"," OrderByItem}*
  1066. Subselect ::= SimpleSelectClause SubselectFromClause [WhereClause] [GroupByClause] [HavingClause] [OrderByClause]
  1067. Items
  1068. ~~~~~
  1069. .. code-block:: php
  1070. UpdateItem ::= SingleValuedPathExpression "=" NewValue
  1071. OrderByItem ::= (SimpleArithmeticExpression | SingleValuedPathExpression | ScalarExpression | ResultVariable) ["ASC" | "DESC"]
  1072. GroupByItem ::= IdentificationVariable | ResultVariable | SingleValuedPathExpression
  1073. NewValue ::= SimpleArithmeticExpression | "NULL"
  1074. From, Join and Index by
  1075. ~~~~~~~~~~~~~~~~~~~~~~~
  1076. .. code-block:: php
  1077. IdentificationVariableDeclaration ::= RangeVariableDeclaration [IndexBy] {JoinVariableDeclaration}*
  1078. SubselectIdentificationVariableDeclaration ::= IdentificationVariableDeclaration | (AssociationPathExpression ["AS"] AliasIdentificationVariable)
  1079. JoinVariableDeclaration ::= Join [IndexBy]
  1080. RangeVariableDeclaration ::= AbstractSchemaName ["AS"] AliasIdentificationVariable
  1081. Join ::= ["LEFT" ["OUTER"] | "INNER"] "JOIN" JoinAssociationPathExpression ["AS"] AliasIdentificationVariable ["WITH" ConditionalExpression]
  1082. IndexBy ::= "INDEX" "BY" StateFieldPathExpression
  1083. Select Expressions
  1084. ~~~~~~~~~~~~~~~~~~
  1085. .. code-block:: php
  1086. SelectExpression ::= (IdentificationVariable | ScalarExpression | AggregateExpression | FunctionDeclaration | PartialObjectExpression | "(" Subselect ")" | CaseExpression) [["AS"] ["HIDDEN"] AliasResultVariable]
  1087. SimpleSelectExpression ::= (StateFieldPathExpression | IdentificationVariable | FunctionDeclaration | AggregateExpression | "(" Subselect ")" | ScalarExpression) [["AS"] AliasResultVariable]
  1088. PartialObjectExpression ::= "PARTIAL" IdentificationVariable "." PartialFieldSet
  1089. PartialFieldSet ::= "{" SimpleStateField {"," SimpleStateField}* "}"
  1090. Conditional Expressions
  1091. ~~~~~~~~~~~~~~~~~~~~~~~
  1092. .. code-block:: php
  1093. ConditionalExpression ::= ConditionalTerm {"OR" ConditionalTerm}*
  1094. ConditionalTerm ::= ConditionalFactor {"AND" ConditionalFactor}*
  1095. ConditionalFactor ::= ["NOT"] ConditionalPrimary
  1096. ConditionalPrimary ::= SimpleConditionalExpression | "(" ConditionalExpression ")"
  1097. SimpleConditionalExpression ::= ComparisonExpression | BetweenExpression | LikeExpression |
  1098. InExpression | NullComparisonExpression | ExistsExpression |
  1099. EmptyCollectionComparisonExpression | CollectionMemberExpression |
  1100. InstanceOfExpression
  1101. Collection Expressions
  1102. ~~~~~~~~~~~~~~~~~~~~~~
  1103. .. code-block:: php
  1104. EmptyCollectionComparisonExpression ::= CollectionValuedPathExpression "IS" ["NOT"] "EMPTY"
  1105. CollectionMemberExpression ::= EntityExpression ["NOT"] "MEMBER" ["OF"] CollectionValuedPathExpression
  1106. Literal Values
  1107. ~~~~~~~~~~~~~~
  1108. .. code-block:: php
  1109. Literal ::= string | char | integer | float | boolean
  1110. InParameter ::= Literal | InputParameter
  1111. Input Parameter
  1112. ~~~~~~~~~~~~~~~
  1113. .. code-block:: php
  1114. InputParameter ::= PositionalParameter | NamedParameter
  1115. PositionalParameter ::= "?" integer
  1116. NamedParameter ::= ":" string
  1117. Arithmetic Expressions
  1118. ~~~~~~~~~~~~~~~~~~~~~~
  1119. .. code-block:: php
  1120. ArithmeticExpression ::= SimpleArithmeticExpression | "(" Subselect ")"
  1121. SimpleArithmeticExpression ::= ArithmeticTerm {("+" | "-") ArithmeticTerm}*
  1122. ArithmeticTerm ::= ArithmeticFactor {("*" | "/") ArithmeticFactor}*
  1123. ArithmeticFactor ::= [("+" | "-")] ArithmeticPrimary
  1124. ArithmeticPrimary ::= SingleValuedPathExpression | Literal | "(" SimpleArithmeticExpression ")"
  1125. | FunctionsReturningNumerics | AggregateExpression | FunctionsReturningStrings
  1126. | FunctionsReturningDatetime | IdentificationVariable | ResultVariable
  1127. | InputParameter | CaseExpression
  1128. Scalar and Type Expressions
  1129. ~~~~~~~~~~~~~~~~~~~~~~~~~~~
  1130. .. code-block:: php
  1131. ScalarExpression ::= SimpleArithmeticExpression | StringPrimary | DateTimePrimary | StateFieldPathExpression | BooleanPrimary | CaseExpression | InstanceOfExpression
  1132. StringExpression ::= StringPrimary | "(" Subselect ")"
  1133. StringPrimary ::= StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression | CaseExpression
  1134. BooleanExpression ::= BooleanPrimary | "(" Subselect ")"
  1135. BooleanPrimary ::= StateFieldPathExpression | boolean | InputParameter
  1136. EntityExpression ::= SingleValuedAssociationPathExpression | SimpleEntityExpression
  1137. SimpleEntityExpression ::= IdentificationVariable | InputParameter
  1138. DatetimeExpression ::= DatetimePrimary | "(" Subselect ")"
  1139. DatetimePrimary ::= StateFieldPathExpression | InputParameter | FunctionsReturningDatetime | AggregateExpression
  1140. .. note::
  1141. Parts of CASE expressions are not yet implemented.
  1142. Aggregate Expressions
  1143. ~~~~~~~~~~~~~~~~~~~~~
  1144. .. code-block:: php
  1145. AggregateExpression ::= ("AVG" | "MAX" | "MIN" | "SUM") "(" ["DISTINCT"] StateFieldPathExpression ")" |
  1146. "COUNT" "(" ["DISTINCT"] (IdentificationVariable | SingleValuedPathExpression) ")"
  1147. Case Expressions
  1148. ~~~~~~~~~~~~~~~~
  1149. .. code-block:: php
  1150. CaseExpression ::= GeneralCaseExpression | SimpleCaseExpression | CoalesceExpression | NullifExpression
  1151. GeneralCaseExpression ::= "CASE" WhenClause {WhenClause}* "ELSE" ScalarExpression "END"
  1152. WhenClause ::= "WHEN" ConditionalExpression "THEN" ScalarExpression
  1153. SimpleCaseExpression ::= "CASE" CaseOperand SimpleWhenClause {SimpleWhenClause}* "ELSE" ScalarExpression "END"
  1154. CaseOperand ::= StateFieldPathExpression | TypeDiscriminator
  1155. SimpleWhenClause ::= "WHEN" ScalarExpression "THEN" ScalarExpression
  1156. CoalesceExpression ::= "COALESCE" "(" ScalarExpression {"," ScalarExpression}* ")"
  1157. NullifExpression ::= "NULLIF" "(" ScalarExpression "," ScalarExpression ")"
  1158. Other Expressions
  1159. ~~~~~~~~~~~~~~~~~
  1160. QUANTIFIED/BETWEEN/COMPARISON/LIKE/NULL/EXISTS
  1161. .. code-block:: php
  1162. QuantifiedExpression ::= ("ALL" | "ANY" | "SOME") "(" Subselect ")"
  1163. BetweenExpression ::= ArithmeticExpression ["NOT"] "BETWEEN" ArithmeticExpression "AND" ArithmeticExpression
  1164. ComparisonExpression ::= ArithmeticExpression ComparisonOperator ( QuantifiedExpression | ArithmeticExpression )
  1165. InExpression ::= SingleValuedPathExpression ["NOT"] "IN" "(" (InParameter {"," InParameter}* | Subselect) ")"
  1166. InstanceOfExpression ::= IdentificationVariable ["NOT"] "INSTANCE" ["OF"] (InstanceOfParameter | "(" InstanceOfParameter {"," InstanceOfParameter}* ")")
  1167. InstanceOfParameter ::= AbstractSchemaName | InputParameter
  1168. LikeExpression ::= StringExpression ["NOT"] "LIKE" StringPrimary ["ESCAPE" char]
  1169. NullComparisonExpression ::= (SingleValuedPathExpression | InputParameter) "IS" ["NOT"] "NULL"
  1170. ExistsExpression ::= ["NOT"] "EXISTS" "(" Subselect ")"
  1171. ComparisonOperator ::= "=" | "<" | "<=" | "<>" | ">" | ">=" | "!="
  1172. Functions
  1173. ~~~~~~~~~
  1174. .. code-block:: php
  1175. FunctionDeclaration ::= FunctionsReturningStrings | FunctionsReturningNumerics | FunctionsReturningDateTime
  1176. FunctionsReturningNumerics ::=
  1177. "LENGTH" "(" StringPrimary ")" |
  1178. "LOCATE" "(" StringPrimary "," StringPrimary ["," SimpleArithmeticExpression]")" |
  1179. "ABS" "(" SimpleArithmeticExpression ")" |
  1180. "SQRT" "(" SimpleArithmeticExpression ")" |
  1181. "MOD" "(" SimpleArithmeticExpression "," SimpleArithmeticExpression ")" |
  1182. "SIZE" "(" CollectionValuedPathExpression ")" |
  1183. "DATE_DIFF" "(" ArithmeticPrimary "," ArithmeticPrimary ")" |
  1184. "BIT_AND" "(" ArithmeticPrimary "," ArithmeticPrimary ")" |
  1185. "BIT_OR" "(" ArithmeticPrimary "," ArithmeticPrimary ")"
  1186. FunctionsReturningDateTime ::=
  1187. "CURRENT_DATE" |
  1188. "CURRENT_TIME" |
  1189. "CURRENT_TIMESTAMP" |
  1190. "DATE_ADD" "(" ArithmeticPrimary "," ArithmeticPrimary "," StringPrimary ")" |
  1191. "DATE_SUB" "(" ArithmeticPrimary "," ArithmeticPrimary "," StringPrimary ")"
  1192. FunctionsReturningStrings ::=
  1193. "CONCAT" "(" StringPrimary "," StringPrimary ")" |
  1194. "SUBSTRING" "(" StringPrimary "," SimpleArithmeticExpression "," SimpleArithmeticExpression ")" |
  1195. "TRIM" "(" [["LEADING" | "TRAILING" | "BOTH"] [char] "FROM"] StringPrimary ")" |
  1196. "LOWER" "(" StringPrimary ")" |
  1197. "UPPER" "(" StringPrimary ")" |
  1198. "IDENTITY" "(" SingleValuedAssociationPathExpression ")"