How to apply values from array and update rows using Laravel Eloquent?

Question

If I have a table like this:

+--------+-------------------+-----------+
| ID     | Type              |  nOrder   |
+--------+-------------------+-----------+
| 1      | A                 | 0         |
| 2      | A                 | 1         |
| 3      | A                 | 2         |
| 4      | B                 | 0         |
| 5      | B                 | 1         |
| 6      | B                 | 2         |
| 7      | B                 | 3         |
| 8      | B                 | 4         |
| 9      | C                 | 0         |
| 10     | C                 | 1         |
+--------+-------------------+-----------+

Using Laravel Eloquent:

$sortOrder = [1, 2, 0, 3, 4];

$list = Type::where('Type', '=', 'B')
    ->orderBy('nOrder', 'ASC')
    ->update(['nOrder' => ??? ]); // how can i apply the $sortOrder values at ???

My guess would be, the update would look something like this:

->update(['nOrder' => $sortOrder[DB::raw('`nOrder`')] ]); // this doesn't work though

The expected results would look like this:

+--------+-------------------+-----------+
| ID     | Type              |  nOrder   |
+--------+-------------------+-----------+
| 1      | A                 | 0         |
| 2      | A                 | 1         |
| 3      | A                 | 2         |
| 4      | B                 | 1         |
| 5      | B                 | 2         |
| 6      | B                 | 0         |
| 7      | B                 | 3         |
| 8      | B                 | 4         |
| 9      | C                 | 0         |
| 10     | C                 | 1         |
+--------+-------------------+-----------+

So, your problem isn't with constructing the query per se, but with reformatting it for laravel/eloquent? BTW, note that order is a reserved word - a poor choice for a table/column identifier. — Strawberry
– Strawberry, Commented May 18, 2015 at 10:07
I fixed the example so it's now nOrder. I was hoping the title for the question was self explanatory. — Howard
– Howard, Commented May 18, 2015 at 10:13
you have to loop the $sortOrder there is no one step I guess. — Jobin
– Jobin, Commented May 18, 2015 at 10:18
I'm pretty sure there's a way to update with a single query. It doesn't make sense if you have to run n queries for n rows for something like this. — Howard
– Howard, Commented May 18, 2015 at 10:20

vps · Accepted Answer · 2015-05-18 11:53:07Z

1

You can try following approach...

$sortOrder = [1, 2, 0, 3, 4];
$updateArray = ['Type' => 'B', 'param2' => ''];
$collection = Type::whereIn('nOrder', $sortOrder);
$data = $collection->get();
$collection->update($updateArray);

I didn't test this but this should work.

edited May 18, 2015 at 11:53

answered May 18, 2015 at 11:08

vps

4202 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Howard Over a year ago

I'm trying to update the table not get information from it. Basically I need a updateIn().

Howard Over a year ago

Can you edit the answer so I can give you an upvote? It says I can't upvote since it's locked but if you edit anything it becomes unlocked.

vps Over a year ago

Great, it worked for you, please up-vote my answer as well so others can use it as well.

Collectives™ on Stack Overflow

How to apply values from array and update rows using Laravel Eloquent?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related