QueryBuilder with CTE

QueryBuilderCteTrait.php

<?php

namespace Data;


use Data\CampaignData\Query\AssignmentQueryBuilder;

trait QueryBuilderCteTrait
{
    protected $ctes = [];

    public function with($expression, $alias)
    {
        if ($expression instanceof QueryBuilder || $expression instanceof AssignmentQueryBuilder) {
            $query = $expression->getSQL();
            $params = $expression->getParameters();
            $paramTypes = $expression->getParameterTypes();
            $paramMap = [];
            foreach ($params as $key => $value) {
                $type = isset($paramTypes[$key]) ? $paramTypes[$key] : \PDO::PARAM_STR;
                $newKey = uniqid("replace_", true);
                $query = str_replace(":$key", $newKey, $query);
                $paramMap[$newKey] = $this->createNamedParameter($value, $type);
            }
            if (!empty($paramMap)) {
                $query = str_replace(array_keys($paramMap), array_values($paramMap), $query);
            }

            echo null;

            $this->ctes[] = [
                "expression" => $query,
                "alias" => $alias
            ];
        } else {
            $this->ctes[] = [
                "expression" => $expression,
                "alias" => $alias
            ];
        }
        return $this;
    }

    public function getSQL()
    {
        if (empty($this->getQueryPart('from'))) {
            $this->from('(SELECT \'X\' as dummy)', 'dual');
        }
        $sql = parent::getSQL();

        if (!empty($this->ctes)) {
            $sql = $this->getWithSQL() . $sql;
        }

        return $sql;
    }

    public function getCtes()
    {
        return $this->ctes;
    }

    public function getWithSQL()
    {
        return "WITH " . implode(
            ",\n",
            array_map(
                function ($cte) {
                    return $cte['alias'] . " AS (" . $cte["expression"] . ")";
                },
                $this->ctes
            )
        ) . "\n";
    }
}