So richten Sie ResultSetMapping für Zuordnung / Fremdschlüssel ein [war Warum werden von nativen Abfragen zurückgegebene Zuordnungen immer null]

Eine aktualisierte Frage zu ResultSetMapping finden Sie unter Bearbeiten am Ende

Ich habe zwei Entitäten definiert (Item und ItemType), von denen eine eine ManyToOne-Zuordnung zur anderen hat. Ich habe einige native Abfragen, da es schwierig ist, die richtigen Elemente zu finden. Diese Abfragen geben immer alle Spalten der ersten Entität zurück (SELECT-Elemente. * ...).

Ich habe festgestellt, dass meine Assoziationen beim ersten Element immer null sind und ich nicht sicher bin, was ich falsch mache. Jede Hilfe wäre dankbar.

Einheiten:

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.. //

}

Gegenstandsart

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.. //

}

Die Abfrage wird von der Methode getItem meiner ItemRepository-Klasse generiert. Dies ist etwas lang, läuft aber auf eine SELECT items.* FROM items ... Abfrage hinaus, die über getEntityManager()->createNativeQuery($sql, $rsm); ausgeführt wird.

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->logger = $logger;
        $this->itemTypes = $itemTypes;
        $this->itemValues = $itemValues;
        $this->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->logger->debug(__METHOD__.'::params::'.json_encode(['accountId' => $accountId, 'itemId' => $itemId,
                             'restrictedUserOwnerItemType' => $restrictedUserOwnerItemType, 'restrictedUserOwnerItemId' => $restrictedUserOwnerItemId]));
        if(!$accountId || !$itemId || !is_numeric($restrictedUserOwnerItemType) || !is_numeric($restrictedUserOwnerItemId)) 
            throw new \InvalidArgumentException('getItem requires accountId, itemId, restrictedUserOwnerItemType and restrictedUserOwnerItemId');

        /*
        $query = $this->itemsModel->builder();
        $result = $query->where('account_id', '=', $accountId)
                        ->where('id', '=', $itemId)
                        ->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 .= <<<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 .= <<<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->standardResultSetMapping();
        // $this->logger->debug($sql);
        // $this->logger->debug(print_r($params, true));
        echo $sql;
        $query = $this->getEntityManager()->createNativeQuery($sql, $rsm);
        $query->setParameters($params);

        // Wake up the entities
        $result = array();
        foreach($query->getResult() as $row) {
            $row->initServiceEntity($this->logger, $this, $this->itemValues, $this->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->addEntityResult('\AppBundle\Entity\Item', 'items');
        $rsm->addEntityResult('\AppBundle\Entity\ItemType', 'item_types');
        //                   Table,   Column,           Property
        $rsm->addFieldResult('items', 'id',             'id');
        $rsm->addFieldResult('items', 'account_id',     'accountId');
        //$rsm->addFieldResult('items', 'item_type_id',   'itemTypeId');
        $rsm->addFieldResult('items', 'field_count',    'fieldCount');
        $rsm->addFieldResult('items', 'ver',            'ver');
        $rsm->addFieldResult('items', 'title',          'title');
        $rsm->addMetaResult('items', 'item_type_id',    'item_type_id', true);

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

        return $rsm;
    }

}

Die Item-Entität wird zurückgegeben, hat jedoch immer den Null-ItemType:

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
}

Item_types Daten

id  account_id  name  plural_name label plural_label  are_users own_users
31  1           task  tasks       Task  Tasks         1         0

Artikeldaten

id  account_id  item_type_id  field_count ver         title
23  1           31            4           1451940837  New Item

Bearbeiten Ich denke, ich habe dies auf die ResultSetMapping-Konfiguration eingegrenzt. Code oben aktualisiert. Das Ergebnis gibt jetzt zwei unterschiedliche Objekte zurück, verbindet sie jedoch nicht (der itemType des Elements ist immer noch null):

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

Die Frage ist nun also im Grunde:

Wie richte ich ResultSetMapping so ein, dass eine Entität mit allen verbundenen Verknüpfungen zurückgegeben wird?