Laravel weird issue: query return data with DB::table() but not with model::

user_10151987 0
Viewed 81

I found an issue when delete some data, so I isolated the code

Route::get('v1/testapi', function (Request $request) {
return [
    DB::table('attendances')->select("id","attendance_date_id","remarks")
      ->whereIn('attendance_date_id', [925,926])->get(),
    Attendance::select("id","attendance_date_id","remarks")
      ->whereIn('attendance_date_id', [925,926])->get()
  ];
});

and this API always return

{
  "withDB":[
    {"id":8344,"attendance_date_id":925,"remarks":"Good"},
    {"id":8345,"attendance_date_id":925,"remarks":"Good"},
    {"id":8346,"attendance_date_id":925,"remarks":"Good"},
    {"id":8352,"attendance_date_id":926,"remarks":"Good"},
    {"id":8353,"attendance_date_id":926,"remarks":"Good"}
  ],
  "withModel":[]
}

Is there any difference between DB query and Model query?

NB: with or without select("id","attendance_date_id","remarks") it act same.

laravel mysql php
2 Answers

This is called SoftDelete. You have deleted_at column on your Attendances table. Laravel models have a trait called SoftDeletes so not only does it delete the record if this trait exits in the Model, but Eloquent sets the date on deleted_at column and doesn't show you this record.

On the other hand, the DB doesn't have this type of thing. It's like a normal query builder, and if it exists in the database, it just shows you. Here you can read more about soft delete.

user_25235310

little extra detailes: anyone can troubleshoot these type issues by enabling database query log like below

Route::get('v1/testapi', function (Request $request) {
  DB::enableQueryLog();
  return [
    'withDB'=>DB::table('attendances')->select("id","attendance_date_id","remarks")->whereIn('attendance_date_id', [925,926])->get(),
    'withModel'=>Attendance::select("id","attendance_date_id","remarks")->whereIn('attendance_date_id', [925,926])->get(),
    DB::getQueryLog()
  ];
});

and i found the query difference

{"query":"select `id`, `attendance_date_id`, `remarks` from `attendances` where `attendance_date_id` in (?, ?)","bindings":[925,926],"time":1.65},
{"query":"select `id`, `attendance_date_id`, `remarks` from `attendances` where `attendance_date_id` in (?, ?) and `attendances`.`deleted_at` is null","bindings":[925,926],"time":0.39}

the 'deleted_at' column make the issue

user_101519870
Related