Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

TuffRivers's avatar

WhereRaw for date comparison not working

Hi All,

I have a chained when query that is used for input searching to build a dynamic query. I for some reason cannot get the date comparson to work.

In my query the date column is a timestamp, ive tried

        ->when($start_date, function ($q, $start_date){
            return $q->whereRaw('DATE(t.timestamp) >= ?', [$start_date]); //Y-m-d
        })
        ->when($end_date, function ($q, $end_date){
            return $q->whereRaw('DATE(t.timestamp) <= ?', [$end_date]); //Y-m-d
        })

as well ass

        ->when($start_date, function ($q, $start_date){
            return $q->whereDate('t.timestamp', '>= ', $start_date);
        })
        ->when($end_date, function ($q, $end_date){
            return $q->whereDate('t.timestamp', '>= ', $end_date);
        })

this is my full query, please let me know if there is something wrong.

        $data = DB::table('Client_Transactions as t')
        ->leftJoin('Client_Accounts as c','c.ClientID', 't.ClientID')
        ->leftJoin('Facilities as f','f.id','t.facility')
        ->leftJoin('materials as m','m.id','t.material_id')
        ->leftJoin('outbound_locations as o', 'o.id','t.outbound_location_id')
        ->select(
            'c.ClientID as ClientID',
            'c.Client_Name as Client_Name',
            't.timestamp',
            't.material_id AS material_id',
            'm.name AS Material',
            'f.name AS FacilityName',
            't.load_type AS LoadType',
            't.facility AS Facility',
            'o.name as Receiver',
            DB::raw('concat(month(t.timestamp),"-",dayofmonth(t.timestamp), "-",year(t.timestamp)) AS "Date-String"'))
        ->when($material, function ($q, $material){
            return $q->where('t.material_id', $material);
        })
        ->when($start_date, function ($q, $start_date){
            return $q->whereRaw('DATE(t.timestamp) >= ?', [$start_date]);
        })
        ->when($end_date, function ($q, $end_date){
            return $q->whereRaw('DATE(t.timestamp) <= ?', [$end_date]);
        })
         ->when($receiver, function ($q, $receiver){
           
            return $q->where('t.outbound_location_id', $receiver);
        })
         ->when($clients, function ($q, $clients){
             
            return $q->whereIn('c.ClientID', $clients);
        })
        ->when($terminal, function ($q, $terminal){
           
            return $q->where('t.facility', $terminal);
        })
        ->when($load_type, function ($q, $load_type){
           
            return $q->where('t.load_type', $load_type);
        })
       ->when($page > 1, function ($q, $page){
            $offset = 10 * ($page - 1);
            return $q->offset($offset);
        }, function ($q){
            return $q->offset(1);
        })
        ->when(!$pdf, function($q){
            return $q->limit(10);
        })
        ->orderBy('t.Timestamp','desc')
        ->get();
0 likes
11 replies
tykus's avatar

That's not how you pass data into the Closure scope; use function ($query) use ($var) {

->when($start_date, function ($q) use ($start_date) {
    return $q->whereRaw('DATE(t.timestamp) >= ?', [$start_date]); //Y-m-d
})

or, use Short Closures

->when($start_date, fn ($q) => $q->whereDate('t.timestamp', '>=', $start_date))
1 like
tykus's avatar

@TuffRivers the Closure syntax use issue is PHP-related; not Laravel version. You might not be running a PHP version that supports short closures however (>= 7.4)

1 like
tykus's avatar

@TuffRivers it is how you are trying to pass variable like $start_date into the Closure's scope:

It should be like this:

->when($start_date, function ($q) use ($start_date) {

not like this:

 ->when($start_date, function ($q, $start_date){

This applies for all of the Closures that need data outside the Closure scope,

        ->when($material, function ($q) use ($material){
            $q->where('t.material_id', $material);
        })
        ->when($start_date, function ($q) use ($start_date){
            $q->whereDate('t.timestamp', '>=', $start_date);
        })
        ->when($end_date, function ($q) use ($end_date){
            $q->whereDate('t.timestamp', '<=', $end_date);
        })
         ->when($receiver, function ($q) use ($receiver){
            $q->where('t.outbound_location_id');
        })
         ->when($clients, function ($q) use ($clients){
            $q->whereIn('c.ClientID', $clients);
        })
        ->when($terminal, function ($q) use ($terminal){
            $q->where('t.facility', $terminal);
        })
        ->when($load_type, function ($q) use ($load_type){
            $q->where('t.load_type', $load_type);
        })
       ->when($page > 1, function ($q) use ($page){
            $offset = 10 * ($page - 1);
            $q->offset($offset);
        }, function ($q){
            $q->offset(1);
        })
        ->when(!$pdf, function($q){
            $q->limit(10);
        })
1 like
TuffRivers's avatar

@tykus unfortunately this still isnt working fo rme, my query returns no results everytime :(

when i do ->toSql() and copy that query to MySQL editor i replace the ? bindings and it works, so i have no idea why it keeps returning blanks for some parameter!!!

1 like
newbie360's avatar
Level 24

may be you can wrap your query into... for check the bindings value, or install Laravel Debugbar

        DB::enableQueryLog();

        // your query here

        dd(DB::getQueryLog());
1 like
TuffRivers's avatar

@newbie360 Thank you all for your help. I resolved my issue with help from @newbie360 - when debugging the log i saw that i was sending an empty binding for one of my inputs - as this was binding Clients to = "" - i was ALWAYS returning no results.

Thank you all for your help it was much appreciated.

Please or to participate in this conversation.