Oracle hierarchical queries in Yii 2 Query

Oracle Hierarchical Queries (START WITH ... CONNECT BY ...) are great for selecting tree-like views using single SQL query and still be able to sort them as intended. Things are getting complicated when We trying to use them in framework Query Builder, like this one in Yii 2.

By design “query builder allows you to construct a SQL query in a programmatic and DBMS-agnostic way”^src and “specific SQL constructions will not be supported”^src.

But this can be done! Moreover we still can use Query. The whole trick is to dump SQL query generated by Query, extend it with our custom Oracle queries and run it like raw SQL in Yii 2 DAO.

$query = new Query();
// ...
list ($sql, $params) = \Yii::$app->getDb()->getQueryBuilder()->build($query);
$sql .= <<<SQL
    START WITH parent_id IS NULL
    CONNECT BY PRIOR id = parent_id
    ORDER SIBLINGS BY size ASC
SQL;

$command = \Yii::$app->db
	->createCommand($sql)
	->bindValues($params);

$data = $command->queryAll();

Use framework they said… it will be easier, they said… 😉