使用双连接优化DQL [英] Optimize DQL with double join

问题描述

我有这样一个结构：单一个人资料可以参加几项研究，而对于每项研究，法规字段是 study_statute 。在每一行的HTML中，我需要显示个人资料：profile.name，profile.study.field，profile.study.statute。我已经使用

  $ profileRepository-> findAll（）
  / pre> 
 
 
，但它会生成许多查询，我想优化。目前有查询提取配置文件，每个配置文件要求进行研究，并为每个研究请求法定名称。所以如果有2个资料，每个个人资料都会出现在2个研究中，我有一个
 
 
 1（list）
 +1（个人资料＃1的学习列表）
 +2（以上每项研究的学习名称）
 +1（个人资料＃2的学习列表）
 +2（以上每项研究的学习名称）
查询。 p> 
 
 
如何优化？
 
 
 
在简单的PHP中，我会做：为所有配置文件加入研究获取大表并研究名称并将其解析为多维数组，fe  $ profile [1] ['研究'] [1] ['name'] 。
解决方案
您可以使用dql  fetch join ：
  $ profiles = $ em-> createQuery（'select p，stu，sta from YourBundleName：Profile p join p.studies stu join stu.statute sta'）
  - > getResult（）; 
  
结果将与使用 findAll 除了一件事。所有相关实体都将被提取。如果您访问学习或法规，教义不需要懒惰加载与另一个查询的关联。 
 
I have such a structure: single profile can attend do several studies and for each study there is statute field which is foreign key to study_statute. In HTML in each row I need to show information for profile: profile.name, profile.study.field, profile.study.statute. I've done this using 
$profileRepository->findAll()
but it generates to many queries and I want to optimize. At the moment there is query fetching profiles , for each profile ask for studies and for each study ask for statute name. So if there are 2 profiles and each profile attends to 2 studies I have 

1 (list)
+1 (study list for profile #1)
+2 (study names for each of above studies)
+1 (study list for profile #2)
+2 (study names for each of above studies)
queries.

How to optimize this?

In plain PHP I'd do: fetch big table for all profiles joined with studies and study names and parse it to multidimensional arrays, f.e. $profile[1]['studies'][1]['name'].
解决方案
You can use dql fetch join:
$profiles = $em->createQuery('select p, stu, sta from YourBundleName:Profile p join p.studies stu join stu.statute sta')
    ->getResult();
The result would be the same as if you would use findAll except one thing. All related entities would have been fetched. If you access the "study" or "statute" Doctrine does not need to lazy load the association with another query. 

                        
这篇关于使用双连接优化DQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！