I am working with laravel eloquent query with this case statement structure (sum time difference between start and end time, if records created within a current week in timelogs table and project is billable in projects table)result return always zero. please help I don't understand.
$billable_data = Project::where(['active' => 1, 'company_id' => session('company_id'), 'is_deleted' => 0])
->join('timelogs','timelogs.project_id','=','projects.id')
->select(DB::raw('CASE WHEN (billable = 1 AND timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59") THEN SUM(TIMESTAMPDIFF(second, timelogs.start_time, timelogs.end_time)) ELSE 0 END as billable_hours,
CASE WHEN (billable = 0 AND timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59") THEN SUM(TIMESTAMPDIFF(second, timelogs.start_time, timelogs.end_time)) ELSE 0 END as non_billable_hours'))
->first()->toArray();
dd result of $billable_data :
array:3 [▼
"billable_hours" => "0"
"non_billable_hours" => "0"
]
But when I used this raw query within DB::raw everything worked fine (return time sum correctly within a week).
$billable_data = Project::where(['active' => 1, 'company_id' => session('company_id'), 'is_deleted' => 0])
->join('timelogs','timelogs.project_id','=','projects.id')
->select(DB::raw('(SELECT SUM(TIMESTAMPDIFF(second, start_time, end_time)) FROM timelogs WHERE timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59" AND projects.billable = 0 GROUP BY projects.id) as non_billable_hours,
(SELECT SUM(TIMESTAMPDIFF(second, start_time, end_time)) FROM timelogs WHERE timelogs.created_at BETWEEN "'.$monday.' 00:00:00" AND "'.$friday.' 23:59:59" AND projects.billable = 1 GROUP BY projects.id) as billable_hours'))
->first()->toArray();
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire