php - 如何为关联/外键设置 ResultSetMapping [是为什么 native 查询返回的关联始终为空]

Question

有关 ResultSetMapping 的更新问题，请参阅最后的编辑

我定义了两个实体(Item 和 ItemType)，其中一个与另一个具有 ManyToOne 关联。由于生成查找正确项目的一些复杂性，我有很多 native 查询。这些查询总是返回第一个实体的所有列 (SELECT items.* ...)。

我发现我的关联在第一项上始终为空，我不确定自己做错了什么。任何帮助将不胜感激。

实体:

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

}

元素类型

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

}

查询是由我的 ItemRepository 类的 getItem 方法生成的。这有点长，但归结为 SELECT items.* FROM items ... 通过 getEntityManager()->createNativeQuery($sql, $rsm); 运行的查询

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;
    }

}

Item 实体被返回但总是有一个空的 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 数据

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

项目数据

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

编辑 我认为我已将其缩小到 ResultSetMapping 配置。更新了上面的代码。结果现在返回两个不同的对象，但没有将它们连接起来(Item 的 itemType 仍然是 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

所以现在的问题基本上是:

我如何设置 ResultSetMapping 以便它返回一个实体，所有加入的关联都完好无损？

Answer 1

Doctrine 的 documentation on Native SQL有一些很好的见解，它很清楚你的错误是什么。对您现有帖子的简短回答是，您应该为您的 ItemType 实体使用 addJoinedEntityResult() 而不是 addEntityResult()。

documentation for Entity Results状态:

An entity result describes an entity type that appears as a root element in the transformed result.

这意味着如果您在同一个映射中添加两个实体结果，您将获得当前看到的结果 - Item 和 ItemType 作为两个不同的对象返回.但是，您知道这两者是相关的，所以 Joined Entity Result更有意义:

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.

要按原样直接修复代码，您需要更改

 $rsm->addEntityResult('\AppBundle\Entity\ItemType', 'item_types');

为此:

$rsm->addJoinedEntityResult(
    '\AppBundle\Entity\ItemType',
    'item_types',
    'items',
    'itemType'
);

格式为addJoinedEntityResult($class, $alias, $parentAlias, $relation)，因此您可以看到添加的第三个和第四个参数指向父别名和 <指向 ItemType 的 strong>Item 类。

综上所述，我认为你把它弄得太复杂了，使用 ResultSetMappingBuilder 可以大大简化你的代码.这可以自动将字段映射到它们的等效 SQL 列，然后如果您更改了字段的名称或数据库中的列的名称，您将不必挖掘所有代码来更新映射.

因此，无需调用复杂的 standardResultSetMapping() 函数，您可以简单地执行以下操作:

$rsm = new ResultSetMappingBuilder($this->_em);
$rsm->addRootEntityFromClassMetadata('AppBundle\Entity\Item', 'items');
$rsm->addJoinedEntityFromClassMetadata('AppBundle\Entity\ItemType', 'item_types', 'items', 'itemType',
    ['id' => 'item_type_id', 
     'account_id' => 'item_type_id', 
     'name' => 'item_type_name',
     'plural_name' => 'item_type_plural_name',
     'label' => 'item_type_label',
     'plural_label' => 'item_type_plural_label',
     'are_users' => 'item_type_are_users',
     'own_users' => 'item_type_own_users']
);

有了它，您就可以消除冗余代码，使其不易出错、更易于测试并自动处理对您的实体和数据库的更新。第二次调用显示您仍然可以传递一组重命名的列。