Doctrine Query Builder, "between" expression and subquery

Question

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 Distributor class and a DistributorVisit class with a one-to-many relationship. Every time a Distributor is visited by a representative, a new row is added to the DistributorVisit table with the visit date. An ER diagram of the two tables can be found here.

Now i want to be able to filter Distributors by their last visit date. 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 DistributorRepository class:

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

This gives me the following error:

[Syntax Error] line 0, col 83: Error: Expected Literal, got 'SELECT'

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?

Thanks a lot for your help.

See this post for some details on how to see the generated query. From there, if you haven't figured it out, please post the query here so it's easier for us to help you! :-) http://stackoverflow.com/questions/7329288/how-do-you-view-a-dql-query-prepared-query-at-runtime — mbinette, Sep 27 '12 at 03:19

score 5 · Accepted Answer · answered Nov 20 '12 at 20:43

I now resolved the issue the following way:

$qb = $this->getEntityManager()->createQueryBuilder()
    ->select('o')
    ->from('MyBundle:Distributor', 'o');

$qbdv = $this->getEntityManager()->createQueryBuilder();
$qbdv->select('MAX(dv2.visitDate)')
    ->from('MyBundle:DistributorVisit', 'dv2')
    ->where($qbdv->expr()->eq('dv2.distributor', 'o'));

$maxVisitDate = '('.$qbdv->getDQL().')';

$qb->leftJoin(
    'o.distributorVisits',
    'dv',
    Join::WITH,
    $qb->expr()->eq('dv.visitDate', $maxVisitDate)
);

$qb->andWhere(
    $qb->expr()->between(
        'dv.visitDate',
        ':dateFrom',
        ':dateTo'
    )
)
->setParameter('dateFrom', $filter->getLastVisitFrom())
->setParameter('dateTo', $filter->getLastVisitTo());

So what I basically did is the following: I joined the DistributorVisit table to the Distributor table with the maximum visit date. The trick was the fact that one can pass the DQL of a (sub-)query ($qb1->getDQL()) directly to a Doctrine expression ($qb2->expr()->eq('column', $qb1->getDQL()). I did this with the left join in the code above.

score 1 · Answer 2 · answered Sep 27 '12 at 08:16

I guess that your current DQL looks like this:

SELECT ..
FROM ..
WHERE SELECT .. FROM .. BETWEEN .. AND ..

But should look like this:

SELECT ..
FROM ..
WHERE (SELECT .. FROM ..) BETWEEN .. AND ..

To fix your code, i'd just put the subquery's dql inside parentheses:

$subQueryDQL = $qbv->select($qbv->expr()->max('v.visitDate'))
    ->from('MyBundle:DistributorVisit', 'v')
    ...
    ->getDQL();

$qb->andWhere($qb->expr()->between(
    sprintf('(%s)', $subQueryDQL),
    $filter->getLastVisitFrom()->getTimestamp(),
    $filter->getLastVisitTo()->getTimestamp()
));

I didn't actually try you solution, but it definitely goes into the right direction. Below you can see how I actually solved it, I believe both solutions are basically equal. — thomaskonrad, Nov 20 '12 at 20:47

score 0 · Answer 3 · answered Mar 06 '18 at 13:53

My case:

$qb->andWhere(
"t.field BETWEEN (
    {$subQuerybuilder1->getDQL()}
  ) AND (
    {$subQuerybuilder2->getDQL()}
  )
");

Got:

SELECT ... WHERE t.field BETWEEN (
        SELECT t1.field FROM t1
      ) AND (
        SELECT t2.field FROM t2
      )

Doctrine Query Builder, "between" expression and subquery

3 Answers3