public function SelectSubqueryTest::testConditionSubquerySelect4 in Drupal 9

Same name and namespace in other branches

1. 8 core/tests/Drupal/KernelTests/Core/Database/SelectSubqueryTest.php \Drupal\KernelTests\Core\Database\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

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:
  // @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.');
}