Laravel eloquent update record with minimum value

Question

I need to find the minimum "priority" value and then set it's value to another entry, in place using laravel eloquent.

Is there any way to do so or any best practice for it?

What I have in mind :

Orders::where('user_id', '!=', $user_id)
 ->where('odm_id', $odm_id)
 ->whereExpired(0)
 ->min('priority')
 ->update(['priority' => 1]);

FYI: Above approach is giving an error and is not working. The Error Message is :

Call to a member function update() on string

Looks good to me. Cannot think of any other better way to do this. — Chris, Aug 21 '18 at 07:59
It depends on your requirements. if you only want to get min priority then do not add extra checks. if your result depends on other column values then you are in correct way already. Next, you can minimize query by defining method in Order class. — Afraz Ahmad, Aug 21 '18 at 08:02
@Chris WhereExpired() is better way of using where('expired', 1) — Mohammad_Hosseini, Aug 21 '18 at 08:03
@AfrazAhmad I need those checks, that's why i put them in there in the first place, but as I mentioned this approach is giving error and is not updating the column value — Mohammad_Hosseini, Aug 21 '18 at 08:05
@ShobiPP I've updated the question and I put the error message in it. — Mohammad_Hosseini, Aug 21 '18 at 08:10

apokryfos · Accepted Answer · 2018-08-21T08:36:32.740

1

The problem is that min will return a number and not an actual model. If you want the actual model which has the min value you need to something like:

Orders::where('user_id', '!=', $user_id)
        ->where('odm_id', $odm_id)
        ->wherePriority(function ($query) use ($user_id, $odm_id) {
        $query->from(\DB::raw('(SELECT * FROM orders) AS o'))
              ->selectRaw("MIN(`o`.`priority`)")                   
              ->where('o.odm_id', $odm_id)
              ->where('o.expired', 0);
        })->update(['priority' => 1]);

Note that this will update all records which have expired 0, odm_id equal to $odm_id and match the minimum value.

Alternatively you can do:

$orders =Orders::where('user_id', '!=', $user_id)
        ->where('odm_id', $odm_id)
        ->wherePriority(function ($query) use ($user_id, $odm_id) {
        $query->from(\DB::raw('(SELECT * FROM orders) AS o'))
              ->selectRaw("MIN(`o`.`priority`)")                   
              ->where('o.odm_id', $odm_id)
              ->where('o.expired', 0);
        })->get();

  $orders->each(function ($order) {
       $order->priority = 1;
       $order->save();
  });

The second way has the disadvantage of doing 2 separate queries, one to get the data and one to update, however this has the advantage of triggering model events such as saving etc.

edited Aug 21 '18 at 08:36

answered Aug 21 '18 at 08:10

apokryfos

30,388
6
55
83

I've used your method but the problem is it gives me this error : "SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where `odm_id` = ? and `expired` = ?)' at line 1 (SQL: update `orders` set `priority` = 1, `updated_at` = 2018-08-21 08:22:19 where `user_id` != 27 and `odm_id` = 6 and `priority` = (select MIN(`priority`) where `odm_id` = 6 and `expired` = 0))" – Mohammad_Hosseini Aug 21 '18 at 08:27
@Mohammad_Hosseini try adding the table name in the nested select as in the update – apokryfos Aug 21 '18 at 08:28
I've already done that but it was giving me this error : "SQLSTATE[HY000]: General error: 1093 You can't specify target table 'orders' for update in FROM clause (SQL: update `orders` set `priority` = 1, `updated_at` = 2018-08-21 08:30:51 where `user_id` != 27 and `odm_id` = 6 and `priority` = (select MIN(`priority`) from `orders` where `odm_id` = 6 and `expired` = 0))" – Mohammad_Hosseini Aug 21 '18 at 08:32
Try the update again. It seems that [MySQL is a bit strange in working with the same table in inner queries on updates](https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause) . – apokryfos Aug 21 '18 at 08:38

Laravel eloquent update record with minimum value

1 Answers1