在 Symfony2/Doctrine SQL 中使用 JOIN

2022-01-16 00:00:00 php symfony doctrine query-builder dql

我在尝试使用 QueryBuilder 或 DQL 时遇到问题.

I have a problem while trying to USE QueryBuilder OR DQL.

我有以下关系:

用户 <-1:n-> 配置文件 <-n:m-> RouteGroup <-1:n-> Route

User <-1:n-> Profile <-n:m-> RouteGroup <-1:n-> Route

我想制作一个 DQL，列出特定用户可以访问的所有路由.我可以使用以下代码获取此信息:

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.

我尝试了以下方法:

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 - 关系的名称而不是实体的名称 - 但这也不起作用):

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();

有人可以帮忙吗???

更新:我尝试了 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();

但我得到了这个错误:

The parent object of entity result with alias 'r' was not found. The parent alias is 'rg'.

如果我将->select('u, r')"更改为->select('r')"，我会得到:

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();

相关文章