function SelectSubqueryTest::testConditionSubquerySelect3

Tests we can use 2 subqueries with a relational operator in a WHERE clause.

File

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

Class

SelectSubqueryTest

Tests the Select query builder.

Namespace

Code

public function testConditionSubquerySelect3() : 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('AVG([tt2].[priority])');
    // Create another query that adds a clause using the subqueries.
    $select = $this->connection
        ->select('test', 't');
    $select->addField('t', 'name');
    $select->condition($subquery1, $subquery2, '>');
    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    // WHERE (SELECT AVG(tt.priority) FROM test_task tt WHERE tt.pid = t.id) > (SELECT AVG(tt2.priority) FROM test_task tt2)
    $people = $select->execute()
        ->fetchCol();
    $this->assertEqualsCanonicalizing([
        'John',
    ], $people, 'Returned John.');
}