SelectSubqueryTest.php 9.38 KB
Newer Older
1 2
<?php

3
namespace Drupal\KernelTests\Core\Database;
4 5

/**
6 7 8
 * Tests the Select query builder.
 *
 * @group Database
9 10 11 12
 */
class SelectSubqueryTest extends DatabaseTestBase {

  /**
13
   * Tests that we can use a subquery in a FROM clause.
14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
   */
  function testFromSubquerySelect() {
    // Create a subquery, which is just a normal query object.
    $subquery = db_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 = db_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
30
        // placeholder counter, testing https://www.drupal.org/node/1112854.
31 32 33 34 35 36 37 38 39 40 41
        $select->condition('name', 'John');
      }
      $select->condition('task', 'code');

      // The resulting query should be equivalent to:
      // 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'
      $people = $select->execute()->fetchCol();

42
      $this->assertCount(1, $people, 'Returned the correct number of rows.');
43 44 45 46
    }
  }

  /**
47
   * Tests that we can use a subquery in a FROM clause with a LIMIT.
48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
   */
  function testFromSubquerySelectWithLimit() {
    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->addField('tt', 'task', 'task');
    $subquery->orderBy('priority', 'DESC');
    $subquery->range(0, 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select($subquery, 'tt2');
    $select->join('test', 't', 't.id=tt2.pid');
    $select->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM (SELECT tt.pid AS pid, tt.task AS task FROM test_task tt ORDER BY priority DESC LIMIT 1 OFFSET 0) tt
    //   INNER JOIN test t ON t.id=tt.pid
    $people = $select->execute()->fetchCol();

69
    $this->assertCount(1, $people, 'Returned the correct number of rows.');
70 71 72
  }

  /**
73
   * Tests that we can use a subquery with an IN operator in a WHERE clause.
74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
   */
  function testConditionSubquerySelect() {
    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->condition('tt.priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test_task', 'tt2');
    $select->addField('tt2', 'task');
    $select->condition('tt2.pid', $subquery, 'IN');

    // The resulting query should be equivalent to:
    // SELECT tt2.name
    // FROM test tt2
    // WHERE tt2.pid IN (SELECT tt.pid AS pid FROM test_task tt WHERE tt.priority=1)
    $people = $select->execute()->fetchCol();
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177
    $this->assertCount(5, $people, 'Returned the correct number of rows.');
  }

  /**
   * Test that we can use a subquery with a relational operator in a WHERE clause.
   */
  function testConditionSubquerySelect2() {
    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test', 't2');
    $subquery->addExpression('AVG(t2.age)');

    // Create another query that adds a clause using the subquery.
    $select = db_select('test', 't');
    $select->addField('t', 'name');
    $select->condition('t.age', $subquery, '<');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    // WHERE t.age < (SELECT AVG(t2.age) FROM test t2)
    $people = $select->execute()->fetchCol();
    $this->assertEquals(['John', 'Paul'], $people, 'Returned Paul and John.', 0.0, 10, TRUE);
  }

  /**
   * Test that we can use 2 subqueries with a relational operator in a WHERE clause.
   */
  function testConditionSubquerySelect3() {
    // Create subquery 1, which is just a normal query object.
    $subquery1 = db_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 = db_select('test_task', 'tt2');
    $subquery2->addExpression('AVG(tt2.priority)');

    // Create another query that adds a clause using the subqueries.
    $select = db_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->assertEquals(['John'], $people, 'Returned John.', 0.0, 10, TRUE);
  }

  /**
   * 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 :)
   */
  function testConditionSubquerySelect4() {
    // Create subquery 1, which is just a normal query object.
    $subquery1 = db_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 = db_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 = db_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 = db_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->assertEquals(['George', 'Paul'], $people, 'Returned George and Paul.', 0.0, 10, TRUE);
178 179 180
  }

  /**
181
   * Tests that we can use a subquery in a JOIN clause.
182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200
   */
  function testJoinSubquerySelect() {
    // Create a subquery, which is just a normal query object.
    $subquery = db_select('test_task', 'tt');
    $subquery->addField('tt', 'pid', 'pid');
    $subquery->condition('priority', 1);

    // Create another query that joins against the virtual table resulting
    // from the subquery.
    $select = db_select('test', 't');
    $select->join($subquery, 'tt', 't.id=tt.pid');
    $select->addField('t', 'name');

    // The resulting query should be equivalent to:
    // SELECT t.name
    // FROM test t
    //   INNER JOIN (SELECT tt.pid AS pid FROM test_task tt WHERE priority=1) tt ON t.id=tt.pid
    $people = $select->execute()->fetchCol();

201
    $this->assertCount(2, $people, 'Returned the correct number of rows.');
202 203 204
  }

  /**
205
   * Tests EXISTS subquery conditionals on SELECT statements.
206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230
   *
   * We essentially select all rows from the {test} table that have matching
   * rows in the {test_people} table based on the shared name column.
   */
  function testExistsSubquerySelect() {
    // Put George into {test_people}.
    db_insert('test_people')
      ->fields(array(
        'name' => 'George',
        'age' => 27,
        'job' => 'Singer',
      ))
      ->execute();
    // Base query to {test}.
    $query = db_select('test', 't')
      ->fields('t', array('name'));
    // Subquery to {test_people}.
    $subquery = db_select('test_people', 'tp')
      ->fields('tp', array('name'))
      ->where('tp.name = t.name');
    $query->exists($subquery);
    $result = $query->execute();

    // Ensure that we got the right record.
    $record = $result->fetch();
231
    $this->assertEquals('George', $record->name, 'Fetched name is correct using EXISTS query.');
232 233 234
  }

  /**
235
   * Tests NOT EXISTS subquery conditionals on SELECT statements.
236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260
   *
   * We essentially select all rows from the {test} table that don't have
   * matching rows in the {test_people} table based on the shared name column.
   */
  function testNotExistsSubquerySelect() {
    // Put George into {test_people}.
    db_insert('test_people')
      ->fields(array(
        'name' => 'George',
        'age' => 27,
        'job' => 'Singer',
      ))
      ->execute();

    // Base query to {test}.
    $query = db_select('test', 't')
      ->fields('t', array('name'));
    // Subquery to {test_people}.
    $subquery = db_select('test_people', 'tp')
      ->fields('tp', array('name'))
      ->where('tp.name = t.name');
    $query->notExists($subquery);

    // Ensure that we got the right number of records.
    $people = $query->execute()->fetchCol();
261
    $this->assertCount(3, $people, 'NOT EXISTS query returned the correct results.');
262
  }
263

264
}