SqlDataProvider with CTE (SQL Server 2005-2008)

What steps will reproduce the problem?

CTE supported in SQL Server since SQL Server 2005. I use SQL Server 2008 and used CTE to provide data. My sqlDataProvider looks like this.

                    $data = new SqlDataProvider([
                        'sql' => "
                            WITH data AS
                            (
                                SELECT * FROM table
                            )
                            SELECT column1, column2, SUM(column3) AS column3 FROM data WHERE column1 = :column1 GROUP BY column1, column2
                        ",
                        'params' => [
                            ':column1' => $this->column1
                        ],
                        'totalCount' => 9999,
                        //'sort' =>false, to remove the table header sorting
                        'pagination' => [
                            'pageSize' => 50,
                        ],
                    ]);

Code above works fine with SQL Server 2012 and above, but failed at SQL Server 2005 and SQL Server 2008 because it return this query.

SELECT TOP 50 * FROM (
                            WITH data AS
                            (
                                SELECT * FROM table
                            )
                            SELECT column1, column2, SUM(column3) AS column3 FROM data WHERE column1 = 1 GROUP BY column1, column2
)sub

What is the expected result?

SqlDataProvider should return proper query like this


WITH data AS
(
    SELECT * FROM table
)
SELECT TOP 50 * FROM (
    SELECT column1, column2, SUM(column3) AS column3 FROM data WHERE column1 = 1 GROUP BY column1, column2
)sub

What do you get instead?

SELECT TOP 50 * FROM (
    WITH data AS
    (
        SELECT * FROM table
    )
    SELECT column1, column2, SUM(column3) AS column3 FROM data WHERE column1 = 1 GROUP BY column1, column2
)sub

Additional info

Q	A
Yii version	dev-master
PHP version	7.3
Operating system	Windows

@hoaaah Do you have ideas how to detect correct select query for create pagination in complex queries without human?

For example

WITH data AS
  (
  SELECT * FROM table
  )
  SELECT column1, column2, SUM(column3) AS column3  FROM data 
  OUTER APPLY (SELECT * from ... ) 
  WHERE column1 = :column1 GROUP BY column1, column

WITH data AS
  (
  SELECT * FROM table
  )
  SELECT column1, column2, SUM(column3) AS column3 ,
    (SELECT count(*) from ...) as cntColumn 
  FROM data 
  WHERE column1 = :column1 GROUP BY column1, column

Me not have ideas (

@darkdef my temporary workaround are disable limit when query contains CTE. I know it's bad idea, but in my case every query with CTE didn't have a lot of returned row (max 1000). I change QueryBuilder class https://github.com/yiisoft/yii2/blob/9aa46136f6496b8369954e8110b33b0575a9b65c/framework/db/mssql/QueryBuilder.php#L113-L133

So oldBuilderOrderByAndLimit method be like this

protected function oldBuildOrderByAndLimit($sql, $orderBy, $limit, $offset)
{
    $orderBy = $this->buildOrderBy($orderBy);
    if ($orderBy === '') {
        // ROW_NUMBER() requires an ORDER BY clause
        $orderBy = 'ORDER BY (SELECT NULL)';
    }

    $sql = preg_replace('/^([\s(])*SELECT(\s+DISTINCT)?(?!\s*TOP\s*\()/i', "\\1SELECT\\2 rowNum = ROW_NUMBER() over ($orderBy),", $sql);

    $isCte = str_contains($sql, 'WITH');
    // if cte disable $limit
    if($isCte){
        $sql = $sql;
    }else{
        if ($this->hasLimit($limit)) {
            $sql = "SELECT TOP $limit * FROM ($sql) sub";
        } else {
            $sql = "SELECT * FROM ($sql) sub";
        }
    }
    if ($this->hasOffset($offset)) {
        $sql .= $this->separator . "WHERE rowNum > $offset";
    }

    return $sql;
}

I only have one idea, but maybe it is bad idea because it will BBC. Maybe we could have cte method in QueryBuilder so when we want to create a query with CTE we use it like this


// with createCommand
$data = Yii::$app->db->createCommand("SELECT * FROM data")->cte([
    "WITH data AS (
        SELECT * FROM table
    )"
])->queryAll();

// or with sqlDataProvider
$data = new SqlDataProvider([
    'sql' => "SELECT * FROM data",
    'cte' => "
            WITH data AS (
                SELECT * FROM table
            )
        ",
    'pagination' => ['pageSize' => 50]
]);

So when cte method exist, query builder will create limit from sql like the oldBuilderOrderByAndLimit method, but it will prepend cte query in front of $sql returned from oldBuilderOrderByAndLimit method.

yiisoft / yii2