title: Doctrine Query Builder, "between" expression and subquery
body: <p>I'm trying to create a quite complex query with Doctrine's Query Builder (I'm using Doctrine 2.2). In the model, i have a <code>Distributor</code> class and a <code>DistributorVisit</code> class with a one-to-many relationship. Every time a Distributor is visited by a representative, a new row is added to the <code>DistributorVisit</code> table with the visit date. An ER diagram of the two tables can be found <a href="http://i.stack.imgur.com/qJGK2.png" rel="nofollow">here</a>.</p>
  
  <p>Now i want to be able to filter Distributors by their <strong>last visit date</strong>. So the user enters a date range (last visit from and last visit to) and Distributors whose last visit date is between those two dates are listed. I'm using Doctrine's Query Builder because there are a lot of other conditional queries I do in the filter, and I found the object-oriented approach to work best in this scenario. Here's what i did in the <code>DistributorRepository</code> class:</p>
  
  <pre><code>$qb = $this-&gt;getEntityManager()-&gt;createQueryBuilder()
       -&gt;select('o')
       -&gt;from('MyBundle:Distributor', 'o');
  
  // Lots of 'andWhere's here
  
  $qbv = $this-&gt;getEntityManager()-&gt;createQueryBuilder();
  
  $qb-&gt;andWhere($qb-&gt;expr()-&gt;between(
  
      $qbv-&gt;select($qbv-&gt;expr()-&gt;max('v.visitDate'))
          -&gt;from('MyBundle:DistributorVisit', 'v')
          -&gt;join('MyBundle:Distributor', 'o2',
              Join::WITH,
              $qbv-&gt;expr()-&gt;andX(
                  $qbv-&gt;expr()-&gt;eq('o2.id', 'v.distributorId'),
                  $qbv-&gt;expr()-&gt;eq('o2.id', 'o.id')
              ))
          -&gt;getDQL(),
  
      $filter-&gt;getLastVisitFrom()-&gt;getTimestamp(),
      $filter-&gt;getLastVisitTo()-&gt;getTimestamp()
  ));
  </code></pre>
  
  <p>This gives me the following error:</p>
  
  <pre><code>[Syntax Error] line 0, col 83: Error: Expected Literal, got 'SELECT'
  </code></pre>
  
  <p>I guess this is because the Query Builder expects a literal where my sub-select is, however, the result of the sub-query should be a literal, right? May it be because the Query Builder does not add parenthesis accordingly?</p>
  
  <p>Thanks a lot for your help.</p>