DEV Community

Cover image for Stop Using whereDate() Laravel
Timothy Soladoye
Timothy Soladoye

Posted on

Stop Using whereDate() Laravel

whereDate is a harmless eloquent method, or is it??

$users = DB::table('users')
                ->whereDate('created_at', '2016-12-31')
                ->get();
Enter fullscreen mode Exit fullscreen mode

When you are querying a table with large data, whereDate is a bad option.

Reasons

  1. The query does a full table scan
  2. It does not allow indexed columns to work.

My Personal Experience
A snippet from my code before

$message_week=Log::where('user_id',$user->id)
         ->whereDate('created_at','>=',$week_started_date)
         ->count();
Enter fullscreen mode Exit fullscreen mode

I changed it to this and I had great performance improvement

$date=$week_started_date->format('Y-m-d');
$message_week=Log::where('user_id',$user->id)
         ->where('created_at','>=',$date.' 00:00:00')
         ->count();
Enter fullscreen mode Exit fullscreen mode

Take note of the space before the 00 in ' 00:00:00'
It is buiding the date to be 2022-12-31 00:00:00

Stop using whereDate for large data query

Top comments (1)

Collapse
 
hakimi_hamzan profile image
Hakimi Hamzan

seem fine but this query will get all Log where is greater than $date, I think to get only Log on that $date, better to use whereBetween right?