Using JOIN in Symfony2/Doctrine SQL

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

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.

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.