在Symfony2 / Doctrine SQL中使用JOIN [英] Using JOIN in Symfony2/Doctrine SQL
问题描述
我有以下关系:
用户< -1:n->个人资料:m-> RouteGroup< -1:n-> Route
我想制作一个列出特定用户可以访问的所有路由的DQL。
我可以使用以下代码获取此信息:
$ usr = $ this-> container->得到( 'security.context') - >为gettoken() - >的getUser();
foreach($ usr-> getProfiles()as $ profile){
foreach($ profile-> getRoutegroups()as $ routegroup){
var_dump($ routegroup-> getRoutes () - >指定者());
}
}
出于明显的原因,我不能使用这个代码,否则我将超载我的服务器,LOL。
我尝试了以下方法:
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(我尝试使用u.profiles - 关系的名称而不是实体 - 但这没有工作):
$ em-> createQueryBuilder()
- > select('r' )
- > from('CRMCoreBundle:User','u')
- > innerJoin('u.profiles','p')
- >其中('u 。= = user_id')
- > setParameter('user_id',$ user-> getId())
- > getQuery()
- > getResult()
有人可以帮助吗?
更新:我尝试了Zeljko的解决方案,并制作了这个脚本:
return $ this-> getEntityManager ()
- > createQueryBuilder()
- > select('u,r')
- >来自('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();
但是我收到这个错误:
未找到别名为r的实体结果的父对象。父别名是rg。
如果我更改 - >选择('u,r')到 - > select 'r')我得到这个:
[语义错误]行0,col -1靠近'SELECT r FROM':错误:在不选择至少一个根实体别名的情况下,不能通过识别变量来选择实体。
在尝试了一些替代方案后,我发现我可以从路由到用户的反向查找。解决方案如下:
return $ this-> getEntityManager()
- > createQueryBuilder()
- > select('r')
- > from('CRMCoreBundle:Route','r')
- > innerJoin('r.routegroup','rg')
- > innerJoin('rg.profiles','p')
- > innerJoin('p.users','u')
- >其中('u。 id =:user_id')
- > setParameter('user_id',$ user-> getId())
- > getQuery()
- > getResult()
I have a problem while trying to USE QueryBuilder OR DQL.
I have the following relation:
User <-1:n-> Profile <-n:m-> RouteGroup <-1:n-> Route
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:
$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):
$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:
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:
[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.
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();
这篇关于在Symfony2 / Doctrine SQL中使用JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!