StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PODoctrine Query Builder, "between" expression and subquery
text
Body
copied!<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->getEntityManager()->createQueryBuilder() ->select('o') ->from('MyBundle:Distributor', 'o'); // Lots of 'andWhere's here $qbv = $this->getEntityManager()->createQueryBuilder(); $qb->andWhere($qb->expr()->between( $qbv->select($qbv->expr()->max('v.visitDate')) ->from('MyBundle:DistributorVisit', 'v') ->join('MyBundle:Distributor', 'o2', Join::WITH, $qbv->expr()->andX( $qbv->expr()->eq('o2.id', 'v.distributorId'), $qbv->expr()->eq('o2.id', 'o.id') )) ->getDQL(), $filter->getLastVisitFrom()->getTimestamp(), $filter->getLastVisitTo()->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>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload