php 在 Symfony2/Doctrine SQL 中使用 JOIN
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13354401/
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
Using JOIN in Symfony2/Doctrine SQL
提问by Marcelo Diotto
I have a problem while trying to USE QueryBuilder OR DQL.
我在尝试使用 QueryBuilder OR DQL 时遇到问题。
I have the following relation:
我有以下关系：
User <-1:n-> Profile <-n:m-> RouteGroup <-1:n-> Route
用户 <-1:n-> 配置文件 <-n:m-> RouteGroup <-1:n-> 路由
I would like to make a DQL that lists all the routes that a specific user has access. I can get this information with the following code:
我想制作一个 DQL，列出特定用户有权访问的所有路由。我可以使用以下代码获取此信息：
$usr = $this->container->get('security.context')->getToken()->getUser();
foreach ($usr->getProfiles() as $profile){
foreach ($profile->getRoutegroups() as $routegroup){
var_dump($routegroup->getRoutes()->toArray());
}
}
For obvious reason i cant use this code, otherwise I will overload my server, LOL.
出于显而易见的原因，我不能使用此代码，否则我会超载我的服务器，哈哈。
I tried the following approaches:
我尝试了以下方法：
DQL:
数据质量：
$em->createQuery('SELECT p FROM CRMCoreBundle:User u
JOIN CRMCoreBundle:Profile p
JOIN CRMCoreBundle:RoleGroup rg
JOIN CRMCoreBundle:Role r
WHERE
u.id=:user')
->setParameter('user', $user->getId())
->getResult();
QueryBuilder (i tried using u.profiles - the name of the relationship instead of the entity - but this did not work also):
QueryBuilder（我尝试使用 u.profiles - 关系的名称而不是实体 - 但这也不起作用）：
$em->createQueryBuilder()
->select('r')
->from('CRMCoreBundle:User', 'u')
->innerJoin('u.profiles','p')
->where('u.id = :user_id')
->setParameter('user_id', $user->getId())
->getQuery()
->getResult();
Can someone help please???
有人可以帮忙吗？？？
UPDATE: I tried Zeljko's solution and made this script:
更新：我尝试了 Zeljko 的解决方案并制作了这个脚本：
return $this->getEntityManager()
->createQueryBuilder()
->select('u, r')
->from('CRMCoreBundle:User', 'u')
->innerJoin('u.profiles','p')
->innerJoin('p.routegroups','rg')
->innerJoin('rg.routes','r')
->where('u.id = :user_id')->setParameter('user_id', $user->getId())
->getQuery()
->getResult();
But i got this error:
但我收到了这个错误：
The parent object of entity result with alias 'r' was not found. The parent alias is 'rg'.
If i change "->select('u, r')" to "->select('r')" i get this:
如果我将 "->select('u, r')" 更改为 "->select('r')" 我得到这个：
[Semantical Error] line 0, col -1 near 'SELECT r FROM': Error: Cannot select entity through identification variables without choosing at least one root entity alias.
回答by Marcelo Diotto
After trying some alternatives I found out that I could make an inverse lookup, starting from routes to users. The solution was as follows:
在尝试了一些替代方案后，我发现我可以进行反向查找，从路由到用户开始。解决方法如下：
return $this->getEntityManager()
->createQueryBuilder()
->select('r')
->from('CRMCoreBundle:Route', 'r')
->innerJoin('r.routegroup','rg')
->innerJoin('rg.profiles','p')
->innerJoin('p.users','u')
->where('u.id = :user_id')
->setParameter('user_id', $user->getId())
->getQuery()
->getResult();
回答by Zeljko
In your DQL, you are fetching users but you asked how to fetch routes. What actually you need?
在您的 DQL 中，您正在获取用户，但您询问了如何获取路由。你究竟需要什么？
Anyway, in RoutesRepository:
无论如何，在 RoutesRepository 中：
$this->createQueryBuilder("r")
->innerJoin("r.Profiles", "p")
->innerJoin("p.User", "u")
->where("u=:user")->setParameter("user", $user)
I might not understand the relation but I think you can change this to reflect your code. You must use innerJoin, not leftJoin.
我可能不理解这种关系，但我认为您可以更改它以反映您的代码。您必须使用innerJoin，而不是leftJoin。
回答by Damien Whaley
I'm not an expert with Doctrine, but just solved a very similar problem. I fixed my problem by including all the entities that you were using in the joins in the SELECT part of the statement.
我不是 Doctrine 的专家，但刚刚解决了一个非常相似的问题。我通过在语句的 SELECT 部分的连接中包含您使用的所有实体来解决我的问题。
I have not tested this, but this should work.
我没有测试过这个，但这应该有效。
$em->createQuery('SELECT u, p, rg, r FROM CRMCoreBundle:User u
JOIN CRMCoreBundle:Profile p
JOIN CRMCoreBundle:RoleGroup rg
JOIN CRMCoreBundle:Role r
WHERE
u.id=:user')
->setParameter('user', $user->getId())
->getResult();
I don't know exactly why, but if you don't include the entities then the hydrator does not know about the aliases that you are using for the entities.
我不知道确切的原因，但是如果您不包括实体，那么 hydrator 就不会知道您为实体使用的别名。
I hope this helps out.
我希望这会有所帮助。
