Laravel Query Filtering Out Transactions and Checking Between Two Dates-CodePudding

My query is working for getting the existing transactions between two dates. However, the whereNotIn function is not filtering out the transaction. It still selects transactions that were either cancelled, declined or finished. What seems to be the problem on my query? Thank you

$associates = Associate::join('transactions', 'associate.associate_id', '=', 'transactions.associate_id')
            ->select('associate.associate_id')
            ->whereNotIn('status', ['Cancelled', 'Declined', 'Finished'])
            ->whereBetween('startdate',[$start_date, $end_date])
            ->orWhereBetween('enddate',[$start_date, $end_date])
            ->orWhere(function ($query) use ($request) {
                $start_date = new DateTime($request->input('start_date'));
                $end_date = new DateTime($request->input('end_date'));
                $query->where('startdate','>=',$start_date)
                  ->where('enddate','<=',$end_date);
            })
            ->get();

CodePudding user response：

you should orginize your 'wheres' to do exactly what you want, to do this, you can use where with closer:

  $associates = Associate::join('transactions', 'associate.associate_id', '=', 'transactions.associate_id')
            ->select('associate.associate_id')
            ->whereNotIn('status', ['Cancelled', 'Declined', 'Finished'])
            ->where(function ($query) use ($request, $end_date, $start_date) {
                $query->whereBetween('startdate',[$start_date, $end_date])
                    ->orWhereBetween('enddate',[$start_date, $end_date])
                    ->orWhere(function ($query) use ($request) {
                        $start_date = new DateTime($request->input('start_date'));
                        $end_date = new DateTime($request->input('end_date'));
                        $query->where('startdate','>=',$start_date)
                            ->where('enddate','<=',$end_date);
                    });
            });