function SelectSubqueryTest::testConditionSubquerySelect4

Tests that we can use multiple subqueries.

This test uses a subquery at the left hand side and multiple subqueries at the right hand side. The test query may not be that logical but that's due to the limited amount of data and tables. 'Valid' use cases do exist :)

File

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

Class

SelectSubqueryTest

Tests the Select query builder.

Namespace

Code

public function testConditionSubquerySelect4() : void {
    // Create subquery 1, which is just a normal query object.
    $subquery1 = $this->connection
        ->select('test_task', 'tt');
    $subquery1->addExpression('AVG([tt].[priority])');
    $subquery1->where('[tt].[pid] = [t].[id]');
    // Create subquery 2, which is just a normal query object.
    $subquery2 = $this->connection
        ->select('test_task', 'tt2');
    $subquery2->addExpression('MIN([tt2].[priority])');
    $subquery2->where('[tt2].[pid] <> [t].[id]');
    // Create subquery 3, which is just a normal query object.
    $subquery3 = $this->connection
        ->select('test_task', 'tt3');
    $subquery3->addExpression('AVG([tt3].[priority])');
    $subquery3->where('[tt3].[pid] <> [t].[id]');
    // Create another query that adds a clause using the subqueries.
    $select = $this->connection
        ->select('test', 't');
    $select->addField('t', 'name');
    $select->condition($subquery1, [
        $subquery2,
        $subquery3,
    ], 'BETWEEN');
    // The resulting query should be equivalent to:
    // @code
    // SELECT t.name AS name
    // FROM {test} t
    // WHERE (SELECT AVG(tt.priority) AS expression FROM {test_task} tt WHERE (tt.pid = t.id))
    //   BETWEEN (SELECT MIN(tt2.priority) AS expression FROM {test_task} tt2 WHERE (tt2.pid <> t.id))
    //       AND (SELECT AVG(tt3.priority) AS expression FROM {test_task} tt3 WHERE (tt3.pid <> t.id));
    // @endcode
    $people = $select->execute()
        ->fetchCol();
    $this->assertEqualsCanonicalizing([
        'George',
        'Paul',
    ], $people, 'Returned George and Paul.');
}