Laravel - Search betwwen dates issue

user_11352561 0
Viewed 62

I am trying to use date filter in Laravel. Everything is working fine except the fact that if start_date and end_date are the same date. Then it brings no result except when I increase by 1.

    public function userresponseReport(Request $request)
{
    $data['title'] = 'User Response';

    $userresponses = DB::table('user_response as g')
    ->select(
       DB::raw('DATE(g.created_at) as created_date'),
       'g.msisdn', 
       'g.game_code', 
       'g.answer',
       'g.answer_code',
       'g.Amount_charged',
       'g.payment_ref',
       'g.status',
       'g.user_channel'                
  )               
 ->orderByRaw('g.created_at DESC'); 

    $render=[];  
    if(isset($request->start_date) && isset($request->end_date))
    {
        $userresponses=$userresponses->whereBetween('created_at',[$request->start_date,$request->end_date]);

        $render['start_date']=$request->start_date;
        $render['end_date']=$request->end_date;
    }elseif(isset($request->start_date))
    {
        $userresponses=$userresponses->where('created_at',$request->start_date);
        $render['start_date']=$request->start_date;
    }                
    $userresponses= $userresponses->orderBy('created_at','DESC');
    $userresponses= $userresponses->paginate(15);
    $userresponses= $userresponses->appends($render);
    $data['userresponses'] = $userresponses;

return view('report.userresponseReport',$data);        
}

How do I resolve this?

laravel
2 Answers

You can use whereDate() with >= and <= operators. Note the whereDate() method will take the date part out from the datetime expression. So, for the same date, the query will return all registers created_at that date.

$date_from = $request->start_date;
$date_to = $request->end_date;
$render=[];
$userresponses = DB::table('user_response as g')
    ->select(
       // ...               
  )               
 ->orderByRaw('g.created_at DESC')
 ->when($date_from, function($query) use ($date_from, $render) {
     $render['start_date'] = $date_from;
     $query->whereDate('created_at', '>=', $date_from);
 })
 ->when($date_to, function($query) use ($date_to, $render) {
     $render['end_date'] = $date_to;
     $query->whereDate('created_at', '<=', $date_to);
 })
 ->orderBy('created_at','DESC')
 ->paginate(15);

If you want compare datetimes, use where(). And the same, you can resolve the issue "if start_date and end_date are the same date not working" with the operators <= and >=.

 // ...
 ->when($date_from, function($query) use ($date_from, $render) {
     $render['start_date'] = $date_from;
     $query->where('created_at', '>=', $date_from);
 })
 ->when($date_to, function($query) use ($date_to, $render) {
     $render['end_date'] = $date_to;
     $query->where('created_at', '<=', $date_to);
 })

But it would be very difficult to find a record from a datetime "2019-06-28 14:44:44" until the same datetime, it would only return something if a record had been created in that precise second.

user_74981160

If start date and end date are the same you still want to return the result?

You could use orWhere.

$userresponses->whereBetween('created_at',[$request->start_date,$request->end_date])
->orWhere('created_at', '=', $request->start_date)
->orWhere('created_at', '=', $request->end_date);
user_20310330
Related