3 namespace Drupal\KernelTests\Core\Database;
5 use Drupal\Core\Database\Query\Condition;
8 * Tests the Update query builder, complex queries.
12 class UpdateComplexTest extends DatabaseTestBase {
15 * Tests updates with OR conditionals.
17 public function testOrConditionUpdate() {
18 $update = db_update('test')
19 ->fields(['job' => 'Musician'])
20 ->condition((new Condition('OR'))
21 ->condition('name', 'John')
22 ->condition('name', 'Paul')
24 $num_updated = $update->execute();
25 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
27 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
28 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
32 * Tests WHERE IN clauses.
34 public function testInConditionUpdate() {
35 $num_updated = db_update('test')
36 ->fields(['job' => 'Musician'])
37 ->condition('name', ['John', 'Paul'], 'IN')
39 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
41 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
42 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
46 * Tests WHERE NOT IN clauses.
48 public function testNotInConditionUpdate() {
49 // The o is lowercase in the 'NoT IN' operator, to make sure the operators
50 // work in mixed case.
51 $num_updated = db_update('test')
52 ->fields(['job' => 'Musician'])
53 ->condition('name', ['John', 'Paul', 'George'], 'NoT IN')
55 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
57 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
58 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
62 * Tests BETWEEN conditional clauses.
64 public function testBetweenConditionUpdate() {
65 $num_updated = db_update('test')
66 ->fields(['job' => 'Musician'])
67 ->condition('age', [25, 26], 'BETWEEN')
69 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
71 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
72 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
76 * Tests LIKE conditionals.
78 public function testLikeConditionUpdate() {
79 $num_updated = db_update('test')
80 ->fields(['job' => 'Musician'])
81 ->condition('name', '%ge%', 'LIKE')
83 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
85 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
86 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
90 * Tests UPDATE with expression values.
92 public function testUpdateExpression() {
93 $before_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
94 $num_updated = db_update('test')
95 ->condition('name', 'Ringo')
96 ->fields(['job' => 'Musician'])
97 ->expression('age', 'age + :age', [':age' => 4])
99 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
101 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
102 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
104 $person = db_query('SELECT * FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetch();
105 $this->assertEqual($person->name, 'Ringo', 'Name set correctly.');
106 $this->assertEqual($person->age, $before_age + 4, 'Age set correctly.');
107 $this->assertEqual($person->job, 'Musician', 'Job set correctly.');
111 * Tests UPDATE with only expression values.
113 public function testUpdateOnlyExpression() {
114 $before_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
115 $num_updated = db_update('test')
116 ->condition('name', 'Ringo')
117 ->expression('age', 'age + :age', [':age' => 4])
119 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
121 $after_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
122 $this->assertEqual($before_age + 4, $after_age, 'Age updated correctly');
126 * Test UPDATE with a subselect value.
128 public function testSubSelectUpdate() {
129 $subselect = db_select('test_task', 't');
130 $subselect->addExpression('MAX(priority) + :increment', 'max_priority', [':increment' => 30]);
131 // Clone this to make sure we are running a different query when
133 $select = clone $subselect;
134 $query = db_update('test')
135 ->expression('age', $subselect)
136 ->condition('name', 'Ringo');
137 // Save the number of rows that updated for assertion later.
138 $num_updated = $query->execute();
139 $after_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
140 $expected_age = $select->execute()->fetchField();
141 $this->assertEqual($after_age, $expected_age);
142 $this->assertEqual(1, $num_updated, t('Expected 1 row to be updated in subselect update query.'));