Using JOIN in Symfony2/Doctrine SQL

Question

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.

Falsework

To answer your update, you can't just select u and r.. everything along the way to r has to also be selected. so you need select('u, p, rg, r') – Hazel

Answer 1

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

Falsework

Now you should try to learn the difference between left-join and inner-join, they are very different and innerjoins can save you tons of headaches. Simplest way; create Category has many Products relation. When fetching categories, do innerJoin of products; you will get only categories that actually have products in it. Really cool thing, specially when you combine it with "WITH" clause. – Gerik

No problem. I already know the difference between Inner and Left joins. – Falsework

Answer 2

In your DQL, you are fetching users but you asked how to fetch routes. What actually you need?

Anyway, in 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.

Gerik

Hi, I tried your solution but got another problem. I updated the question above. Can you take a look, please? Thanks – Falsework

Answer 3

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.

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.

I hope this helps out.

Loireatlantique