具有多个条件的laravel查询更新

Question

I have column named flag and I want to update it if value is 1 to null and if value is null to 1 so far is easy to update this column but issue comes where I send multiple data to controller and not only one.

code

public function flagmultiplemessage(Request $request){
  $ids = $request->input('ids');
  DB::table('messages')->whereIn('id', $ids)
  ->whereNotNull('messages.flag')->update(['flag' => null])
  ->whereNull('messages.flag')->update(['flag' => '1']);
}

with function above i get:

message Call to a member function whereNull() on integer

dd

code above is something like this:

ids = [11, 12, 3]
database = [
  11->flag = 1,
  12->flag = null,
  3->flag = 1,
]

the result of code above most change my database like:

database = [
  11->flag = null,
  12->flag = 1,
  3->flag = null,
]

any idea why i get error?

Answer 1

it occurred because you called whereNull method on update method. You should run 3 separate query like this.

public function flagmultiplemessage(Request $request){
  $ids = $request->input('ids');

  DB::transaction(function () use ($ids) {
    DB::table('messages')->whereIn('id', $ids)
    ->whereNotNull('messages.flag')->update(['flag' => 0]);

    DB::table('messages')->whereIn('id', $ids)
    ->whereNull('messages.flag')->update(['flag' => 1]);

    DB::table('messages')->whereIn('id', $ids)
    ->where('messages.flag', 0)->update(['flag' => null]);

  });

}

but for better performance I suggest you use boolean for flag column and use this simple query

DB::table('messages')->whereIn('id', $ids)->update(['flag' => DB::raw('!flag')]);