3 namespace Drupal\KernelTests\Core\Database;
6 * Tests the Update query builder, complex queries.
10 class UpdateComplexTest extends DatabaseTestBase {
13 * Tests updates with OR conditionals.
15 public function testOrConditionUpdate() {
16 $update = db_update('test')
17 ->fields(['job' => 'Musician'])
19 ->condition('name', 'John')
20 ->condition('name', 'Paul')
22 $num_updated = $update->execute();
23 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
25 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
26 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
30 * Tests WHERE IN clauses.
32 public function testInConditionUpdate() {
33 $num_updated = db_update('test')
34 ->fields(['job' => 'Musician'])
35 ->condition('name', ['John', 'Paul'], 'IN')
37 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
39 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
40 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
44 * Tests WHERE NOT IN clauses.
46 public function testNotInConditionUpdate() {
47 // The o is lowercase in the 'NoT IN' operator, to make sure the operators
48 // work in mixed case.
49 $num_updated = db_update('test')
50 ->fields(['job' => 'Musician'])
51 ->condition('name', ['John', 'Paul', 'George'], 'NoT IN')
53 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
55 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
56 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
60 * Tests BETWEEN conditional clauses.
62 public function testBetweenConditionUpdate() {
63 $num_updated = db_update('test')
64 ->fields(['job' => 'Musician'])
65 ->condition('age', [25, 26], 'BETWEEN')
67 $this->assertIdentical($num_updated, 2, 'Updated 2 records.');
69 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
70 $this->assertIdentical($num_matches, '2', 'Updated fields successfully.');
74 * Tests LIKE conditionals.
76 public function testLikeConditionUpdate() {
77 $num_updated = db_update('test')
78 ->fields(['job' => 'Musician'])
79 ->condition('name', '%ge%', 'LIKE')
81 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
83 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
84 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
88 * Tests UPDATE with expression values.
90 public function testUpdateExpression() {
91 $before_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
92 $num_updated = db_update('test')
93 ->condition('name', 'Ringo')
94 ->fields(['job' => 'Musician'])
95 ->expression('age', 'age + :age', [':age' => 4])
97 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
99 $num_matches = db_query('SELECT COUNT(*) FROM {test} WHERE job = :job', [':job' => 'Musician'])->fetchField();
100 $this->assertIdentical($num_matches, '1', 'Updated fields successfully.');
102 $person = db_query('SELECT * FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetch();
103 $this->assertEqual($person->name, 'Ringo', 'Name set correctly.');
104 $this->assertEqual($person->age, $before_age + 4, 'Age set correctly.');
105 $this->assertEqual($person->job, 'Musician', 'Job set correctly.');
109 * Tests UPDATE with only expression values.
111 public function testUpdateOnlyExpression() {
112 $before_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
113 $num_updated = db_update('test')
114 ->condition('name', 'Ringo')
115 ->expression('age', 'age + :age', [':age' => 4])
117 $this->assertIdentical($num_updated, 1, 'Updated 1 record.');
119 $after_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
120 $this->assertEqual($before_age + 4, $after_age, 'Age updated correctly');
124 * Test UPDATE with a subselect value.
126 public function testSubSelectUpdate() {
127 $subselect = db_select('test_task', 't');
128 $subselect->addExpression('MAX(priority) + :increment', 'max_priority', [':increment' => 30]);
129 // Clone this to make sure we are running a different query when
131 $select = clone $subselect;
132 $query = db_update('test')
133 ->expression('age', $subselect)
134 ->condition('name', 'Ringo');
135 // Save the number of rows that updated for assertion later.
136 $num_updated = $query->execute();
137 $after_age = db_query('SELECT age FROM {test} WHERE name = :name', [':name' => 'Ringo'])->fetchField();
138 $expected_age = $select->execute()->fetchField();
139 $this->assertEqual($after_age, $expected_age);
140 $this->assertEqual(1, $num_updated, t('Expected 1 row to be updated in subselect update query.'));