Laravel Eloquent Sum difference dates about relationship column

Question

I have an issue, maybe you can help me.

In my model, 1 Event has N Reports, then 1 Report has M Workers. I need to query workers who has reports in a concrete event date (month + year) with count of reports in this date and time total time wasted in all reports during this date. Time wasted is report start_at - report end_at.

My query is working, except time wasted. I found a nice trick in #2 Laravel Eloquent Sum of relation's column making a count to sum, but its not working to me.

$workers = Worker::whereHas('reports', function ($query) use ($month, $year) {
    $query->duringMonth($month, $year);
})->withCount(['reports' => function ($query) use ($month, $year) {
    $query->duringMonth($month, $year);
}, 'reports AS total_minutes' => function ($query) use ($month, $year) {
    // here is the problem
    $query->select(DB::raw('SUM(DATEDIFF(start_at, end_at)) as totalminutes'))->duringMonth($month, $year);
}])->with('media.model')->get()

In this query total_minutes is always 0.

If I swap the conflict lines to

$query->selectRaw('SUM(DATEDIFF(start_at , end_at)) AS minutes')
    ->duringMonth($month, $year);;

total_minutes is always the number of relationships instead of the minutes.

If you need to see duringMonth scope:

public function scopeDuringMonth($query, $month, $year)
{
    return $query->join('events', 'reports.event_id', '=', 'events.id')
           ->whereYear('events.date', '=', $year)
           ->whereMonth('events.date', '=', $month);
}

score 0 · Accepted Answer · answered Jan 26 '20 at 13:05

I solved the problem appending a new field in the model, 'duration', which is the difference in minutes from start_at to end_at.

class Report extends Model
{

   protected $appends = ['duration'];


    public function duration()
    {
        return minutesToString($this->minutes);
    }

    public function scopeWithMinutes($query)
    {
        $query->selectRaw('reports.*, DATEDIFF(start_at , end_at) AS minutes');
    }

Laravel Eloquent Sum difference dates about relationship column

1 Answers1