UPDATE Query Builder
The simplest UPDATE
statement example:
$update = new Update();
$update->table('user');
$update->set('first_name', 'John');
$update->set('last_name', 'Smith');
$update->set('age', Db::expr('age + 1'));
$update->where('id', 5);
Or:
Db::update('user')
->setValues([
'first_name' => 'John',
'last_name' => 'Smith'
])
->where('id', 5)
->increment('age')
->exec();
Both examples will execute:
UPDATE user
SET
first_name = 'John',
last_name = 'Smith',
age = age + 1
WHERE (id = 5)
To get how many rows was updated in table, use:
$rowsUpdated = $update->rowCount();
You can use WHERE statements on update
query builder so you can build complex where conditions.
Update With Order By
Imagine the following example: you have CMS where end user can write a question and available answers. It's possible to
rearrange answers order with drag'n'drop. You're using answers
table to store answer_id
and order_index
which is
real position of each answer and unique
to prevent two different answers of having the same position.
answer_id | order_index |
---|---|
5 | 0 |
8 | 1 |
7 | 2 |
4 | 5 |
1 | 4 |
6 | 6 |
3 | 3 |
If you delete one of the answers, then you need to update order_index
for all other answers below deleted answers,
meaning you have to decrement its order_index
for 1
. Let's say you want to delete answer_id=7
and decrement all
other values:
$oldPosition = Db::select('answers')->where('answer_id', 7)->fetchFirstObj()->order_index;
Db::delete('answers')->where('answer_id', 7)->exec();
Db::update('answers')
->decrement('order_index')
->where('order_index', '>', $oldPosition)
->exec();
This is good idea of how things should work, but update
query will fail in this example with error message from
database saying something about constraint error, duplicate value 4
. How?
After you have deleted record with answerd_id=7
, your table looks like this:
answer_id | order_index |
---|---|
5 | 0 |
8 | 1 |
4 | 5 |
1 | 4 |
6 | 6 |
3 | 3 |
And now you're trying to update all other order_index
values by decrementing them for 1
. Database is going to update
first record which finds under where
condition, and that's every record with order_index
greater than 2
. In this
case, first record for updating is answer_id=4
which needs to get new order_index
with value 4
. Since order_index
column is unique
, it can't be updated because record under answer_id=1
already have position 4
.
The solution for this is to use orderBy()
method so database first orders records and then does the update:
Db::update('answers')
->decrement('order_index')
->where('order_index', '>', $oldPosition)
->orderBy('order_index', 'asc')
->exec();