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();