how to make a multiple filter query?

i have this code to filter

public function viewType(Request $request, ReportViewAll $reportview){

        $reportview = $reportview->newQuery();
        if ($request->has('program')) {
            if($request->input('program') == 'reportall'){
                $reportview;
            }
            elseif($request->input('program') == 'reportactive'){
                $reportview->where('crewprogram_isdisabled', '=',0);
            }
            elseif($request->input('program') == 'reporthistory'){
                $reportview->where('crewprogram_isdisabled', '=',1);
            }
        }

        if(($request->has('datestart') && $request->has('dateend'))){
            $reportview->whereBetween('crewprogrammemo_placement_date', 
            array($request->input('datestart'), $request->input('dateend')));
        }

        if($request->has('search')){
            $reportview->where ( 'employee_nik', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'employee_nama', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_focus_id', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_name', 'LIKE', "%{$request->search}%" );
        }

        return $reportview->get();

        // return view('CrewProgram.ReportView.index', compact('reportviewall'));
    }

the only working query is

  if($request->has('search')){
            $reportview->where ( 'employee_nik', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'employee_nama', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_focus_id', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_name', 'LIKE', "%{$request->search}%" );
        }

how can i make all of those query working? because when i try to use program, and date query i didn't get the value that i want

kalemdzievski

6 years ago

Best Answer

Level 6

The mistake is probably in that search query because of the orWhere statements. Imagine that you've sent for example program with value reporthistory and search with value 'test right? So your end result (query) will look something like:

$reportview->where('crewprogram_isdisabled', '=',1)
	->where ( 'employee_nik', 'LIKE', "%{$request->search}%" )
	->orWhere ( 'employee_nama', 'LIKE', "%{$request->search}%" )
	->orWhere ( 'show_focus_id', 'LIKE', "%{$request->search}%" )
	->orWhere ( 'show_name', 'LIKE', "%{$request->search}%" );

In SQL this will look like:

SELECT * FROM <table> WHERE crewprogram_isdisabled = 1 AND employee_nik LIKE '%test%' OR employee_nama LIKE '%test%' OR show_focus_id LIKE '%test%' OR show_name LIKE '%test%'

Because you have orWhere statements everything that fulfills those conditions will be returned no meter if the where conditions are met.

You should group the where statements.

if($request->has('search')){
            $reportview->where(function($query) use ($request) {
                $query->where ( 'employee_nik', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'employee_nama', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_focus_id', 'LIKE', "%{$request->search}%" )
                    ->orWhere ( 'show_name', 'LIKE', "%{$request->search}%" );
            });
        }

So for example your SQL will look like:

SELECT * FROM <table> WHERE crewprogram_isdisabled = 1 AND (employee_nik LIKE '%test%' OR employee_nama LIKE '%test%' OR show_focus_id LIKE '%test%' OR show_name LIKE '%test%')

Please or to participate in this conversation.