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.
- If at all possible, how can I create a third
QueryBuilderthat can consume the two existing ones as subqueries and JOIN them on the date columns? - 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.