CODEWITHSUNDEEP
phplaravellaravel-5eloquentlaravel-query-builder
FunkyMonk91
FunkyMonk91

Reputation: 1481

Laravel Eager Load Constraint issue

Edit: For clarity, this is Laravel 5.8.

This is for a HR app I'm working on.

They requested a report to show people who have punched in late. Sure, no problem I thought.

So I have a form with some custom parameters the user can punch in, start_date, end_date, wage, and an array of departments.

public function show()
{
    request()->validate([
        'start_date' => 'required|date|before_or_equal:today'
    ]);

    $start = Carbon::parse(request('start_date'));
    $end = request('end_date') ? Carbon::parse(request('end_date')) : today();
    $wage = request('wage');
    $departments = request('departments');

    $query = EmployeePunch::with([
        'employee' => function($query) use ($wage, $departments) {
            // IF I UN COMMENT THESE, IN THE FILTER BLOCK BELOW, THE EMPLOYEE BECOMES UNDEFINED.
            // if($wage != null) {
            //     $query->where('hourly', $wage);
            // }

            // if($departments) {
            //     $query->whereIn('department_id', $departments);
            // }
        },
        'employee.group',
        'employee.department'
    ])
    ->whereBetween('punch_time', [$start->startOfDay(), $end->endOfDay()])
    // only care about punch in for the day
    ->where('type', 1);

    $results = $query->get();

    $latePunches = $results->filter(function ($i) {
        $day = strtolower($i->punch_time->format('D'));
        $startTime = Carbon::parse(sprintf('%s %s', 
                                            $i->punch_time->format('d-m-Y'), 
                                            $i->employee->group[$day.'_start_time'])
                    );

        return $i->punch_time->isAfter($startTime) 
                && $i->punch_time->diffInMinutes($startTime) >= 5;
    });

    return view('hr.employeeLateReport.show', compact('latePunches'));
}

So, my problem is in my eager loading and I can't figure this out. If I uncomment the filters in the eager loading of employees, in the filter block near the end of the code block, the $i->employee becomes undefined. If omit the filters, everything works peachy. I've checked the queries being produced and it all looks great.

Any help would be greatly appreciated.

Here's the relationship methods

Employee.php

public function punches()
{
    return $this->hasMany(EmployeePunch::class);
}

public function group()
{
    return $this->belongsTo(Group::class);
}

public function department()
{
    return $this->belongsTo(Department::class)->withDefault();
}

EmployeePunch.php

public function employee()
{
    return $this->belongsTo(Employee::class);
}

SQL Output

sql

Upvotes: 3

Views: 228

Answers (1)

Farrukh Ayyaz
Farrukh Ayyaz

Reputation: 304

Try and use whereHas and nest whereBetween:

$query = EmployeePunch::with([
    'employee' => function($query) use ($wage, $departments) {
        if($wage != null) {
            $query->where('hourly', $wage);
        }

        if($departments) {
            $query->whereIn('department_id', $departments);
        }
    },
    'employee.group',
    'employee.department'
])->whereHas('employee', function($q) use($start, $end) {
    $q->whereBetween('punch_time', [$start->startOfDay(), $end->endOfDay()]);
})->where('type', 1);

Upvotes: 5

Related Questions