, <body>\) AS $SELECT 'Fourth Article', 'Fourth Article Body'$ " . 'INSERT INTO <articles> $<title>, <body>$', $query->sql(new ValueBinder()), !$this->autoQuote ); // run insert $query->execute()->closeCursor(); $expected = [ 'id' => '4', 'author_id' => null, 'title' => 'Fourth Article', 'body' => 'Fourth Article Body', 'published' => 'N', ]; // test updated state $result = $this->connection->newSelectQuery('*', 'articles') ->where(['id' => 4]) ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } /** * Tests inserting from CTE as values list. */ public function testWithInInsertWithValuesQuery(): void { $this->skipIf( ($this->connection->getDriver() instanceof Sqlserver), '`INSERT INTO ... WITH` syntax is not supported in SQL Server.' ); $query = $this->connection->newInsertQuery(table: 'articles') ->insert(['title', 'body']) ->values( $this->connection->newSelectQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { return $cte ->name('cte') ->field(['title', 'body']) ->query($query->newExpr("SELECT 'Fourth Article', 'Fourth Article Body'")); }) ->select('*') ->from('cte') ); $this->assertRegExpSql( 'INSERT INTO <articles> $<title>, <body>$ ' . "WITH <cte>$<title>, <body>$ AS $SELECT 'Fourth Article', 'Fourth Article Body'$ SELECT \* FROM <cte>", $query->sql(new ValueBinder()), !$this->autoQuote ); // run insert $query->execute()->closeCursor(); $expected = [ 'id' => '4', 'author_id' => null, 'title' => 'Fourth Article', 'body' => 'Fourth Article Body', 'published' => 'N', ]; // test updated state $result = $this->connection->newSelectQuery(fields: '*', table: 'articles') ->where(['id' => 4]) ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } /** * Tests updating from CTE. */ public function testWithInUpdateQuery(): void { $this->skipIf( $this->connection->getDriver() instanceof Mysql && $this->connection->getDriver()->isMariadb(), 'MariaDB does not support CTEs in UPDATE query.' ); // test initial state $result = $this->connection->newSelectQuery(fields: ['count' => 'COUNT(*)'], table: 'articles') ->where(['published' => 'Y']) ->execute(); $this->assertEquals(['count' => '3'], $result->fetch('assoc')); $result->closeCursor(); $query = $this->connection->newUpdateQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { $cteQuery = $query ->select('articles.id') ->from('articles') ->where(['articles.id !=' => 1]); return $cte ->name('cte') ->query($cteQuery); }) ->update('articles') ->set('published', 'N') ->where(function (QueryExpression $exp, Query $query) { return $exp->in( 'articles.id', $query ->getConnection() ->newSelectQuery('cte.id', 'cte') ); }); $this->assertEqualsSql( 'WITH cte AS (SELECT articles.id FROM articles WHERE articles.id != :c0) ' . 'UPDATE articles SET published = :c1 WHERE id IN (SELECT cte.id FROM cte)', $query->sql(new ValueBinder()) ); // run update $query->execute()->closeCursor(); // test updated state $result = $this->connection->newSelectQuery(['count' => 'COUNT(*)'], 'articles') ->where(['published' => 'Y']) ->execute(); $this->assertEquals(['count' => '1'], $result->fetch('assoc')); $result->closeCursor(); } /** * Tests deleting from CTE. */ public function testWithInDeleteQuery(): void { $this->skipIf( $this->connection->getDriver() instanceof Mysql && $this->connection->getDriver()->isMariadb(), 'MariaDB does not support CTEs in DELETE query.' ); // test initial state $result = $this->connection ->newSelectQuery(['count' => 'COUNT(*)'], 'articles') ->execute(); $this->assertEquals(['count' => '3'], $result->fetch('assoc')); $result->closeCursor(); $query = $this->connection->newDeleteQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { $query->select('articles.id') ->from('articles') ->where(['articles.id !=' => 1]); return $cte ->name('cte') ->query($query); }) ->delete() ->from(['a' => 'articles']) ->where(function (QueryExpression $exp, Query $query) { return $exp->in( 'a.id', $query ->getConnection() ->newSelectQuery('cte.id', 'cte') ); }); $this->assertEqualsSql( 'WITH cte AS (SELECT articles.id FROM articles WHERE articles.id != :c0) ' . 'DELETE FROM articles WHERE id IN (SELECT cte.id FROM cte)', $query->sql(new ValueBinder()) ); // run delete $query->execute()->closeCursor(); $expected = [ 'id' => '1', 'author_id' => '1', 'title' => 'First Article', 'body' => 'First Article Body', 'published' => 'Y', ]; // test updated state $result = $this->connection->newSelectQuery('*', 'articles') ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } }

connection = ConnectionManager::get('test'); $this->autoQuote = $this->connection->getDriver()->isAutoQuotingEnabled(); $this->skipIf( !$this->connection->getDriver()->supports(DriverFeatureEnum::CTE), 'The current driver does not support common table expressions.' ); } public function tearDown(): void { parent::tearDown(); unset($this->connection); } /** * Tests with() sql generation. */ public function testWithCte(): void { $query = $this->connection->newSelectQuery() ->with(new CommonTableExpression('cte', function () { return $this->connection->newSelectQuery(fields: ['col' => 1]); })) ->select('col') ->from('cte'); $this->assertRegExpSql( 'WITH AS $SELECT 1 AS $ SELECT FROM ', $query->sql(new ValueBinder()), !$this->autoQuote ); $expected = [ [ 'col' => '1', ], ]; $result = $query->execute(); $this->assertEquals($expected, $result->fetchAll('assoc')); $result->closeCursor(); } /** * Tests calling with() with overwrite clears other CTEs. */ public function testWithCteOverwrite(): void { $query = $this->connection->newSelectQuery() ->with(new CommonTableExpression('cte', function () { return $this->connection->newSelectQuery(['col' => '1']); })) ->select('col') ->from('cte'); $this->assertEqualsSql( 'WITH cte AS (SELECT 1 AS col) SELECT col FROM cte', $query->sql(new ValueBinder()) ); $query ->with(new CommonTableExpression('cte2', $this->connection->newSelectQuery()), true) ->from('cte2', true); $this->assertEqualsSql( 'WITH cte2 AS () SELECT col FROM cte2', $query->sql(new ValueBinder()) ); } /** * Tests recursive CTE. */ public function testWithRecursiveCte(): void { $query = $this->connection->newSelectQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { $anchorQuery = $query->select(1); $recursiveQuery = $query->getConnection() ->newSelectQuery(function (Query $query) { return $query->newExpr('col + 1'); }, 'cte') ->where(['col !=' => 3], ['col' => 'integer']); $cteQuery = $anchorQuery->unionAll($recursiveQuery); return $cte ->name('cte') ->field(['col']) ->query($cteQuery) ->recursive(); }) ->select('col') ->from('cte'); if ($this->connection->getDriver() instanceof Sqlserver) { $expectedSql = 'WITH cte(col) AS ' . "(SELECT 1\nUNION ALL SELECT (col + 1) FROM cte WHERE col != :c0) " . 'SELECT col FROM cte'; } elseif ($this->connection->getDriver() instanceof Sqlite) { $expectedSql = 'WITH RECURSIVE cte(col) AS ' . "(SELECT 1\nUNION ALL SELECT (col + 1) FROM cte WHERE col != :c0) " . 'SELECT col FROM cte'; } else { $expectedSql = 'WITH RECURSIVE cte(col) AS ' . "((SELECT 1)\nUNION ALL (SELECT (col + 1) FROM cte WHERE col != :c0)) " . 'SELECT col FROM cte'; } $this->assertEqualsSql( $expectedSql, $query->sql(new ValueBinder()) ); $expected = [ [ 'col' => '1', ], [ 'col' => '2', ], [ 'col' => '3', ], ]; $result = $query->execute(); $this->assertEquals($expected, $result->fetchAll('assoc')); $result->closeCursor(); } /** * Test inserting from CTE. */ public function testWithInsertQuery(): void { $this->skipIf( ($this->connection->getDriver() instanceof Mysql), '`WITH ... INSERT INTO` syntax is not supported in MySQL.' ); // test initial state $result = $this->connection->newSelectQuery(fields: '*', table: 'articles') ->where(['id' => 4]) ->execute(); $this->assertFalse($result->fetch('assoc')); $result->closeCursor(); $query = $this->connection ->newInsertQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { return $cte ->name('cte') ->field(['title', 'body']) ->query($query->newExpr("SELECT 'Fourth Article', 'Fourth Article Body'")); }) ->insert(['title', 'body']) ->into('articles') ->values( $this->connection ->newSelectQuery(fields: '*', table: 'cte') ); $this->assertRegExpSql( "WITH $, <body>$ AS $SELECT 'Fourth Article', 'Fourth Article Body'$ " . 'INSERT INTO <articles> $<title>, <body>$', $query->sql(new ValueBinder()), !$this->autoQuote ); // run insert $query->execute()->closeCursor(); $expected = [ 'id' => '4', 'author_id' => null, 'title' => 'Fourth Article', 'body' => 'Fourth Article Body', 'published' => 'N', ]; // test updated state $result = $this->connection->newSelectQuery('*', 'articles') ->where(['id' => 4]) ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } /** * Tests inserting from CTE as values list. */ public function testWithInInsertWithValuesQuery(): void { $this->skipIf( ($this->connection->getDriver() instanceof Sqlserver), '`INSERT INTO ... WITH` syntax is not supported in SQL Server.' ); $query = $this->connection->newInsertQuery(table: 'articles') ->insert(['title', 'body']) ->values( $this->connection->newSelectQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { return $cte ->name('cte') ->field(['title', 'body']) ->query($query->newExpr("SELECT 'Fourth Article', 'Fourth Article Body'")); }) ->select('*') ->from('cte') ); $this->assertRegExpSql( 'INSERT INTO <articles> $<title>, <body>$ ' . "WITH <cte>$<title>, <body>$ AS $SELECT 'Fourth Article', 'Fourth Article Body'$ SELECT \* FROM <cte>", $query->sql(new ValueBinder()), !$this->autoQuote ); // run insert $query->execute()->closeCursor(); $expected = [ 'id' => '4', 'author_id' => null, 'title' => 'Fourth Article', 'body' => 'Fourth Article Body', 'published' => 'N', ]; // test updated state $result = $this->connection->newSelectQuery(fields: '*', table: 'articles') ->where(['id' => 4]) ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } /** * Tests updating from CTE. */ public function testWithInUpdateQuery(): void { $this->skipIf( $this->connection->getDriver() instanceof Mysql && $this->connection->getDriver()->isMariadb(), 'MariaDB does not support CTEs in UPDATE query.' ); // test initial state $result = $this->connection->newSelectQuery(fields: ['count' => 'COUNT(*)'], table: 'articles') ->where(['published' => 'Y']) ->execute(); $this->assertEquals(['count' => '3'], $result->fetch('assoc')); $result->closeCursor(); $query = $this->connection->newUpdateQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { $cteQuery = $query ->select('articles.id') ->from('articles') ->where(['articles.id !=' => 1]); return $cte ->name('cte') ->query($cteQuery); }) ->update('articles') ->set('published', 'N') ->where(function (QueryExpression $exp, Query $query) { return $exp->in( 'articles.id', $query ->getConnection() ->newSelectQuery('cte.id', 'cte') ); }); $this->assertEqualsSql( 'WITH cte AS (SELECT articles.id FROM articles WHERE articles.id != :c0) ' . 'UPDATE articles SET published = :c1 WHERE id IN (SELECT cte.id FROM cte)', $query->sql(new ValueBinder()) ); // run update $query->execute()->closeCursor(); // test updated state $result = $this->connection->newSelectQuery(['count' => 'COUNT(*)'], 'articles') ->where(['published' => 'Y']) ->execute(); $this->assertEquals(['count' => '1'], $result->fetch('assoc')); $result->closeCursor(); } /** * Tests deleting from CTE. */ public function testWithInDeleteQuery(): void { $this->skipIf( $this->connection->getDriver() instanceof Mysql && $this->connection->getDriver()->isMariadb(), 'MariaDB does not support CTEs in DELETE query.' ); // test initial state $result = $this->connection ->newSelectQuery(['count' => 'COUNT(*)'], 'articles') ->execute(); $this->assertEquals(['count' => '3'], $result->fetch('assoc')); $result->closeCursor(); $query = $this->connection->newDeleteQuery() ->with(function (CommonTableExpression $cte, SelectQuery $query) { $query->select('articles.id') ->from('articles') ->where(['articles.id !=' => 1]); return $cte ->name('cte') ->query($query); }) ->delete() ->from(['a' => 'articles']) ->where(function (QueryExpression $exp, Query $query) { return $exp->in( 'a.id', $query ->getConnection() ->newSelectQuery('cte.id', 'cte') ); }); $this->assertEqualsSql( 'WITH cte AS (SELECT articles.id FROM articles WHERE articles.id != :c0) ' . 'DELETE FROM articles WHERE id IN (SELECT cte.id FROM cte)', $query->sql(new ValueBinder()) ); // run delete $query->execute()->closeCursor(); $expected = [ 'id' => '1', 'author_id' => '1', 'title' => 'First Article', 'body' => 'First Article Body', 'published' => 'Y', ]; // test updated state $result = $this->connection->newSelectQuery('*', 'articles') ->execute(); $this->assertEquals($expected, $result->fetch('assoc')); $result->closeCursor(); } }