laravel eager loading use parents column value inside nested relation

Question

Here is my scenario, I have 2 table Orders and Employees. They have many to many relationship, so there is a pivot table to connect them. Order table has a few fields such as date,in_time,oute_time etc.

Now I want to get all the employees for orders within a current week. I can do that easily with

$orders = Order::with(['employees'])
               ->currentWeek($fromDate, $tillDate)
               ->get()
               ->groupBy('date');

Here currentWeek is a scope where date range is calculated. What I need to do is have a nested relation with employees having all the orders on the same date, like

$orders = Order::with(['employees' => function ($q) {
            $q->with(['orders' => function ($q) {
                $q->where('child_order.date', '=', 'parent_order.date');
            }]);
          }])->currentWeek($fromDate, $tillDate)->get()->groupBy('date');

How do I access parent orders columns inside the nested?

Example: Order table has 2 fields, in_time and out_time. My purpose is to list all orders within a week and get all employees which has been added to an order. At the same time, I need to determine if an employee is added to an order whose in_time/out_time conflicts. Like, I have an order on dec-22 which starts at 3-30pm and ends at 9-30pm. Now, I need to list all employees for that order and list all the orders for each employees which matches the date and falls within the `in_time/out_time

Answer 1

This might not give you the exact answer your looking for, but have you tried approaching the problem using the Employee model? If you were to use the employee's model and its orders relationship, you could just query that. Like so

$orders = Employee::with('orders')
    ->whereHas(['orders' => function ($q) {
        $q->where('date', '<', $tillDate);
        $q->where('date', '>', $fromDate);
    })->get();

This would also mean you need to have a hasMany relationship on the Employees model like so

Employee model

public function orders()
{
    return $this->hasMany(Order::class);
}

Given your scenario you described in the comments, this would give you a collection with the 5 employees, and any orders they have made in between the dates.