dani94
5 months ago

Help with eloquent query with a calculated field

Posted 5 months ago by dani94

Hey guys,

I got a model Event which has a Report and this report has many Workers. Well, im trying to get all workers with reports in a date ($year + $month) with the time than they spend in this reports, this is calculated field:

    public function getMinutesAttribute()
    {
        $start = Carbon::parse($this->start_at);
        $end = Carbon::parse($this->end_at);

        return $start->diffInMinutes($end);
    }

With next eloquent query, I get reports where has events in a date and counter. But i need also get the time spend. I know I cant use my minutes accessor because it doesnt exists in database. Just I need how to do the callback in the eloquent query to calculated it.

$workers = Worker::whereHas('reports', function ($query) use ($year, $month) {
            $query->join('events', 'reports.event_id', '=', 'events.id')
                ->whereYear('events.date', '=', $year)
                ->whereMonth('events.date', '=', $month);
   })->withCount(['reports' => function ($query) use ($year, $month) {
            $query->join('events', 'reports.event_id', '=', 'events.id')
                ->whereYear('events.date', '=', $year)
                ->whereMonth('events.date', '=', $month);
   }])->with('media.model')
   ->get();

I've only managed to get the reports but not a sum of their start_at and end_at diffeference.

        $workers = Worker::whereHas('reports', function ($query) use ($year, $month) {
            $query->join('events', 'reports.event_id', '=', 'events.id')
                ->whereYear('events.date', '=', $year)
                ->whereMonth('events.date', '=', $month);
        })->withCount(['reports' => function ($query) use ($year, $month) {
            $query->join('events', 'reports.event_id', '=', 'events.id')
                ->whereYear('events.date', '=', $year)
                ->whereMonth('events.date', '=', $month);
        }])->with(['media.model', 'reports' => function ($query) use ($year, $month) {
            $query->join('events', 'reports.event_id', '=', 'events.id')
                ->whereYear('events.date', '=', $year)
                ->whereMonth('events.date', '=', $month);
        }])
            ->get();

Any idea?

Please sign in or create an account to participate in this conversation.