How to setup ResultSetMapping for association / foreign key [was Why are associations returned by native queries always null]
<p><strong>See edit at end for updated question about ResultSetMapping</strong></p>

<p>I have two entities defined (Item and ItemType), one of which has a ManyToOne association to the other. I have quite a few native queries due to some complexity of generating finding the right items. These queries always return all columns of the first entity (SELECT items.* ...).</p>

<p>I've found that my associations are always null on the first item and I'm not sure what I'm doing incorrectly. Any help would be appreciated.</p>

<p>Entities:</p>

<p>

<pre><code>namespace AppBundle\Entity;

use Psr\Log\LoggerInterface;
use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="items")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\ItemRepository")
 */
class Item {

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="account_id", type="integer")
     */
    private $accountId;

    /**
     * @ORM\ManyToOne(targetEntity="ItemType")
     * @ORM\JoinColumn(name="item_type_id", referencedColumnName="id")
     */
    private $itemType;

    // ..snip.. //

}
</code></pre>

<p>ItemType</p>

<p>

<pre><code>namespace AppBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * @ORM\Table(name="item_types")
 * @ORM\Entity(repositoryClass="AppBundle\Entity\ItemTypeRepository")
 */
class ItemType {

    /**
     * @ORM\Column(type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    private $id;

    /**
     * @ORM\Column(name="account_id", type="integer")
     */
    private $accountId;

    /**
     * @ORM\Column(type="string", length=128)
     */
    private $name;

    // ..snip.. //

}
</code></pre>

<p>The query is generated by the method getItem of my ItemRepository class. This is a bit long but boils down to a <code>SELECT items.* FROM items ...</code> query run via <code>getEntityManager()-&gt;createNativeQuery($sql, $rsm);</code></p>

<p>

<pre><code>namespace AppBundle\Entity;

use Psr\Log\LoggerInterface;
use Doctrine\ORM\Query\ResultSetMapping;

/**
 * ItemRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class ItemRepository extends \Doctrine\ORM\EntityRepository
{
    /**
     * @var \Psr\Log\LoggerInterface $logger
     */
    protected $logger;

    /**
     * @var ItemTypeRepository
     */
    protected $itemTypes;

    /**
     * @var ItemValueRepository
     */
    protected $itemValues;

    /**
     * @var FieldRepository
     */
    protected $fields;

    /**
     * Called by service bindings in services.yml instead of __construct, which is needed by
     * Doctrine.
     */
    public function initService(LoggerInterface $logger,
                                ItemTypeRepository $itemTypes,
                                ItemValueRepository $itemValues,
                                FieldRepository $fields)
    {
        $this-&gt;logger = $logger;
        $this-&gt;itemTypes = $itemTypes;
        $this-&gt;itemValues = $itemValues;
        $this-&gt;fields = $fields;
    }

    /**
     * Get items for an account via itemId
     *
     * @param integer $accountId a user's account id
     * @param $itemId unique ID for an Item
     * @return Item_model
     */
    public function getItem($accountId, $itemId, $restrictedUserOwnerItemType, $restrictedUserOwnerItemId)
    {
        $this-&gt;logger-&gt;debug(__METHOD__.'::params::'.json_encode(['accountId' =&gt; $accountId, 'itemId' =&gt; $itemId,
                             'restrictedUserOwnerItemType' =&gt; $restrictedUserOwnerItemType, 'restrictedUserOwnerItemId' =&gt; $restrictedUserOwnerItemId]));
        if(!$accountId || !$itemId || !is_numeric($restrictedUserOwnerItemType) || !is_numeric($restrictedUserOwnerItemId)) 
            throw new \InvalidArgumentException('getItem requires accountId, itemId, restrictedUserOwnerItemType and restrictedUserOwnerItemId');

        /*
        $query = $this-&gt;itemsModel-&gt;builder();
        $result = $query-&gt;where('account_id', '=', $accountId)
                        -&gt;where('id', '=', $itemId)
                        -&gt;first();
        */

        $sql = "SELECT items.*, ".
        "item_types.id AS item_type_id, ".
        "item_types.account_id AS item_type_account_id, ".
        "item_types.name AS item_type_name, ".
        "item_types.plural_name AS item_type_name, ".
        "item_types.label AS item_type_label, ".
        "item_types.plural_label AS item_type_plural_label, ".
        "item_types.are_users AS item_type_are_users, ".
        "item_types.own_users AS item_type_own_users ".
        "FROM items ".
        "JOIN item_types ON item_types.id = items.item_type_id ";

        $isRestrictedUser = $restrictedUserOwnerItemType != 0 || $restrictedUserOwnerItemId != 0;
        if($isRestrictedUser)
        {
            // Limit to items that are visible to restricted users
            $sql .= &lt;&lt;&lt;SQL


      WHERE item_types.visible_to_restricted_users = 1 

SQL;

            // Limit to items that contain a relationship field pointed at the same owner item type,
            // with the same item ID. For instance, limit items to those that have a Clients relationship
            // field with "Acme Co." client selected as the client.
            $sql .= &lt;&lt;&lt;SQL

AND items.id IN ( /* Where Item Belongs to Same Owner */
      SELECT item_id 
      FROM item_values 
      JOIN fields ON fields.id = item_values.field_id
      JOIN items ON items.id = item_values.item_id AND item_values.ver = items.ver
      JOIN item_types ON item_types.id = items.item_type_id
      WHERE item_values.value = ?
        AND fields.field_type = "Relationship"
        AND fields.field_item_type_id = ?)

SQL;
            $params[] = $restrictedUserOwnerItemId;     // Example: 3  -- CLIENT ID
            $params[] = $restrictedUserOwnerItemType;   // Example: 10 -- CLIENTS

            $sql .= "AND ";
        } else {
            $sql .= "WHERE ";
        }

        $sql .= "items.account_id = ? AND items.id = ? ";
        $params[] = $accountId;
        $params[] = $itemId;

        // Get raw records
        $rsm = $this-&gt;standardResultSetMapping();
        // $this-&gt;logger-&gt;debug($sql);
        // $this-&gt;logger-&gt;debug(print_r($params, true));
        echo $sql;
        $query = $this-&gt;getEntityManager()-&gt;createNativeQuery($sql, $rsm);
        $query-&gt;setParameters($params);

        // Wake up the entities
        $result = array();
        foreach($query-&gt;getResult() as $row) {
            $row-&gt;initServiceEntity($this-&gt;logger, $this, $this-&gt;itemValues, $this-&gt;fields);
            $result[] = $row;
        }

        if(!$result || count($result) == 0)
            throw new \InvalidArgumentException("Item could not be located for Item #".$itemId.". You may not have permission to view this item or it may not exist.");
        else
        {
            return $result[0];
        }
    }

    private function standardResultSetMapping()
    {
        $rsm = new ResultSetMapping();
        //                    Class,                    Table
        $rsm-&gt;addEntityResult('\AppBundle\Entity\Item', 'items');
        $rsm-&gt;addEntityResult('\AppBundle\Entity\ItemType', 'item_types');
        //                   Table,   Column,           Property
        $rsm-&gt;addFieldResult('items', 'id',             'id');
        $rsm-&gt;addFieldResult('items', 'account_id',     'accountId');
        //$rsm-&gt;addFieldResult('items', 'item_type_id',   'itemTypeId');
        $rsm-&gt;addFieldResult('items', 'field_count',    'fieldCount');
        $rsm-&gt;addFieldResult('items', 'ver',            'ver');
        $rsm-&gt;addFieldResult('items', 'title',          'title');
        $rsm-&gt;addMetaResult('items', 'item_type_id',    'item_type_id', true);

        $rsm-&gt;addFieldResult('item_types', 'item_type_id',          'id');
        $rsm-&gt;addFieldResult('item_types', 'item_type_name',        'name');
        $rsm-&gt;addFieldResult('item_types', 'item_type_plural_name', 'pluralName');
        $rsm-&gt;addFieldResult('item_types', 'item_type_label',       'label');
        $rsm-&gt;addFieldResult('item_types', 'item_type_plural_label','pluralLabel');
        $rsm-&gt;addFieldResult('item_types', 'item_type_are_users',   'areUsers');
        $rsm-&gt;addFieldResult('item_types', 'item_type_own_users',   'ownUsers');

        return $rsm;
    }

}
</code></pre>

<p>The Item entity is returned but always has a null itemType:</p>

<pre><code>Item {#548 ▼
  -id: 23
  -accountId: 1
  -itemType: null
  -fieldCount: 4
  -ver: 1451940837
  -title: "New Item"
  #fields: []
  #itemValues: []
  #cacheValues: []
  #logger: Logger {#268 ▶}
  #itemsRepository: ItemRepository {#349 ▶}
  #itemValuesRepository: ItemValueRepository {#416 ▶}
  #fieldsRepository: FieldRepository {#338 ▶}
  #loaded: true
  #changeCount: 0
}
</code></pre>

<p>item_types data</p>

<pre><code>id  account_id  name  plural_name label plural_label  are_users own_users
31  1           task  tasks       Task  Tasks         1         0
</code></pre>

<p>items data</p>

<pre><code>id  account_id  item_type_id  field_count ver         title
23  1           31            4           1451940837  New Item
</code></pre>

<p><strong><em>Edit</em></strong> I've narrowed this down to the ResultSetMapping configuration, I think. Updated code above. The result now returns two distinct objects, but does not wire them up (the Item's itemType is still null):</p>

<pre><code>object(AppBundle\Entity\Item)[560]
  private 'id' =&gt; int 23
  private 'accountId' =&gt; int 1
  private 'itemType' =&gt; null
  private 'fieldCount' =&gt; int 4
  private 'ver' =&gt; int 1451940837
  private 'title' =&gt; string 'New Item' (length=8)
  protected 'fields' =&gt; 
    array (size=0)
      empty
  protected 'itemValues' =&gt; 
    array (size=0)
      empty
  protected 'cacheValues' =&gt; 
    array (size=0)
      empty
  protected 'logger' =&gt; null
  protected 'itemsRepository' =&gt; null
  protected 'itemValuesRepository' =&gt; null
  protected 'fieldsRepository' =&gt; null
  protected 'loaded' =&gt; boolean false
  protected 'changeCount' =&gt; int 0
object(AppBundle\Entity\ItemType)[507]
  private 'id' =&gt; int 31
  private 'accountId' =&gt; int 1
  private 'name' =&gt; string 'task' (length=4)
  private 'pluralName' =&gt; string 'tasks' (length=5)
  private 'label' =&gt; string 'Task' (length=4)
  private 'pluralLabel' =&gt; string 'Tasks' (length=5)
  private 'areUsers' =&gt; boolean true
  private 'ownUsers' =&gt; boolean false
</code></pre>

<p>So the question is now basically:</p>

<p>How do I setup ResultSetMapping so that it will return an Entity with all joined associations intact?</p>