Building SQL queries using CTE with Doctrine DBAL

ComplexSqlBuilder.php

<?php

namespace Incenteev\WebBundle\Doctrine;

class ComplexSqlBuilder
{
    /**
     * @param string   $mainSql
     * @param string[] $cteQueries
     * @param bool     $recursive
     *
     * @return string
     */
    public function buildSql(string $mainSql, array $cteQueries, bool $recursive = false): string
    {
        if (empty($cteQueries)) {
            return $mainSql;
        }

        $ctes = array();

        foreach ($cteQueries as $alias => $sqlQuery) {
            $ctes[] = sprintf('%s AS (%s)', $alias, $sqlQuery);
        }

        if ($recursive) {
            return sprintf("WITH RECURSIVE %s\n%s", implode(",\n", $ctes), $mainSql);
        }

        return sprintf("WITH %s\n%s", implode(",\n", $ctes), $mainSql);
    }
}

usage.php

<?php

$cteQueries = [];
$params = [];
$paramTypes = [];

$firstQb = $connection->createQueryBuilder();
// Build SQL query.
// Use $params and $paramTypes to set parameters rather than $qb->setParameter(), as we want to build a single
// list of parameters

$cteQueries['first'] = $firstQb->getSql();


$secondQb = $connection->createQueryBuilder();
// Build SQL query. It can reference "first" as a CTE can reference previous CTEs.
$cteQueries['second'] = $secondQb->getSql();


$qb = $connection->createQueryBuilder();
// build the main SQL query

$sql = (new ComplexSqlBuilder)->buildSql($qb->getSql(), $cteQueries);

$connection->fetchAll($sql, $params, $paramTypes);
// Or create an ORM native query.