💡 Support QueryBuilder CTE (Common Table Expression)

[dbannik]

Feature Request

After Merged support CTE in doctrine/dbal
doctrine/dbal#6621

Is there a plan to release this mechanism in ORM/QueryBuilder?

Example

$avgOrderCte = $this->entityManager->createQueryBuilder()
    ->select('AVG(order.total) as total')
    ->from(Order::class, 'order')
    ->where('order.status > :status')
    ->andWhere('order.createdAt > :date')
    ->groupBy('item.order')
    ->setParameter('date', new DateTimeImmutable())
    ->setParameter('stats', [1, 2])
;

$this->entityManager->createQueryBuilder()
    ->with('avg_order', $avgOrderCte) #<-- inject CTE
    ->from(Order::class, 'order')
    ->select('order.id, order.total')
    ->innerJoin('avg_order', 'avg_order', Join::WITH, 'order.total > avg_order.total') #<-- using CTE
    ->orderBy('order.total', 'DESC')
    ->getQuery()
    ->getResult()
;

[stof]

DQL does not have a concept of CTE in its syntax.