function SelectSubqueryTest::testConditionSubquerySelect4

Same name in other branches
  1. 9 core/tests/Drupal/KernelTests/Core/Database/SelectSubqueryTest.php \Drupal\KernelTests\Core\Database\SelectSubqueryTest::testConditionSubquerySelect4()
  2. 10 core/tests/Drupal/KernelTests/Core/Database/SelectSubqueryTest.php \Drupal\KernelTests\Core\Database\SelectSubqueryTest::testConditionSubquerySelect4()
  3. 11.x core/tests/Drupal/KernelTests/Core/Database/SelectSubqueryTest.php \Drupal\KernelTests\Core\Database\SelectSubqueryTest::testConditionSubquerySelect4()

Test 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 149

Class

SelectSubqueryTest
Tests the Select query builder.

Namespace

Drupal\KernelTests\Core\Database

Code

public function testConditionSubquerySelect4() {
    // 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:
    // 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));
    $people = $select->execute()
        ->fetchCol();
    $this->assertEqualsCanonicalizing([
        'George',
        'Paul',
    ], $people, 'Returned George and Paul.');
}

Buggy or inaccurate documentation? Please file an issue. Need support? Need help programming? Connect with the Drupal community.