CODEWITHSUNDEEP
phplaravel
Ahsan
Ahsan

Reputation: 1359

Laravel sum from relationship with condition

I have the following query where I have a conditional query with relation between two models. I need sum of column hours from the attendance where the conditions are met. I have tried the following, but it's not working.

$users = User::with('attendance')
            ->whereHas('attendance', function (Builder $query) use ($end, $start) {
                $query->whereBetween('date', [$start, $end])
                ->where('status', 2)
                ->select(DB::raw('SUM(hours) as h'));
            })->orderBy('name')
            ->where('status', 0)
            ->get();

In my blade

@foreach($users as $user)
    {{ $user->h }}
@endforeach

Please help

Upvotes: 5

Views: 9020

Answers (2)

Othmane Nemli
Othmane Nemli

Reputation: 1193

User::whereHas('attendance')
            ->withSum(['attendance' => function ($query) use ($start, $end){
                $query->whereBetween('date', [$start, $end])
                    ->where('status', 2);
            }], 'hours')
            ->get();

you can access sum hours using attendance_sum_hours property ({relation}_{function}_{column})

$user->attendance_sum_hours

Tip:

one more thing; $query->whereBetween('date', [$start, $end]) be carefull when using whereBetween on datetime column because will compare also the time, so the results won't be favorable use whereBetweenColumn('date(date)', [$start, $end])

Upvotes: 8

Jacob Brassington
Jacob Brassington

Reputation: 257

I do not think you can do this with whereHas, but this is how you would do it using joins. This would return all users that have an attendance between 2 dates and then give you the count.

$users = User::with('attendances')
    ->selectRaw('users.*, SUM(attendances.hours) as hours_sum')
    ->leftJoin('attendances', 'users.id', 'attendances.user_id')
    ->whereBetween('attendances.date', [$startDate, $endDate])
    ->groupBy('users.id')
    ->get();

However, if you want to return all users but if they have no attendance it will return 0 you can do the following

    $users = User::with('attendances')
    ->selectRaw('users.*, COALESCE(SUM(attendances.hours), 0) as hours_sum')
    ->leftJoin('attendances', function (JoinClause $joinClause) {
        $joinClause->on('users.id', 'attendances.user_id')
            ->whereBetween('attendances.date', [$startDate, $endDate]);
    })
    ->groupBy('users.id')
    ->get();

Upvotes: 1

Related Questions