title: Using JOIN in Symfony2/Doctrine SQL
body: <p>I have a problem while trying to USE QueryBuilder OR DQL. </p>
  
  <p>I have the following relation:</p>
  
  <p>User &lt;-1:n-> Profile &lt;-n:m-> RouteGroup &lt;-1:n-> Route</p>
  
  <p>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:</p>
  
  <pre><code>$usr = $this-&gt;container-&gt;get('security.context')-&gt;getToken()-&gt;getUser();
  foreach ($usr-&gt;getProfiles() as $profile){
      foreach ($profile-&gt;getRoutegroups() as $routegroup){
          var_dump($routegroup-&gt;getRoutes()-&gt;toArray());
       }
  }
  </code></pre>
  
  <p>For obvious reason i cant use this code, otherwise I will overload my server, LOL.</p>
  
  <p>I tried the following approaches:</p>
  
  <p>DQL:</p>
  
  <pre><code>$em-&gt;createQuery('SELECT p FROM CRMCoreBundle:User u
                    JOIN CRMCoreBundle:Profile p
                    JOIN CRMCoreBundle:RoleGroup rg
                    JOIN CRMCoreBundle:Role r
                    WHERE
                      u.id=:user')
          -&gt;setParameter('user', $user-&gt;getId())
          -&gt;getResult();
  </code></pre>
  
  <p>QueryBuilder (i tried using u.profiles - the name of the relationship instead of the entity - but this did not work also):</p>
  
  <pre><code>$em-&gt;createQueryBuilder()
          -&gt;select('r')
          -&gt;from('CRMCoreBundle:User', 'u')
          -&gt;innerJoin('u.profiles','p')
          -&gt;where('u.id = :user_id')
          -&gt;setParameter('user_id', $user-&gt;getId())
          -&gt;getQuery()
          -&gt;getResult();
  </code></pre>
  
  <p>Can someone help please???</p>
  
  <p><strong>UPDATE: I tried Zeljko's solution and made this script:</strong></p>
  
  <pre><code>    return $this-&gt;getEntityManager()
          -&gt;createQueryBuilder()
          -&gt;select('u, r')
          -&gt;from('CRMCoreBundle:User', 'u')
          -&gt;innerJoin('u.profiles','p')
          -&gt;innerJoin('p.routegroups','rg')
          -&gt;innerJoin('rg.routes','r')
          -&gt;where('u.id = :user_id')-&gt;setParameter('user_id', $user-&gt;getId())
          -&gt;getQuery()
          -&gt;getResult();
  </code></pre>
  
  <p>But i got this error: </p>
  
  <pre><code>The parent object of entity result with alias 'r' was not found. The parent alias is 'rg'.
  </code></pre>
  
  <p>If i change "->select('u, r')" to "->select('r')" i get this:</p>
  
  <pre><code>[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.
  </code></pre>