Full join multiple Doctrine QueryBuilder instances

15 Views Asked by At

I created two query builder instances using a helper function like this:

public function openOrdersQueryBuilder()
{
    return $this->createQueryBuilder('order')
        ->where("order.state != 'done'")
        ->andWhere(...)
        ...;
}

$confirmedDateSummary = $this->openOrdersQueryBuilder()
    ->groupBy('summarydate')
    ->select('confirmed AS date, count(order) AS count_confirmed');
$orderedDateSummary = $this->openOrdersQueryBuilder()
    ->groupBy('summarydate')
    ->select('ordered AS summarydate, count(order) AS count_ordered');

My actual queries are a little more involved.

Now I would like to FULL JOIN those two but I don't know how. I basically have two questions.

  1. If at all possible, how can I create a third QueryBuilder that can consume the two existing ones as subqueries and JOIN them on the date columns?
  2. Neither Doctrine nor MySQL support FULL JOIN. How to emulate it? Unfortunately Doctrine also does not seem to have common table expressions.

Some raw MySQL around whatever $queryBuilder->getQuery()->getSql() produces would also be appreciated. With the help of common table expressions I tried to use a LEFT JOIN UNIONed with a RIGHT JOIN as a general workaround for MySQL missing FULL JOIN. However I ran into the problem that $queryBuilder->getQuery()->getSql() only produces column names like sclr_0, sclr_1 etc..

If 1. is possible, I presume I could achieve the same workaround without CTEs and without raw SQL at the cost of a rather bloaty query.

The question title should have made it obvious that the problem I am having is NOT a MySQL problem per se - neither SQL nor MySQL are part of the title. Apparently this was not obvious, so let me explain: I only mentioned MySQL because it is the database I have at hand. If my question can not be answered with a database-agnostic solution which would naturally be preferred, a workaround using some raw MySQL would also be appreciated. Least preferred would be a solution that only involves raw MySQL but as I pointed out above I am very much aware how to achieve this on my own. Obviously I would like to use as many benefits of the Doctrine ORM as I can instead. How to achieve this is the question.

0

There are 0 best solutions below