function SelectSubqueryTest::testFromSubquerySelect

Tests that we can use a subquery in a FROM clause.

File

core/tests/Drupal/KernelTests/Core/Database/SelectSubqueryTest.php, line 17

Class

SelectSubqueryTest

Tests the Select query builder.

Namespace

Code

public function testFromSubquerySelect() : void {
    // Create a subquery, which is just a normal query object.
    $subquery = $this->connection
        ->select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->addField('tt', 'task', 'task');
    $subquery->condition('priority', 1);
    for ($i = 0; $i < 2; $i++) {
        // Create another query that joins against the virtual table resulting
        // from the subquery.
        $select = $this->connection
            ->select($subquery, 'tt2');
        $select->join('test', 't', '[t].[id] = [tt2].[pid]');
        $select->addField('t', 'name');
        if ($i) {
            // Use a different number of conditions here to confuse the subquery
            // placeholder counter, testing https://www.drupal.org/node/1112854.
            $select->condition('name', 'John');
        }
        $select->condition('task', 'code');
        // The resulting query should be equivalent to:
        // @code
        // SELECT t.name
        // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt WHERE priority=1) tt
        //   INNER JOIN test t ON t.id=tt.pid
        // WHERE tt.task = 'code'
        // @endcode
        $people = $select->execute()
            ->fetchCol();
        $this->assertCount(1, $people, 'Returned the correct number of rows.');
    }
}