有关ResultSetMapping的更新问题，请参阅末尾的编辑
我定义了两个实体（Item和ItemType），其中一个实体与另一个实体有ManyToOne关联。由于生成查找正确项目的复杂性，我有相当多的本地查询。这些查询总是返回第一个实体（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生成的。这有点长，但可以归结为通过getEntityManager()->createNativeQuery($sql, $rsm); 运行的SELECT items.* FROM items ...查询
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实体已返回，但始终具有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数据
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
Edit我已经将其缩小到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，使其返回一个所有已联接关联都完好无损的实体？
Doctrine关于Native SQL的文档有一些很好的见解，它清楚地表明了您的错误是什么。对您现有文章的简短回答是，您应该使用addJoinedEntityResult()而不是addEntityResult()作为ItemType实体。
实体结果文件规定：
实体结果描述了在转换结果中显示为根元素的实体类型。
这意味着，如果在同一映射中添加两个实体结果，您将获得当前看到的结果-返回的Item和ItemsType都是两个不同的对象。然而，您知道这两者是相关的，所以Joined Entity Result更有意义：
联接实体结果描述了一种实体类型，该实体类型在转换的结果中显示为联接关系元素，并附加到（根）实体结果。
要直接按原样修复代码，您需要更改
$rsm->addEntityResult(''AppBundle'Entity'ItemType', 'item_types');
到此：
$rsm->addJoinedEntityResult(
''AppBundle'Entity'ItemType',
'item_types',
'items',
'itemType'
);
格式为addJoinedEntityResult($class, $alias, $parentAlias, $relation)，因此您可以看到添加到父别名的第3个和第4个参数，以及指向ItemType的Items类中的字段。
话虽如此，我认为这太复杂了，使用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']
);
这样，您就可以消除冗余代码，使其不易出错，更容易测试，并自动处理实体和数据库的更新。第二个调用显示，您仍然可以传递一个重命名列的数组。
这是因为您只从表项（items.*）中进行选择。原则使用延迟加载，而不从联接表中的任何数据中加载。
输入您选择的所有想要获得的数据，即：
SELECT items.*, item_types.* ...