Native SQL - Doctrine Object Relational Mapper (ORM) (original) (raw)

Understanding the ResultSetMapping is the key to using aNativeQuery. A Doctrine result can contain the following components:

It might not surprise you that Doctrine usesResultSetMapping internally when you create DQL queries. As the query gets parsed and transformed to SQL, Doctrine fills aResultSetMapping that describes how the results should be processed by the hydration routines.

We will now look at each of the result types that can appear in a ResultSetMapping in detail.

An entity result describes an entity type that appears as a root element in the transformed result. You add an entity result throughResultSetMapping#addEntityResult(). Let's take a look at the method signature in detail:

`<?php` `/**` ` * Adds an entity result to this ResultSetMapping.` ` *` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">class The class name of the entity.` ` * @param string </annotation></semantics></math>classTheclassnameoftheentity.‘‘∗@paramstringalias The alias for the class. The alias must be unique among all entity` ` * results or joined entity results within this ResultSetMapping.` ` */` `public function addEntityResult($class, $alias)`

The first parameter is the fully qualified name of the entity class. The second parameter is some arbitrary alias for this entity result that must be unique within a ResultSetMapping. You use this alias to attach field results to the entity result. It is very similar to an identification variable that you use in DQL to alias classes or relationships.

An entity result alone is not enough to form a validResultSetMapping. An entity result or joined entity result always needs a set of field results, which we will look at soon.

A joined entity result describes an entity type that appears as a joined relationship element in the transformed result, attached to a (root) entity result. You add a joined entity result throughResultSetMapping#addJoinedEntityResult(). Let's take a look at the method signature in detail:

`<?php` `/**` ` * Adds a joined entity result.` ` *` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>j</mi><mi>o</mi><mi>i</mi><mi>n</mi><mi>e</mi><mi>d</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">class The class name of the joined entity.` ` * @param string </annotation></semantics></math>classTheclassnameofthejoinedentity.‘‘∗@paramstringalias The unique alias to use for the joined entity.` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>p</mi><mi>a</mi><mi>r</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>A</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>t</mi><mi>h</mi><mi>a</mi><mi>t</mi><mi>i</mi><mi>s</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mi>j</mi><mi>o</mi><mi>i</mi><mi>n</mi><mi>e</mi><mi>d</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>o</mi><mi>b</mi><mi>j</mi><mi>e</mi><mi>c</mi><mi>t</mi></mrow><annotation encoding="application/x-tex">parentAlias The alias of the entity result that is the parent of this joined result.` ` * @param object </annotation></semantics></math>parentAliasThealiasoftheentityresultthatistheparentofthisjoinedresult.‘‘∗@paramobjectrelation The association field that connects the parent entity result with the joined entity result.` ` */` `public function addJoinedEntityResult($class, <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">alias, </annotation></semantics></math>alias,parentAlias, $relation)`

The first parameter is the class name of the joined entity. The second parameter is an arbitrary alias for the joined entity that must be unique within the ResultSetMapping. You use this alias to attach field results to the entity result. The third parameter is the alias of the entity result that is the parent type of the joined relationship. The fourth and last parameter is the name of the field on the parent entity result that should contain the joined entity result.

A field result describes the mapping of a single column in a SQL result set to a field in an entity. As such, field results are inherently bound to entity results. You add a field result throughResultSetMapping#addFieldResult(). Again, let's examine the method signature in detail:

`<?php` `/**` ` * Adds a field result that is part of an entity result or joined entity result.` ` *` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi>j</mi><mi>o</mi><mi>i</mi><mi>n</mi><mi>e</mi><mi>d</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">alias The alias of the entity result or joined entity result.` ` * @param string </annotation></semantics></math>aliasThealiasoftheentityresultorjoinedentityresult.‘‘∗@paramstringcolumnName The name of the column in the SQL result set.` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>f</mi><mi>i</mi><mi>e</mi><mi>l</mi><mi>d</mi><mi>N</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>f</mi><mi>i</mi><mi>e</mi><mi>l</mi><mi>d</mi><mi>o</mi><mi>n</mi><mi>t</mi><mi>h</mi><mi>e</mi><mo stretchy="false">(</mo><mi>j</mi><mi>o</mi><mi>i</mi><mi>n</mi><mi>e</mi><mi>d</mi><mo stretchy="false">)</mo><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">/</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mi>p</mi><mi>u</mi><mi>b</mi><mi>l</mi><mi>i</mi><mi>c</mi><mi>f</mi><mi>u</mi><mi>n</mi><mi>c</mi><mi>t</mi><mi>i</mi><mi>o</mi><mi>n</mi><mi>a</mi><mi>d</mi><mi>d</mi><mi>F</mi><mi>i</mi><mi>e</mi><mi>l</mi><mi>d</mi><mi>R</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">fieldName The name of the field on the (joined) entity.` ` */` `public function addFieldResult(</annotation></semantics></math>fieldNameThenameofthefieldonthe(joined)entity.‘‘∗/‘‘publicfunctionaddFieldResult(alias, <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mi>N</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">columnName, </annotation></semantics></math>columnName,fieldName)`

The first parameter is the alias of the entity result to which the field result will belong. The second parameter is the name of the column in the SQL result set. Note that this name is case sensitive, i.e. if you use a native query against Oracle it must be all uppercase. The third parameter is the name of the field on the entity result identified by $alias into which the value of the column should be set.

A scalar result describes the mapping of a single column in a SQL result set to a scalar value in the Doctrine result. Scalar results are typically used for aggregate values but any column in the SQL result set can be mapped as a scalar value. To add a scalar result use ResultSetMapping#addScalarResult(). The method signature in detail:

`<?php` `/**` ` * Adds a scalar result mapping.` ` *` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>c</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mi>N</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>c</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mi>i</mi><mi>n</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>S</mi><mi>Q</mi><mi>L</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>s</mi><mi>e</mi><mi>t</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">columnName The name of the column in the SQL result set.` ` * @param string </annotation></semantics></math>columnNameThenameofthecolumnintheSQLresultset.‘‘∗@paramstringalias The result alias with which the scalar result should be placed in the result structure.` ` */` `public function addScalarResult($columnName, $alias)`

The first parameter is the name of the column in the SQL result set and the second parameter is the result alias under which the value of the column will be placed in the transformed Doctrine result.

You can also use ResultSetMapping to map the results of a native SQL query to a DTO (Data Transfer Object). This is done by adding scalar results for each argument of the DTO's constructor, then filling thenewObjectMappings property of the ResultSetMapping with information about where to map each scalar result:

`<?php` <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>s</mi><mi>m</mi><mo>=</mo><mi>n</mi><mi>e</mi><mi>w</mi><mi>R</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>S</mi><mi>e</mi><mi>t</mi><mi>M</mi><mi>a</mi><mi>p</mi><mi>p</mi><mi>i</mi><mi>n</mi><mi>g</mi><mo stretchy="false">(</mo><mo stretchy="false">)</mo><mo separator="true">;</mo><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi></mrow><annotation encoding="application/x-tex">rsm = new ResultSetMapping(); </annotation></semantics></math>rsm=newResultSetMapping();‘‘rsm->addScalarResult('name', 1, 'string'); <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>s</mi><mi>m</mi><mo>−</mo><mo>></mo><mi>a</mi><mi>d</mi><mi>d</mi><mi>S</mi><mi>c</mi><mi>a</mi><mi>l</mi><mi>a</mi><mi>r</mi><mi>R</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>e</mi><mi>m</mi><mi>a</mi><mi>i</mi><msup><mi>l</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo><mn>2</mn><msup><mo separator="true">,</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><msup><mi>g</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo stretchy="false">)</mo><mo separator="true">;</mo><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi></mrow><annotation encoding="application/x-tex">rsm->addScalarResult('email', 2, 'string'); </annotation></semantics></math>rsm−>addScalarResult(′email′,2,′string′);‘‘rsm->addScalarResult('city', 3, 'string'); <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>s</mi><mi>m</mi><mo>−</mo><mo>></mo><mi>n</mi><mi>e</mi><mi>w</mi><mi>O</mi><mi>b</mi><mi>j</mi><mi>e</mi><mi>c</mi><mi>t</mi><mi>M</mi><mi>a</mi><mi>p</mi><mi>p</mi><mi>i</mi><mi>n</mi><mi>g</mi><mi>s</mi><msup><mo stretchy="false">[</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>n</mi><mi>a</mi><mi>m</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo stretchy="false">]</mo><mo>=</mo><mo stretchy="false">[</mo><mi mathvariant="normal">‘</mi><msup><mi mathvariant="normal">‘</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mi>N</mi><mi>a</mi><mi>m</mi><msup><mi>e</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>=</mo><mo>></mo><mi>C</mi><mi>m</mi><mi>s</mi><mi>U</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>D</mi><mi>T</mi><mi>O</mi><mo>:</mo><mo>:</mo><mi>c</mi><mi>l</mi><mi>a</mi><mi>s</mi><mi>s</mi><mo separator="true">,</mo><mi mathvariant="normal">‘</mi><msup><mi mathvariant="normal">‘</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>o</mi><mi>b</mi><mi>j</mi><mi>I</mi><mi>n</mi><mi>d</mi><mi>e</mi><msup><mi>x</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>=</mo><mo>></mo><mn>0</mn><mo separator="true">,</mo><mi mathvariant="normal">/</mi><mi mathvariant="normal">/</mi><mi>a</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>c</mi><mi>a</mi><mi>n</mi><mi>c</mi><mi>o</mi><mi>n</mi><mi>t</mi><mi>a</mi><mi>i</mi><mi>n</mi><mi>m</mi><mi>a</mi><mi>n</mi><mi>y</mi><mi>D</mi><mi>T</mi><mi>O</mi><mi>s</mi><mo separator="true">,</mo><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mi>i</mi><mi>s</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>i</mi><mi>n</mi><mi>d</mi><mi>e</mi><mi>x</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>D</mi><mi>T</mi><mi>O</mi><mi>t</mi><mi>o</mi><mi>m</mi><mi>a</mi><mi>p</mi><mi>t</mi><mi>o</mi><mi mathvariant="normal">‘</mi><msup><mi mathvariant="normal">‘</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>a</mi><mi>r</mi><mi>g</mi><mi>I</mi><mi>n</mi><mi>d</mi><mi>e</mi><msup><mi>x</mi><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo>=</mo><mo>></mo><mn>0</mn><mo separator="true">,</mo><mi mathvariant="normal">/</mi><mi mathvariant="normal">/</mi><mi>e</mi><mi>a</mi><mi>c</mi><mi>h</mi><mi>s</mi><mi>c</mi><mi>a</mi><mi>l</mi><mi>a</mi><mi>r</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>c</mi><mi>a</mi><mi>n</mi><mi>b</mi><mi>e</mi><mi>m</mi><mi>a</mi><mi>p</mi><mi>p</mi><mi>e</mi><mi>d</mi><mi>t</mi><mi>o</mi><mi>a</mi><mi>d</mi><mi>i</mi><mi>f</mi><mi>f</mi><mi>e</mi><mi>r</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>a</mi><mi>r</mi><mi>g</mi><mi>u</mi><mi>m</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>D</mi><mi>T</mi><mi>O</mi><mi>c</mi><mi>o</mi><mi>n</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>u</mi><mi>c</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo stretchy="false">]</mo><mo separator="true">;</mo><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi></mrow><annotation encoding="application/x-tex">rsm->newObjectMappings['name'] = [ 'className' => CmsUserDTO::class, 'objIndex' => 0, // a result can contain many DTOs, this is the index of the DTO to map to 'argIndex' => 0, // each scalar result can be mapped to a different argument of the DTO constructor ]; </annotation></semantics></math>rsm−>newObjectMappings[′name′]=[‘‘′className′=>CmsUserDTO::class,‘‘′objIndex′=>0,//aresultcancontainmanyDTOs,thisistheindexoftheDTOtomapto‘‘′argIndex′=>0,//eachscalarresultcanbemappedtoadifferentargumentoftheDTOconstructor‘‘];‘‘rsm->newObjectMappings['email'] = [ 'className' => CmsUserDTO::class, 'objIndex' => 0, 'argIndex' => 1, ]; $rsm->newObjectMappings['city'] = [ 'className' => CmsUserDTO::class, 'objIndex' => 0, 'argIndex' => 2, ]; ``

When joining an inheritance tree you have to give Doctrine a hint which meta-column is the discriminator column of this tree.

`<?php` `/**` ` * Sets a discriminator column for an entity result or joined entity result.` ` * The discriminator column will be used to determine the concrete class name to` ` * instantiate.` ` *` ` * @param string <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>T</mi><mi>h</mi><mi>e</mi><mi>a</mi><mi>l</mi><mi>i</mi><mi>a</mi><mi>s</mi><mi>o</mi><mi>f</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi>j</mi><mi>o</mi><mi>i</mi><mi>n</mi><mi>e</mi><mi>d</mi><mi>e</mi><mi>n</mi><mi>t</mi><mi>i</mi><mi>t</mi><mi>y</mi><mi>r</mi><mi>e</mi><mi>s</mi><mi>u</mi><mi>l</mi><mi>t</mi><mi>t</mi><mi>h</mi><mi>e</mi><mi>d</mi><mi>i</mi><mi>s</mi><mi>c</mi><mi>r</mi><mi>i</mi><mi>m</mi><mi>i</mi><mi>n</mi><mi>a</mi><mi>t</mi><mi>o</mi><mi>r</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi>c</mi><mi>o</mi><mi>l</mi><mi>u</mi><mi>m</mi><mi>n</mi><mi>s</mi><mi>h</mi><mi>o</mi><mi>u</mi><mi>l</mi><mi>d</mi><mi>b</mi><mi>e</mi><mi>u</mi><mi>s</mi><mi>e</mi><mi>d</mi><mi>f</mi><mi>o</mi><mi>r</mi><mi mathvariant="normal">.</mi><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi><mo>∗</mo><mi mathvariant="normal">@</mi><mi>p</mi><mi>a</mi><mi>r</mi><mi>a</mi><mi>m</mi><mi>s</mi><mi>t</mi><mi>r</mi><mi>i</mi><mi>n</mi><mi>g</mi></mrow><annotation encoding="application/x-tex">alias The alias of the entity result or joined entity result the discriminator` ` * column should be used for.` ` * @param string </annotation></semantics></math>aliasThealiasoftheentityresultorjoinedentityresultthediscriminator‘‘∗columnshouldbeusedfor.‘‘∗@paramstringdiscrColumn The name of the discriminator column in the SQL result set.` ` */` `public function setDiscriminatorColumn($alias, $discrColumn)`

Understanding a ResultSetMapping is probably easiest through looking at some examples.

First a basic example that describes the mapping of a single entity.

`<?php` `// Equivalent DQL query: "select u from User u where u.name=?1"` `// User owns no associations.` `$rsm = new ResultSetMapping;` `$rsm->addEntityResult('User', 'u');` `$rsm->addFieldResult('u', 'id', 'id');` `$rsm->addFieldResult('u', 'name', 'name');` $query = <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mo>−</mo><msub><mo>></mo><mi>e</mi></msub><mi>m</mi><mo>−</mo><mo>></mo><mi>c</mi><mi>r</mi><mi>e</mi><mi>a</mi><mi>t</mi><mi>e</mi><mi>N</mi><mi>a</mi><mi>t</mi><mi>i</mi><mi>v</mi><mi>e</mi><mi>Q</mi><mi>u</mi><mi>e</mi><mi>r</mi><mi>y</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>W</mi><mi>H</mi><mi>E</mi><mi>R</mi><mi>E</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo>=</mo><msup><mo stretchy="false">?</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">this->_em->createNativeQuery('SELECT id, name FROM users WHERE name = ?', </annotation></semantics></math>this−>em−>createNativeQuery(′SELECTid,nameFROMusersWHEREname=?′,rsm); $query->setParameter(1, 'romanb'); `$users = $query->getResult();`

The result would look like this:

`array(` ` [0] => User (Object)` `)`

Note that this would be a partial object if the entity has more fields than just id and name. In the example above the column and field names are identical but that is not necessary, of course. Also note that the query string passed to createNativeQuery isreal native SQL. Doctrine does not touch this SQL in any way.

In the previous basic example, a User had no relations and the table the class is mapped to owns no foreign keys. The next example assumes User has a unidirectional or bidirectional one-to-one association to a CmsAddress, where the User is the owning side and thus owns the foreign key.

`<?php` `// Equivalent DQL query: "select u from User u where u.name=?1"` `// User owns an association to an Address but the Address is not loaded in the query.` `$rsm = new ResultSetMapping;` `$rsm->addEntityResult('User', 'u');` `$rsm->addFieldResult('u', 'id', 'id');` `$rsm->addFieldResult('u', 'name', 'name');` `$rsm->addMetaResult('u', 'address_id', 'address_id');` $query = <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mo>−</mo><msub><mo>></mo><mi>e</mi></msub><mi>m</mi><mo>−</mo><mo>></mo><mi>c</mi><mi>r</mi><mi>e</mi><mi>a</mi><mi>t</mi><mi>e</mi><mi>N</mi><mi>a</mi><mi>t</mi><mi>i</mi><mi>v</mi><mi>e</mi><mi>Q</mi><mi>u</mi><mi>e</mi><mi>r</mi><mi>y</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo><mi>a</mi><mi>d</mi><mi>d</mi><mi>r</mi><mi>e</mi><mi>s</mi><msub><mi>s</mi><mi>i</mi></msub><mi>d</mi><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>W</mi><mi>H</mi><mi>E</mi><mi>R</mi><mi>E</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo>=</mo><msup><mo stretchy="false">?</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">this->_em->createNativeQuery('SELECT id, name, address_id FROM users WHERE name = ?', </annotation></semantics></math>this−>em−>createNativeQuery(′SELECTid,name,addressidFROMusersWHEREname=?′,rsm); $query->setParameter(1, 'romanb'); `$users = $query->getResult();`

Foreign keys are used by Doctrine for lazy-loading purposes when querying for objects. In the previous example, each user object in the result will have a proxy (a "ghost") in place of the address that contains the address_id. When the ghost proxy is accessed, it loads itself based on this key.

Consequently, associations that are fetch-joined do not require the foreign keys to be present in the SQL result set, only associations that are lazy.

`<?php` `// Equivalent DQL query: "select u from User u join u.address a WHERE u.name = ?1"` `// User owns association to an Address and the Address is loaded in the query.` `$rsm = new ResultSetMapping;` `$rsm->addEntityResult('User', 'u');` `$rsm->addFieldResult('u', 'id', 'id');` `$rsm->addFieldResult('u', 'name', 'name');` `$rsm->addJoinedEntityResult('Address' , 'a', 'u', 'address');` `$rsm->addFieldResult('a', 'address_id', 'id');` `$rsm->addFieldResult('a', 'street', 'street');` `$rsm->addFieldResult('a', 'city', 'city');` $sql = 'SELECT u.id, u.name, a.id AS address_id, a.street, a.city FROM users u ' . 'INNER JOIN address a ON u.address_id = a.id WHERE u.name = ?'; $query = <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mo>−</mo><msub><mo>></mo><mi>e</mi></msub><mi>m</mi><mo>−</mo><mo>></mo><mi>c</mi><mi>r</mi><mi>e</mi><mi>a</mi><mi>t</mi><mi>e</mi><mi>N</mi><mi>a</mi><mi>t</mi><mi>i</mi><mi>v</mi><mi>e</mi><mi>Q</mi><mi>u</mi><mi>e</mi><mi>r</mi><mi>y</mi><mo stretchy="false">(</mo></mrow><annotation encoding="application/x-tex">this->_em->createNativeQuery(</annotation></semantics></math>this−>em−>createNativeQuery(sql, <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>r</mi><mi>s</mi><mi>m</mi><mo stretchy="false">)</mo><mo separator="true">;</mo><mi mathvariant="normal">‘</mi><mi mathvariant="normal">‘</mi></mrow><annotation encoding="application/x-tex">rsm); </annotation></semantics></math>rsm);‘‘query->setParameter(1, 'romanb'); `$users = $query->getResult();`

In this case the nested entity Address is registered with theResultSetMapping#addJoinedEntityResult method, which notifies Doctrine that this entity is not hydrated at the root level, but as a joined entity somewhere inside the object graph. In this case we specify the alias 'u' as third parameter and address as fourth parameter, which means the Address is hydrated into theUser::$address property.

If a fetched entity is part of a mapped hierarchy that requires a discriminator column, this column must be present in the result set as a meta column so that Doctrine can create the appropriate concrete type. This is shown in the following example where we assume that there are one or more subclasses that extend User and either Class Table Inheritance or Single Table Inheritance is used to map the hierarchy (both use a discriminator column).

`<?php` `// Equivalent DQL query: "select u from User u where u.name=?1"` `// User is a mapped base class for other classes. User owns no associations.` `$rsm = new ResultSetMapping;` `$rsm->addEntityResult('User', 'u');` `$rsm->addFieldResult('u', 'id', 'id');` `$rsm->addFieldResult('u', 'name', 'name');` `$rsm->addMetaResult('u', 'discr', 'discr'); // discriminator column` `$rsm->setDiscriminatorColumn('u', 'discr');` $query = <math xmlns="http://www.w3.org/1998/Math/MathML"><semantics><mrow><mi>t</mi><mi>h</mi><mi>i</mi><mi>s</mi><mo>−</mo><msub><mo>></mo><mi>e</mi></msub><mi>m</mi><mo>−</mo><mo>></mo><mi>c</mi><mi>r</mi><mi>e</mi><mi>a</mi><mi>t</mi><mi>e</mi><mi>N</mi><mi>a</mi><mi>t</mi><mi>i</mi><mi>v</mi><mi>e</mi><mi>Q</mi><mi>u</mi><mi>e</mi><mi>r</mi><mi>y</mi><msup><mo stretchy="false">(</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mi>S</mi><mi>E</mi><mi>L</mi><mi>E</mi><mi>C</mi><mi>T</mi><mi>i</mi><mi>d</mi><mo separator="true">,</mo><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo separator="true">,</mo><mi>d</mi><mi>i</mi><mi>s</mi><mi>c</mi><mi>r</mi><mi>F</mi><mi>R</mi><mi>O</mi><mi>M</mi><mi>u</mi><mi>s</mi><mi>e</mi><mi>r</mi><mi>s</mi><mi>W</mi><mi>H</mi><mi>E</mi><mi>R</mi><mi>E</mi><mi>n</mi><mi>a</mi><mi>m</mi><mi>e</mi><mo>=</mo><msup><mo stretchy="false">?</mo><mo mathvariant="normal" lspace="0em" rspace="0em">′</mo></msup><mo separator="true">,</mo></mrow><annotation encoding="application/x-tex">this->_em->createNativeQuery('SELECT id, name, discr FROM users WHERE name = ?', </annotation></semantics></math>this−>em−>createNativeQuery(′SELECTid,name,discrFROMusersWHEREname=?′,rsm); $query->setParameter(1, 'romanb'); `$users = $query->getResult();`

Note that in the case of Class Table Inheritance, an example as above would result in partial objects if any objects in the result are actually a subtype of User. When using DQL, Doctrine automatically includes the necessary joins for this mapping strategy but with native SQL it is your responsibility.