Laravel 5更新单行限制不起作用 [英] Laravel 5 updating single row limit doesn't work

问题描述

我需要更新db(PostgreSQL)中的单个匹配记录，但是Limit方法不适用于Update方法.此代码将更新所有与Where条件匹配的记录，而不是单个记录.

I need to update single matching record in db (PostgreSQL), but Limit method doesn't work with Update method. This code will update all records matching Where condition instead of single record.

DB::table("records")
  ->where('need_moderate','=','no')
  ->where('locked_per_time','<',$date_now->format("Y-m-d H:i:s"))
  ->limit(1)
  ->update(["locked_per_time"=>$locked_per->format("Y-m-d H:i:s"),'locked_by'=>$mdkey]);

如何解决此问题，以便仅更新一条记录?

How do I work around this so only single record would be updated?

推荐答案

与 Oracle 或 MySQL 更新语句，不能直接在 PostgreSQL更新语句上使用LIMIT .因此，将limit(1)方法链接到查询生成器实例没有任何作用，因为

Unlike with Oracle or MySQL update statements, using LIMIT directly on PostgreSQL update statements is not possible. So chaining the limit(1) method to the Query Builder instance does nothing, because the compileUpdate method from Laravel's PostgresGrammar class that is responsible for compiling the query, only compiles the where statements.

不过，您可以通过使用只返回一行将被更新的子查询的条件来克服此问题.这样的事情应该起作用:

You could however overcome this by having a condition that uses a subquery which only returns one row that will be updated. Something like this should work:

DB::table("records")->whereIn('id', function ($query) use ($date_now) {
    $query->from('records')
          ->select('id')
          ->where('need_moderate', '=', 'no')
          ->where('locked_per_time', '<', $date_now->format("Y-m-d H:i:s"))
          ->limit(1);
})->update(["locked_per_time" => $locked_per->format("Y-m-d H:i:s"), 'locked_by' => $mdkey]);

whereIn('id', ...)条件假定您的表具有名为id的列，该列可用作唯一标识符，因此它可以在子查询中找到与您的条件匹配的第一行.

The whereIn('id', ...) condition assumes your table has a column named id that can be used as a unique identifier so it can find the first row that matches your conditions in the subquery.

这篇关于Laravel 5更新单行限制不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！