php 使用过多的 SQL 查询，doctrine2 加载具有急切获取模式的一对多关联
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9020433/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
doctrine2 loads one-to-many associations with fetch mode eager using too many SQL queries
提问by ausi
I am loading a list of many entities.
These entities have a one-to-many association to other entities.
I want to load all these other entities in one single SQL query (instead of one query for every entity in the first list).
我正在加载许多实体的列表。
这些实体与其他实体具有一对多关联。
我想在一个 SQL 查询中加载所有这些其他实体（而不是对第一个列表中的每个实体进行一个查询）。
As discribed in the doctrine2 documentation: http://www.doctrine-project.org/docs/orm/2.1/en/reference/dql-doctrine-query-language.html#temporarily-change-fetch-mode-in-dqlthis should be possible with "EAGER" loading.
如doctrine2文档中所述：http: //www.doctrine-project.org/docs/orm/2.1/en/reference/dql-doctrine-query-language.html#temporously-change-fetch-mode-in-dql这应该可以通过“EAGER”加载来实现。
but it does not work as described.
但它不像描述的那样工作。
my code:
我的代码：
class User{
/**
* @ORM\OneToMany(targetEntity="Address", mappedBy="user", indexBy="id", fetch="EAGER")
*/
protected $addresses;
public function __construct(){
$this->addresses = new ArrayCollection();
}
}
class Address{
/**
* @ORM\ManyToOne(targetEntity="User", inversedBy="addresses")
* @ORM\JoinColumns({
* @ORM\JoinColumn(name="UserId", referencedColumnName="id")
* })
*/
private $user;
}
class UserRepository{
public function findUsersWithAddresses(){
return $this->getEntityManager()
->createQuery('SELECT u FROM MyBundle:User u ORDER BY u.name ASC')
->setFetchMode('MyBundle\Entity\User', 'addresses', \Doctrine\ORM\Mapping\ClassMetadata::FETCH_EAGER)
->setMaxResults(10)
->getResult();
}
}
The method UserRepository::findUsersWithAddresses() executes 11 SQL Queries.
UserRepository::findUsersWithAddresses() 方法执行 11 个 SQL 查询。
How can I tell Doctrine to use only one SQL Query to load the address entities?
如何告诉 Doctrine 只使用一个 SQL 查询来加载地址实体？
I am using:
我在用：
- symfony v2.0.9
- doctrine-common 2.1.4
- doctrine-dbal 2.1.5
- doctrine 2.1.5
- symfony v2.0.9
- 共同原则 2.1.4
- 学说-dbal 2.1.5
- 学说 2.1.5
采纳答案by ausi
The current version of doctrine doesn't support this.
当前版本的学说不支持这一点。
There is a feature requestabout this in the doctrine2 issue tracker.
在doctrine2 问题跟踪器中有一个关于此的功能请求。
So I hope it will be implemented soon.
所以我希望它很快就会实施。
回答by Benjamin
According to your link:
You can mark a many-to-one or one-to-oneassociation as fetched temporarily to batch fetch these entities using a WHERE .. IN query
It looks like the current version of Doctrine does not support eager loading on a one-to-manycollection, unfortunately.
This pageseems to confirm this supposition:
@OneToMany
Required attributes:
targetEntity: FQCN of the referenced target entity. Can be the unqualified class name if both classes are in the same namespace. IMPORTANT: No leading backslash!
Optional attributes:
- cascade: Cascade Option
- orphanRemoval: Boolean that specifies if orphans, inverse OneToOne entities that are not connected to any owning instance, should be removed by Doctrine. Defaults to false.
- mappedBy: This option specifies the property name on the targetEntity that is the owning side of this relation. Its a required attribute for the inverse side of a relationship.
The @OneToManyannotation does not feature a fetchattribute, as opposed to @OneToOneand @ManyToOne.
根据你的链接：
您可以将多对一或一对一关联标记为临时获取，以使用 WHERE .. IN 查询批量获取这些实体
不幸的是，看起来当前版本的 Doctrine 不支持对一对多集合进行预先加载。
这个页面似乎证实了这个假设：
@一对多
必需的属性：
targetEntity：引用的目标实体的 FQCN。如果两个类都在同一个命名空间中，则可以是非限定类名。重要提示：没有前导反斜杠！
可选属性：
- 级联：级联选项
- orphanRemoval：布尔值，指定是否应由 Doctrine 删除孤儿，即未连接到任何拥有实例的逆 OneToOne 实体。默认为假。
- mappingBy：此选项指定作为此关系拥有方的 targetEntity 上的属性名称。它是关系反面的必需属性。
该@OneToMany批注不设有一个fetch属性，而不是@OneToOne和@ManyToOne。
Update
更新
I just noticed that you canactually eager fetch the related entities, using an explicit LEFT JOINin DQL:
我刚刚注意到，您实际上可以使用LEFT JOINDQL 中的显式快速获取相关实体：
SELECT u, a FROM User u
LEFT JOIN u.addresses a
Do use a LEFT JOIN, and not an inner JOIN, or entities with an empty collection (Userwithout any Address) would be omitted from the result set.
请使用 a LEFT JOIN，而不是内部JOIN，否则结果集中会省略具有空集合（User没有任何Address）的实体。
Update (2017)
更新 (2017)
As pointed by GusDeCooland webDEVILopersin the comments, the fetchattribute is now supportedon @OneToMany. The above answer is now obsolete.
正如GusDeCool和webDEVILopers在评论中指出的那样，该fetch属性现在在@OneToMany. 上面的答案现在已经过时了。
