native-sql.rst 34 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905
  1. Native SQL
  2. ==========
  3. With ``NativeQuery`` you can execute native SELECT SQL statements
  4. and map the results to Doctrine entities or any other result format
  5. supported by Doctrine.
  6. In order to make this mapping possible, you need to describe
  7. to Doctrine what columns in the result map to which entity property.
  8. This description is represented by a ``ResultSetMapping`` object.
  9. With this feature you can map arbitrary SQL code to objects, such as highly
  10. vendor-optimized SQL or stored-procedures.
  11. Writing ``ResultSetMapping`` from scratch is complex, but there is a convenience
  12. wrapper around it called a ``ResultSetMappingBuilder``. It can generate
  13. the mappings for you based on Entities and even generates the ``SELECT``
  14. clause based on this information for you.
  15. .. note::
  16. If you want to execute DELETE, UPDATE or INSERT statements
  17. the Native SQL API cannot be used and will probably throw errors.
  18. Use ``EntityManager#getConnection()`` to access the native database
  19. connection and call the ``executeUpdate()`` method for these
  20. queries.
  21. The NativeQuery class
  22. ---------------------
  23. To create a ``NativeQuery`` you use the method
  24. ``EntityManager#createNativeQuery($sql, $resultSetMapping)``. As you can see in
  25. the signature of this method, it expects 2 ingredients: The SQL you want to
  26. execute and the ``ResultSetMapping`` that describes how the results will be
  27. mapped.
  28. Once you obtained an instance of a ``NativeQuery``, you can bind parameters to
  29. it with the same API that ``Query`` has and execute it.
  30. .. code-block:: php
  31. <?php
  32. use Doctrine\ORM\Query\ResultSetMapping;
  33. $rsm = new ResultSetMapping();
  34. // build rsm here
  35. $query = $entityManager->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', $rsm);
  36. $query->setParameter(1, 'romanb');
  37. $users = $query->getResult();
  38. ResultSetMappingBuilder
  39. -----------------------
  40. An easy start into ResultSet mapping is the ``ResultSetMappingBuilder`` object.
  41. This has several benefits:
  42. - The builder takes care of automatically updating your ``ResultSetMapping``
  43. when the fields or associations change on the metadata of an entity.
  44. - You can generate the required ``SELECT`` expression for a builder
  45. by converting it to a string.
  46. - The API is much simpler than the usual ``ResultSetMapping`` API.
  47. One downside is that the builder API does not yet support entities
  48. with inheritance hierachies.
  49. .. code-block:: php
  50. <?php
  51. use Doctrine\ORM\Query\ResultSetMappingBuilder;
  52. $sql = "SELECT u.id, u.name, a.id AS address_id, a.street, a.city " .
  53. "FROM users u INNER JOIN address a ON u.address_id = a.id";
  54. $rsm = new ResultSetMappingBuilder($entityManager);
  55. $rsm->addRootEntityFromClassMetadata('MyProject\User', 'u');
  56. $rsm->addJoinedEntityFromClassMetadata('MyProject\Address', 'a', 'u', 'address', array('id' => 'address_id'));
  57. The builder extends the ``ResultSetMapping`` class and as such has all the functionality of it as well.
  58. ..versionadded:: 2.4
  59. Starting with Doctrine ORM 2.4 you can generate the ``SELECT`` clause
  60. from a ``ResultSetMappingBuilder``. You can either cast the builder
  61. object to ``(string)`` and the DQL aliases are used as SQL table aliases
  62. or use the ``generateSelectClause($tableAliases)`` method and pass
  63. a mapping from DQL alias (key) to SQL alias (value)
  64. .. code-block:: php
  65. <?php
  66. $selectClause = $builder->generateSelectClause(array(
  67. 'u' => 't1',
  68. 'g' => 't2'
  69. ));
  70. $sql = "SELECT " . $selectClause . " FROM users t1 JOIN groups t2 ON t1.group_id = t2.id";
  71. The ResultSetMapping
  72. --------------------
  73. Understanding the ``ResultSetMapping`` is the key to using a
  74. ``NativeQuery``. A Doctrine result can contain the following
  75. components:
  76. - Entity results. These represent root result elements.
  77. - Joined entity results. These represent joined entities in
  78. associations of root entity results.
  79. - Field results. These represent a column in the result set that
  80. maps to a field of an entity. A field result always belongs to an
  81. entity result or joined entity result.
  82. - Scalar results. These represent scalar values in the result set
  83. that will appear in each result row. Adding scalar results to a
  84. ResultSetMapping can also cause the overall result to become
  85. **mixed** (see DQL - Doctrine Query Language) if the same
  86. ResultSetMapping also contains entity results.
  87. - Meta results. These represent columns that contain
  88. meta-information, such as foreign keys and discriminator columns.
  89. When querying for objects (``getResult()``), all meta columns of
  90. root entities or joined entities must be present in the SQL query
  91. and mapped accordingly using ``ResultSetMapping#addMetaResult``.
  92. .. note::
  93. It might not surprise you that Doctrine uses
  94. ``ResultSetMapping`` internally when you create DQL queries. As
  95. the query gets parsed and transformed to SQL, Doctrine fills a
  96. ``ResultSetMapping`` that describes how the results should be
  97. processed by the hydration routines.
  98. We will now look at each of the result types that can appear in a
  99. ResultSetMapping in detail.
  100. Entity results
  101. ~~~~~~~~~~~~~~
  102. An entity result describes an entity type that appears as a root
  103. element in the transformed result. You add an entity result through
  104. ``ResultSetMapping#addEntityResult()``. Let's take a look at the
  105. method signature in detail:
  106. .. code-block:: php
  107. <?php
  108. /**
  109. * Adds an entity result to this ResultSetMapping.
  110. *
  111. * @param string $class The class name of the entity.
  112. * @param string $alias The alias for the class. The alias must be unique among all entity
  113. * results or joined entity results within this ResultSetMapping.
  114. */
  115. public function addEntityResult($class, $alias)
  116. The first parameter is the fully qualified name of the entity
  117. class. The second parameter is some arbitrary alias for this entity
  118. result that must be unique within a ``ResultSetMapping``. You use
  119. this alias to attach field results to the entity result. It is very
  120. similar to an identification variable that you use in DQL to alias
  121. classes or relationships.
  122. An entity result alone is not enough to form a valid
  123. ``ResultSetMapping``. An entity result or joined entity result
  124. always needs a set of field results, which we will look at soon.
  125. Joined entity results
  126. ~~~~~~~~~~~~~~~~~~~~~
  127. A joined entity result describes an entity type that appears as a
  128. joined relationship element in the transformed result, attached to
  129. a (root) entity result. You add a joined entity result through
  130. ``ResultSetMapping#addJoinedEntityResult()``. Let's take a look at
  131. the method signature in detail:
  132. .. code-block:: php
  133. <?php
  134. /**
  135. * Adds a joined entity result.
  136. *
  137. * @param string $class The class name of the joined entity.
  138. * @param string $alias The unique alias to use for the joined entity.
  139. * @param string $parentAlias The alias of the entity result that is the parent of this joined result.
  140. * @param object $relation The association field that connects the parent entity result with the joined entity result.
  141. */
  142. public function addJoinedEntityResult($class, $alias, $parentAlias, $relation)
  143. The first parameter is the class name of the joined entity. The
  144. second parameter is an arbitrary alias for the joined entity that
  145. must be unique within the ``ResultSetMapping``. You use this alias
  146. to attach field results to the entity result. The third parameter
  147. is the alias of the entity result that is the parent type of the
  148. joined relationship. The fourth and last parameter is the name of
  149. the field on the parent entity result that should contain the
  150. joined entity result.
  151. Field results
  152. ~~~~~~~~~~~~~
  153. A field result describes the mapping of a single column in a SQL
  154. result set to a field in an entity. As such, field results are
  155. inherently bound to entity results. You add a field result through
  156. ``ResultSetMapping#addFieldResult()``. Again, let's examine the
  157. method signature in detail:
  158. .. code-block:: php
  159. <?php
  160. /**
  161. * Adds a field result that is part of an entity result or joined entity result.
  162. *
  163. * @param string $alias The alias of the entity result or joined entity result.
  164. * @param string $columnName The name of the column in the SQL result set.
  165. * @param string $fieldName The name of the field on the (joined) entity.
  166. */
  167. public function addFieldResult($alias, $columnName, $fieldName)
  168. The first parameter is the alias of the entity result to which the
  169. field result will belong. The second parameter is the name of the
  170. column in the SQL result set. Note that this name is case
  171. sensitive, i.e. if you use a native query against Oracle it must be
  172. all uppercase. The third parameter is the name of the field on the
  173. entity result identified by ``$alias`` into which the value of the
  174. column should be set.
  175. Scalar results
  176. ~~~~~~~~~~~~~~
  177. A scalar result describes the mapping of a single column in a SQL
  178. result set to a scalar value in the Doctrine result. Scalar results
  179. are typically used for aggregate values but any column in the SQL
  180. result set can be mapped as a scalar value. To add a scalar result
  181. use ``ResultSetMapping#addScalarResult()``. The method signature in
  182. detail:
  183. .. code-block:: php
  184. <?php
  185. /**
  186. * Adds a scalar result mapping.
  187. *
  188. * @param string $columnName The name of the column in the SQL result set.
  189. * @param string $alias The result alias with which the scalar result should be placed in the result structure.
  190. */
  191. public function addScalarResult($columnName, $alias)
  192. The first parameter is the name of the column in the SQL result set
  193. and the second parameter is the result alias under which the value
  194. of the column will be placed in the transformed Doctrine result.
  195. Meta results
  196. ~~~~~~~~~~~~
  197. A meta result describes a single column in a SQL result set that
  198. is either a foreign key or a discriminator column. These columns
  199. are essential for Doctrine to properly construct objects out of SQL
  200. result sets. To add a column as a meta result use
  201. ``ResultSetMapping#addMetaResult()``. The method signature in
  202. detail:
  203. .. code-block:: php
  204. <?php
  205. /**
  206. * Adds a meta column (foreign key or discriminator column) to the result set.
  207. *
  208. * @param string $alias
  209. * @param string $columnAlias
  210. * @param string $columnName
  211. * @param boolean $isIdentifierColumn
  212. */
  213. public function addMetaResult($alias, $columnAlias, $columnName, $isIdentifierColumn = false)
  214. The first parameter is the alias of the entity result to which the
  215. meta column belongs. A meta result column (foreign key or
  216. discriminator column) always belongs to an entity result. The
  217. second parameter is the column alias/name of the column in the SQL
  218. result set and the third parameter is the column name used in the
  219. mapping.
  220. The fourth parameter should be set to true in case the primary key
  221. of the entity is the foreign key you're adding.
  222. Discriminator Column
  223. ~~~~~~~~~~~~~~~~~~~~
  224. When joining an inheritance tree you have to give Doctrine a hint
  225. which meta-column is the discriminator column of this tree.
  226. .. code-block:: php
  227. <?php
  228. /**
  229. * Sets a discriminator column for an entity result or joined entity result.
  230. * The discriminator column will be used to determine the concrete class name to
  231. * instantiate.
  232. *
  233. * @param string $alias The alias of the entity result or joined entity result the discriminator
  234. * column should be used for.
  235. * @param string $discrColumn The name of the discriminator column in the SQL result set.
  236. */
  237. public function setDiscriminatorColumn($alias, $discrColumn)
  238. Examples
  239. ~~~~~~~~
  240. Understanding a ResultSetMapping is probably easiest through
  241. looking at some examples.
  242. First a basic example that describes the mapping of a single
  243. entity.
  244. .. code-block:: php
  245. <?php
  246. // Equivalent DQL query: "select u from User u where u.name=?1"
  247. // User owns no associations.
  248. $rsm = new ResultSetMapping;
  249. $rsm->addEntityResult('User', 'u');
  250. $rsm->addFieldResult('u', 'id', 'id');
  251. $rsm->addFieldResult('u', 'name', 'name');
  252. $query = $this->_em->createNativeQuery('SELECT id, name FROM users WHERE name = ?', $rsm);
  253. $query->setParameter(1, 'romanb');
  254. $users = $query->getResult();
  255. The result would look like this:
  256. .. code-block:: php
  257. array(
  258. [0] => User (Object)
  259. )
  260. Note that this would be a partial object if the entity has more
  261. fields than just id and name. In the example above the column and
  262. field names are identical but that is not necessary, of course.
  263. Also note that the query string passed to createNativeQuery is
  264. **real native SQL**. Doctrine does not touch this SQL in any way.
  265. In the previous basic example, a User had no relations and the
  266. table the class is mapped to owns no foreign keys. The next example
  267. assumes User has a unidirectional or bidirectional one-to-one
  268. association to a CmsAddress, where the User is the owning side and
  269. thus owns the foreign key.
  270. .. code-block:: php
  271. <?php
  272. // Equivalent DQL query: "select u from User u where u.name=?1"
  273. // User owns an association to an Address but the Address is not loaded in the query.
  274. $rsm = new ResultSetMapping;
  275. $rsm->addEntityResult('User', 'u');
  276. $rsm->addFieldResult('u', 'id', 'id');
  277. $rsm->addFieldResult('u', 'name', 'name');
  278. $rsm->addMetaResult('u', 'address_id', 'address_id');
  279. $query = $this->_em->createNativeQuery('SELECT id, name, address_id FROM users WHERE name = ?', $rsm);
  280. $query->setParameter(1, 'romanb');
  281. $users = $query->getResult();
  282. Foreign keys are used by Doctrine for lazy-loading purposes when
  283. querying for objects. In the previous example, each user object in
  284. the result will have a proxy (a "ghost") in place of the address
  285. that contains the address\_id. When the ghost proxy is accessed, it
  286. loads itself based on this key.
  287. Consequently, associations that are *fetch-joined* do not require
  288. the foreign keys to be present in the SQL result set, only
  289. associations that are lazy.
  290. .. code-block:: php
  291. <?php
  292. // Equivalent DQL query: "select u from User u join u.address a WHERE u.name = ?1"
  293. // User owns association to an Address and the Address is loaded in the query.
  294. $rsm = new ResultSetMapping;
  295. $rsm->addEntityResult('User', 'u');
  296. $rsm->addFieldResult('u', 'id', 'id');
  297. $rsm->addFieldResult('u', 'name', 'name');
  298. $rsm->addJoinedEntityResult('Address' , 'a', 'u', 'address');
  299. $rsm->addFieldResult('a', 'address_id', 'id');
  300. $rsm->addFieldResult('a', 'street', 'street');
  301. $rsm->addFieldResult('a', 'city', 'city');
  302. $sql = 'SELECT u.id, u.name, a.id AS address_id, a.street, a.city FROM users u ' .
  303. 'INNER JOIN address a ON u.address_id = a.id WHERE u.name = ?';
  304. $query = $this->_em->createNativeQuery($sql, $rsm);
  305. $query->setParameter(1, 'romanb');
  306. $users = $query->getResult();
  307. In this case the nested entity ``Address`` is registered with the
  308. ``ResultSetMapping#addJoinedEntityResult`` method, which notifies
  309. Doctrine that this entity is not hydrated at the root level, but as
  310. a joined entity somewhere inside the object graph. In this case we
  311. specify the alias 'u' as third parameter and ``address`` as fourth
  312. parameter, which means the ``Address`` is hydrated into the
  313. ``User::$address`` property.
  314. If a fetched entity is part of a mapped hierarchy that requires a
  315. discriminator column, this column must be present in the result set
  316. as a meta column so that Doctrine can create the appropriate
  317. concrete type. This is shown in the following example where we
  318. assume that there are one or more subclasses that extend User and
  319. either Class Table Inheritance or Single Table Inheritance is used
  320. to map the hierarchy (both use a discriminator column).
  321. .. code-block:: php
  322. <?php
  323. // Equivalent DQL query: "select u from User u where u.name=?1"
  324. // User is a mapped base class for other classes. User owns no associations.
  325. $rsm = new ResultSetMapping;
  326. $rsm->addEntityResult('User', 'u');
  327. $rsm->addFieldResult('u', 'id', 'id');
  328. $rsm->addFieldResult('u', 'name', 'name');
  329. $rsm->addMetaResult('u', 'discr', 'discr'); // discriminator column
  330. $rsm->setDiscriminatorColumn('u', 'discr');
  331. $query = $this->_em->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', $rsm);
  332. $query->setParameter(1, 'romanb');
  333. $users = $query->getResult();
  334. Note that in the case of Class Table Inheritance, an example as
  335. above would result in partial objects if any objects in the result
  336. are actually a subtype of User. When using DQL, Doctrine
  337. automatically includes the necessary joins for this mapping
  338. strategy but with native SQL it is your responsibility.
  339. Named Native Query
  340. ------------------
  341. You can also map a native query using a named native query mapping.
  342. To achieve that, you must describe the SQL resultset structure
  343. using named native query (and sql resultset mappings if is a several resultset mappings).
  344. Like named query, a named native query can be defined at class level or in a XML or YAML file.
  345. A resultSetMapping parameter is defined in @NamedNativeQuery,
  346. it represents the name of a defined @SqlResultSetMapping.
  347. .. configuration-block::
  348. .. code-block:: php
  349. <?php
  350. namespace MyProject\Model;
  351. /**
  352. * @NamedNativeQueries({
  353. * @NamedNativeQuery(
  354. * name = "fetchMultipleJoinsEntityResults",
  355. * resultSetMapping= "mappingMultipleJoinsEntityResults",
  356. * query = "SELECT u.id AS u_id, u.name AS u_name, u.status AS u_status, a.id AS a_id, a.zip AS a_zip, a.country AS a_country, COUNT(p.phonenumber) AS numphones FROM users u INNER JOIN addresses a ON u.id = a.user_id INNER JOIN phonenumbers p ON u.id = p.user_id GROUP BY u.id, u.name, u.status, u.username, a.id, a.zip, a.country ORDER BY u.username"
  357. * ),
  358. * })
  359. * @SqlResultSetMappings({
  360. * @SqlResultSetMapping(
  361. * name = "mappingMultipleJoinsEntityResults",
  362. * entities= {
  363. * @EntityResult(
  364. * entityClass = "__CLASS__",
  365. * fields = {
  366. * @FieldResult(name = "id", column="u_id"),
  367. * @FieldResult(name = "name", column="u_name"),
  368. * @FieldResult(name = "status", column="u_status"),
  369. * }
  370. * ),
  371. * @EntityResult(
  372. * entityClass = "Address",
  373. * fields = {
  374. * @FieldResult(name = "id", column="a_id"),
  375. * @FieldResult(name = "zip", column="a_zip"),
  376. * @FieldResult(name = "country", column="a_country"),
  377. * }
  378. * )
  379. * },
  380. * columns = {
  381. * @ColumnResult("numphones")
  382. * }
  383. * )
  384. *})
  385. */
  386. class User
  387. {
  388. /** @Id @Column(type="integer") @GeneratedValue */
  389. public $id;
  390. /** @Column(type="string", length=50, nullable=true) */
  391. public $status;
  392. /** @Column(type="string", length=255, unique=true) */
  393. public $username;
  394. /** @Column(type="string", length=255) */
  395. public $name;
  396. /** @OneToMany(targetEntity="Phonenumber") */
  397. public $phonenumbers;
  398. /** @OneToOne(targetEntity="Address") */
  399. public $address;
  400. // ....
  401. }
  402. .. code-block:: xml
  403. <doctrine-mapping>
  404. <entity name="MyProject\Model\User">
  405. <named-native-queries>
  406. <named-native-query name="fetchMultipleJoinsEntityResults" result-set-mapping="mappingMultipleJoinsEntityResults">
  407. <query>SELECT u.id AS u_id, u.name AS u_name, u.status AS u_status, a.id AS a_id, a.zip AS a_zip, a.country AS a_country, COUNT(p.phonenumber) AS numphones FROM users u INNER JOIN addresses a ON u.id = a.user_id INNER JOIN phonenumbers p ON u.id = p.user_id GROUP BY u.id, u.name, u.status, u.username, a.id, a.zip, a.country ORDER BY u.username</query>
  408. </named-native-query>
  409. </named-native-queries>
  410. <sql-result-set-mappings>
  411. <sql-result-set-mapping name="mappingMultipleJoinsEntityResults">
  412. <entity-result entity-class="__CLASS__">
  413. <field-result name="id" column="u_id"/>
  414. <field-result name="name" column="u_name"/>
  415. <field-result name="status" column="u_status"/>
  416. </entity-result>
  417. <entity-result entity-class="Address">
  418. <field-result name="id" column="a_id"/>
  419. <field-result name="zip" column="a_zip"/>
  420. <field-result name="country" column="a_country"/>
  421. </entity-result>
  422. <column-result name="numphones"/>
  423. </sql-result-set-mapping>
  424. </sql-result-set-mappings>
  425. </entity>
  426. </doctrine-mapping>
  427. .. code-block:: yaml
  428. MyProject\Model\User:
  429. type: entity
  430. namedNativeQueries:
  431. fetchMultipleJoinsEntityResults:
  432. name: fetchMultipleJoinsEntityResults
  433. resultSetMapping: mappingMultipleJoinsEntityResults
  434. query: SELECT u.id AS u_id, u.name AS u_name, u.status AS u_status, a.id AS a_id, a.zip AS a_zip, a.country AS a_country, COUNT(p.phonenumber) AS numphones FROM users u INNER JOIN addresses a ON u.id = a.user_id INNER JOIN phonenumbers p ON u.id = p.user_id GROUP BY u.id, u.name, u.status, u.username, a.id, a.zip, a.country ORDER BY u.username
  435. sqlResultSetMappings:
  436. mappingMultipleJoinsEntityResults:
  437. name: mappingMultipleJoinsEntityResults
  438. columnResult:
  439. 0:
  440. name: numphones
  441. entityResult:
  442. 0:
  443. entityClass: __CLASS__
  444. fieldResult:
  445. 0:
  446. name: id
  447. column: u_id
  448. 1:
  449. name: name
  450. column: u_name
  451. 2:
  452. name: status
  453. column: u_status
  454. 1:
  455. entityClass: Address
  456. fieldResult:
  457. 0:
  458. name: id
  459. column: a_id
  460. 1:
  461. name: zip
  462. column: a_zip
  463. 2:
  464. name: country
  465. column: a_country
  466. Things to note:
  467. - The resultset mapping declares the entities retrieved by this native query.
  468. - Each field of the entity is bound to a SQL alias (or column name).
  469. - All fields of the entity including the ones of subclasses
  470. and the foreign key columns of related entities have to be present in the SQL query.
  471. - Field definitions are optional provided that they map to the same
  472. column name as the one declared on the class property.
  473. - ``__CLASS__`` is an alias for the mapped class
  474. In the above example,
  475. the ``fetchJoinedAddress`` named query use the joinMapping result set mapping.
  476. This mapping returns 2 entities, User and Address, each property is declared and associated to a column name,
  477. actually the column name retrieved by the query.
  478. Let's now see an implicit declaration of the property / column.
  479. .. configuration-block::
  480. .. code-block:: php
  481. <?php
  482. namespace MyProject\Model;
  483. /**
  484. * @NamedNativeQueries({
  485. * @NamedNativeQuery(
  486. * name = "findAll",
  487. * resultSetMapping = "mappingFindAll",
  488. * query = "SELECT * FROM addresses"
  489. * ),
  490. * })
  491. * @SqlResultSetMappings({
  492. * @SqlResultSetMapping(
  493. * name = "mappingFindAll",
  494. * entities= {
  495. * @EntityResult(
  496. * entityClass = "Address"
  497. * )
  498. * }
  499. * )
  500. * })
  501. */
  502. class Address
  503. {
  504. /** @Id @Column(type="integer") @GeneratedValue */
  505. public $id;
  506. /** @Column() */
  507. public $country;
  508. /** @Column() */
  509. public $zip;
  510. /** @Column()*/
  511. public $city;
  512. // ....
  513. }
  514. .. code-block:: xml
  515. <doctrine-mapping>
  516. <entity name="MyProject\Model\Address">
  517. <named-native-queries>
  518. <named-native-query name="findAll" result-set-mapping="mappingFindAll">
  519. <query>SELECT * FROM addresses</query>
  520. </named-native-query>
  521. </named-native-queries>
  522. <sql-result-set-mappings>
  523. <sql-result-set-mapping name="mappingFindAll">
  524. <entity-result entity-class="Address"/>
  525. </sql-result-set-mapping>
  526. </sql-result-set-mappings>
  527. </entity>
  528. </doctrine-mapping>
  529. .. code-block:: yaml
  530. MyProject\Model\Address:
  531. type: entity
  532. namedNativeQueries:
  533. findAll:
  534. resultSetMapping: mappingFindAll
  535. query: SELECT * FROM addresses
  536. sqlResultSetMappings:
  537. mappingFindAll:
  538. name: mappingFindAll
  539. entityResult:
  540. address:
  541. entityClass: Address
  542. In this example, we only describe the entity member of the result set mapping.
  543. The property / column mappings is done using the entity mapping values.
  544. In this case the model property is bound to the model_txt column.
  545. If the association to a related entity involve a composite primary key,
  546. a @FieldResult element should be used for each foreign key column.
  547. The @FieldResult name is composed of the property name for the relationship,
  548. followed by a dot ("."), followed by the name or the field or property of the primary key.
  549. .. configuration-block::
  550. .. code-block:: php
  551. <?php
  552. namespace MyProject\Model;
  553. /**
  554. * @NamedNativeQueries({
  555. * @NamedNativeQuery(
  556. * name = "fetchJoinedAddress",
  557. * resultSetMapping= "mappingJoinedAddress",
  558. * query = "SELECT u.id, u.name, u.status, a.id AS a_id, a.country AS a_country, a.zip AS a_zip, a.city AS a_city FROM users u INNER JOIN addresses a ON u.id = a.user_id WHERE u.username = ?"
  559. * ),
  560. * })
  561. * @SqlResultSetMappings({
  562. * @SqlResultSetMapping(
  563. * name = "mappingJoinedAddress",
  564. * entities= {
  565. * @EntityResult(
  566. * entityClass = "__CLASS__",
  567. * fields = {
  568. * @FieldResult(name = "id"),
  569. * @FieldResult(name = "name"),
  570. * @FieldResult(name = "status"),
  571. * @FieldResult(name = "address.id", column = "a_id"),
  572. * @FieldResult(name = "address.zip", column = "a_zip"),
  573. * @FieldResult(name = "address.city", column = "a_city"),
  574. * @FieldResult(name = "address.country", column = "a_country"),
  575. * }
  576. * )
  577. * }
  578. * )
  579. * })
  580. */
  581. class User
  582. {
  583. /** @Id @Column(type="integer") @GeneratedValue */
  584. public $id;
  585. /** @Column(type="string", length=50, nullable=true) */
  586. public $status;
  587. /** @Column(type="string", length=255, unique=true) */
  588. public $username;
  589. /** @Column(type="string", length=255) */
  590. public $name;
  591. /** @OneToOne(targetEntity="Address") */
  592. public $address;
  593. // ....
  594. }
  595. .. code-block:: xml
  596. <doctrine-mapping>
  597. <entity name="MyProject\Model\User">
  598. <named-native-queries>
  599. <named-native-query name="fetchJoinedAddress" result-set-mapping="mappingJoinedAddress">
  600. <query>SELECT u.id, u.name, u.status, a.id AS a_id, a.country AS a_country, a.zip AS a_zip, a.city AS a_city FROM users u INNER JOIN addresses a ON u.id = a.user_id WHERE u.username = ?</query>
  601. </named-native-query>
  602. </named-native-queries>
  603. <sql-result-set-mappings>
  604. <sql-result-set-mapping name="mappingJoinedAddress">
  605. <entity-result entity-class="__CLASS__">
  606. <field-result name="id"/>
  607. <field-result name="name"/>
  608. <field-result name="status"/>
  609. <field-result name="address.id" column="a_id"/>
  610. <field-result name="address.zip" column="a_zip"/>
  611. <field-result name="address.city" column="a_city"/>
  612. <field-result name="address.country" column="a_country"/>
  613. </entity-result>
  614. </sql-result-set-mapping>
  615. </sql-result-set-mappings>
  616. </entity>
  617. </doctrine-mapping>
  618. .. code-block:: yaml
  619. MyProject\Model\User:
  620. type: entity
  621. namedNativeQueries:
  622. fetchJoinedAddress:
  623. name: fetchJoinedAddress
  624. resultSetMapping: mappingJoinedAddress
  625. query: SELECT u.id, u.name, u.status, a.id AS a_id, a.country AS a_country, a.zip AS a_zip, a.city AS a_city FROM users u INNER JOIN addresses a ON u.id = a.user_id WHERE u.username = ?
  626. sqlResultSetMappings:
  627. mappingJoinedAddress:
  628. entityResult:
  629. 0:
  630. entityClass: __CLASS__
  631. fieldResult:
  632. 0:
  633. name: id
  634. 1:
  635. name: name
  636. 2:
  637. name: status
  638. 3:
  639. name: address.id
  640. column: a_id
  641. 4:
  642. name: address.zip
  643. column: a_zip
  644. 5:
  645. name: address.city
  646. column: a_city
  647. 6:
  648. name: address.country
  649. column: a_country
  650. If you retrieve a single entity and if you use the default mapping,
  651. you can use the resultClass attribute instead of resultSetMapping:
  652. .. configuration-block::
  653. .. code-block:: php
  654. <?php
  655. namespace MyProject\Model;
  656. /**
  657. * @NamedNativeQueries({
  658. * @NamedNativeQuery(
  659. * name = "find-by-id",
  660. * resultClass = "Address",
  661. * query = "SELECT * FROM addresses"
  662. * ),
  663. * })
  664. */
  665. class Address
  666. {
  667. // ....
  668. }
  669. .. code-block:: xml
  670. <doctrine-mapping>
  671. <entity name="MyProject\Model\Address">
  672. <named-native-queries>
  673. <named-native-query name="find-by-id" result-class="Address">
  674. <query>SELECT * FROM addresses WHERE id = ?</query>
  675. </named-native-query>
  676. </named-native-queries>
  677. </entity>
  678. </doctrine-mapping>
  679. .. code-block:: yaml
  680. MyProject\Model\Address:
  681. type: entity
  682. namedNativeQueries:
  683. findAll:
  684. name: findAll
  685. resultClass: Address
  686. query: SELECT * FROM addresses
  687. In some of your native queries, you'll have to return scalar values,
  688. for example when building report queries.
  689. You can map them in the @SqlResultsetMapping through @ColumnResult.
  690. You actually can even mix, entities and scalar returns in the same native query (this is probably not that common though).
  691. .. configuration-block::
  692. .. code-block:: php
  693. <?php
  694. namespace MyProject\Model;
  695. /**
  696. * @NamedNativeQueries({
  697. * @NamedNativeQuery(
  698. * name = "count",
  699. * resultSetMapping= "mappingCount",
  700. * query = "SELECT COUNT(*) AS count FROM addresses"
  701. * )
  702. * })
  703. * @SqlResultSetMappings({
  704. * @SqlResultSetMapping(
  705. * name = "mappingCount",
  706. * columns = {
  707. * @ColumnResult(
  708. * name = "count"
  709. * )
  710. * }
  711. * )
  712. * })
  713. */
  714. class Address
  715. {
  716. // ....
  717. }
  718. .. code-block:: xml
  719. <doctrine-mapping>
  720. <entity name="MyProject\Model\Address">
  721. <named-native-query name="count" result-set-mapping="mappingCount">
  722. <query>SELECT COUNT(*) AS count FROM addresses</query>
  723. </named-native-query>
  724. <sql-result-set-mappings>
  725. <sql-result-set-mapping name="mappingCount">
  726. <column-result name="count"/>
  727. </sql-result-set-mapping>
  728. </sql-result-set-mappings>
  729. </entity>
  730. </doctrine-mapping>
  731. .. code-block:: yaml
  732. MyProject\Model\Address:
  733. type: entity
  734. namedNativeQueries:
  735. count:
  736. name: count
  737. resultSetMapping: mappingCount
  738. query: SELECT COUNT(*) AS count FROM addresses
  739. sqlResultSetMappings:
  740. mappingCount:
  741. name: mappingCount
  742. columnResult:
  743. count:
  744. name: count