Laravel WHERE scope on JOIN

Question

I am trying to get "shifts" and get all records I have, But I join it with orders and run where on orders by delivery_date here "the shift" which has no orders erased now I need to make the where only on orders and not affect on shifts

Shift::leftJoin('mt_plans', 'mt_plans.shift_id', '=', 'shifts.id')
   ->leftJoin('subscriptions', 'subscriptions.plan_id', '=', 'mt_plans.plan_id')
   ->join('orders', 'orders.subscription_id', '=', 'subscriptions.id')
   ->join('mt_order_details', 'mt_order_details.order_id', '=', 'orders.id')
   ->join('mt_item', 'mt_item.item_id', '=', 'mt_order_details.item_id')
   ->where('orders.delivery_date', $request->date)
   ->whereIn('orders.status', [OrderStatus::ACCEPTED, OrderStatus::ASSIGNED, OrderStatus::FAILED, OrderStatus::SUCCESSFUL])
   ->select([
  'mt_item.item_id as meal_id',
                'mt_item.en_name',
                'mt_item.color',
                'mt_item.label_number',
                'shifts.id as shift_id',
                'shifts.title',
                'shifts.start_time',
                'shifts.end_time',
                'shifts.pickup_time'
   ])
   ->get();

Answer 1

You need a left join where your condition applies:

Shift::leftJoin('mt_plans', 'mt_plans.shift_id', '=', 'shifts.id')
   ->leftJoin('subscriptions', 'subscriptions.plan_id', '=', 'mt_plans.plan_id')
      ->leftJoin('orders', function($q) use($request) {
           $q->on('orders.subscription_id', '=', 'subscriptions.id');
           $q->where('orders.delivery_date', '=', $request->date);
      })
      ->leftJoin('mt_order_details', 'mt_order_details.order_id', '=', 'orders.id')
      ->leftJoin('mt_item', 'mt_item.item_id', '=', 'mt_order_details.item_id')
      ->whereIn('orders.status', [OrderStatus::ACCEPTED, OrderStatus::ASSIGNED, OrderStatus::FAILED, OrderStatus::SUCCESSFUL])
      ->select([
           'mt_item.item_id as meal_id',
            'mt_item.en_name',
            'mt_item.color',
            'mt_item.label_number',
            'shifts.id as shift_id',
            'shifts.title',
            'shifts.start_time',
            'shifts.end_time',
            'shifts.pickup_time'
       ])
      ->get();

Then you keep all "shifts" but join only order with delivery_date condition. Keep in mind that it will retrieve all "shifts" where where condition applies. The orders will be null if doesn't match the id and delivery_date