title: Doctrine : Pagination with left Joins
body: <p>I would like to paginate a complex request with at least 2 left joins, but the pagination bundle I'm using (KnpPaginationBundle) can't tell Doctrine how to count the result (<a href="http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/pagination.html" rel="noreferrer">which is needed for the pagination process</a>), and keep having this Exception.</p>
  
  <p><code>Cannot count query which selects two FROM components, cannot make distinction</code></p>
  
  <p>Here is a sample request built with the Doctrine QueryBuilder.</p>
  
  <pre class="lang-php prettyprint-override"><code>public function findGroupsByUser(User $user, $listFilter, $getQuery = false, $order = 'ASC')
  {
      $query = $this-&gt;createQueryBuilder('r')
          -&gt;select('r as main,g')
          -&gt;select('r as main,g, count(gg) as members')
          -&gt;leftjoin('r.group', 'g')
          -&gt;innerjoin('MyBundle:GroupMemberRel', 'gg', 'WITH', 'r.group = gg.group')
          -&gt;addGroupBy('g.groupId')
          -&gt;add('orderBy', 'g.name ' . $order);
     if ($getQuery == true) {
          return $query;
      }
  
      return $query-&gt;getQuery()-&gt;getResult();
  }
  </code></pre>
  
  <p>Then I give this request to the knp_paginator service, and then I've got the exception</p>
  
  <pre class="lang-php prettyprint-override"><code>    $groupQuery = $this-&gt;em-&gt;getRepository('MyBundle:GroupMemberRel')-&gt;findGroupsByUser($user, $listFilter, true);
      $paginator = $this-&gt;container-&gt;get('knp_paginator');
      /* @var $groups Knp\Component\Pager\Pagination\PaginationInterface */
      $groups = $paginator-&gt;paginate(
          $groupQuery, $this-&gt;container-&gt;get('request')-&gt;query-&gt;get('page', 1), 10 /* limit per page */
      );
  </code></pre>
  
  <p>Any idea on how to paginate over a complex request, I'm pretty sure this use-case is common, don't want to hydrate my result after the pagination.</p>