Как в Yii2 с помощью yii\db\Query создать запрос где используется WITH RECURSIVE перед SELECT?

Question

[Антон Артёмов]

Здравствуйте. Есть ли простое решение, как можно в Yii2, применяя конструктор запросов yii\db\Query, создать запрос, где используется WITH RECURSIVE перед SELECT (PostgreSQL)?
Желательно, что бы запрос, который используется внутри WITH тоже был объектом yii\db\Query.
Пример результата:

WITH RECURSIVE r AS (
	SELECT "ID" AS "id" FROM "REF_ZONES" WHERE "IS_ACTIVE" = 1 AND "ID" = '63b1963b-212d-4...a3af03ee8' 
	UNION 
	SELECT "REF_ZONES"."ID" FROM "REF_ZONES" JOIN r ON "REF_ZONES"."PARENT" = r."id" WHERE "REF_ZONES"."IS_ACTIVE" = 1
) 
SELECT c.*, cc."ID" AS "ON_CONTROL", COUNT(distinct al_act."ID")::int4 AS "NS_ACTUAL", ...
FROM counters_monitor c 
LEFT JOIN "REG_ABONENT_COUNTERS_CONTROL" cc ON cc."COUNTER" = c."ID" AND cc."ABONENT" = 'dc0ccee3-cdf8...b725a4f2' 
LEFT JOIN "REG_ALERTS" al_closed ON al_closed."OBJECT_ID" = c."ID" AND al_closed."OBJECT" = 2 AND al_closed."IS_ACTUAL" IS FALSE  
INNER JOIN r ON r.id = c."ZONES" 
WHERE ...
GROUP BY c."ID",...,cc."ID"

Было бы здорово, что то типа такого:

<?php
$qCore = new Query();
$qCore->select([/*...*/]);
$qCore->from([/*...*/]);
$qCore->where([/*...*/]);

if(/*какие то условия по алгоритму*/){
     $qWith = new Query();
     $qWith->select([/*...*/]);
     $qWith->from([/*...*/]);

     $qUnion = new Query();
     $qUnion->/*...*/;

     $qWith->union($qUnion);

     $qCore->with($qWith, true, 'r'); // Вот то, чего не хватает
     $qCore->innerJoin(['r'],'r.id = c."ZONES"');
     $qCore->addSelect([...]);
}

Answer 1

[Антон Артёмов]

Переопределил Query... и вроде даже работает...

<?php

namespace common\components;

use Yii;

/**
 * Extend for yii\db\Query class
 *
 * For example,
 *
 * ```php
 * $query = new Query;
 * // compose the query
 * $query->select('id, name')
 *     ->from('user')
 *     ->limit(10)
 *     ->with('SELECT ...',true,'r')
 *     ->innerJoin('r','r."ID" = c."OWNER"');
 * $rows = $query->all();
 * // alternatively, you can create DB command and execute it
 * $command = $query->createCommand();
 * // $command->sql returns the actual SQL
 * $rows = $command->queryAll();
 * ```
 *
 * @author Artyomov Anton <art...ail.com>
 */
class Query extends \yii\db\Query
{

    private $with;
    private $is_recursive;
    private $alias;

    /**
     * Adding sql expression before select operator
     * @param yii\db\Query $w
     * @param boolean $is_recursive
     * @param string $alias
     * @return $this
     */
    public function with($w, $is_recursive = true, $alias = 'r')
    {
        $this->is_recursive = $is_recursive;
        $this->alias        = $alias;

        if ($w instanceof yii\db\Query) {
            $this->with = $w->createCommand()->rawSql;
        } else {
            $this->with = $w;
        }

        return $this;
    }

    /**
     * Creates a DB command that can be used to execute this query.
     * @param Connection $db the database connection used to generate the SQL statement.
     * If this parameter is not given, the `db` application component will be used.
     * @return Command the created DB command instance.
     */
    public function createCommand($db = null)
    {
        if ($db === null) {
            $db = Yii::$app->getDb();
        }
        list($sql, $params) = $db->getQueryBuilder()->build($this);

        if (!empty($this->with)) {
            $sql = 'WITH ' . ( $this->is_recursive ? 'RECURSIVE ' : '')
                    . $this->alias . ' AS (' . $this->with . ') ' . $sql;
        }

        return $db->createCommand($sql, $params);
    }

    public function count($q = '*', $db = null)
    {
        if (!empty($this->with)) {

            if ($db === null) {
                $db = Yii::$app->getDb();
            }
            list($sql, $params) = $db->getQueryBuilder()->build($this);
            
            $sql = 'WITH ' . ( $this->is_recursive ? 'RECURSIVE ' : '')
                    . $this->alias . ' AS (' . $this->with . ') SELECT COUNT(*) FROM (' . $sql . ') c' ;
            
            return $db->createCommand($sql, $params)->queryScalar();
        }

        return $this->queryScalar("COUNT($q)", $db);
    }

}

Answer 2

[profaller]

Зачем конструктор напрягать, пишите сразу запрос целиком в yii\db\Command.

Comments

[Антон Артёмов]

В коде куча условий, от которых зависит структура запроса. Изначально я так и делал. Писал куски SQL в переменные, а по ходу условий кода склеивал эти переменные по разному. Это не феншуй.