CODEWITHSUNDEEP
phplaraveleloquent
M.Izzat
M.Izzat

Reputation: 1166

PHP query all column based on one text value filter

I'm trying to create a text field filter where its value can query back all the data that has the filter value from all column of a table. For now my existing code can only query from column which is the users.name Below is my current code :

$employeeDetails = DB::table('employees')
                            ->join('users', 'users.id', '=', 'employees.user_id')
                            ->leftjoin('cost_centres', 'cost_centres.id', '=', 'employees.cost_centre_id')
                            ->leftjoin('departments', 'departments.id', '=', 'employees.department_id')
                            ->leftjoin('sections', 'sections.id', '=', 'employees.section_id')
                            ->leftjoin('employee_positions', 'employee_positions.id', '=', 'employees.position_id')
                            ->leftjoin('teams', 'teams.id', '=', 'employees.team_id')
                            ->leftjoin('categories', 'categories.id', '=', 'employees.category_id')
                            ->leftjoin('branches', 'branches.id', '=', 'employees.branch_id')
                            ->leftjoin('areas', 'areas.id', '=', 'employees.area_id')
                            ->leftjoin('employee_grades', 'employee_grades.id', '=', 'employees.grade_id')
                            ->leftjoin('attendance_group_area','attendance_group_area.area_id','employees.area_id')
                            ->whereIn('employees.id', $leaveTransactionEmployees)
                            ->where('users.name', 'LIKE', '%'.$filterValue.'%')
                            ->select('employees.*','users.name as name', 'cost_centres.name as costCentre', 'departments.name as department', 'sections.name as section',
                                'employee_positions.name as position', 'teams.name as team', 'areas.name as area', 'employee_grades.name as grade',
                                'categories.name as category', 'attendance_group_area.attendance_group_id as attendance_group_id')
                            ->get();
        return response()->json($employeeDetails);

as you can see the only the filter I have right now is this and its only filter for 1 column

->where('users.name', 'LIKE', '%'.$filterValue.'%')

How can I use the same value to filter all the column in all the table I leftjoined if the value exist in any of my column.

Upvotes: 2

Views: 440

Answers (2)

kmoser
kmoser

Reputation: 9273

You can CONCAT() the fields you want to search, and use LIKE to match on that concatenated result:

->where('CONCAT( ' . join( ', " ", ', [ 'users.name', 'cost_centres.name', 'departments.name' ] ). ' )', 'LIKE', '%'.$filterValue.'%')

This results in:

->where(CONCAT( users.name, " ", cost_centres.name, " ", departments.name ), 'LIKE', '%'.$filterValue.'%')

You can also use MySQL's CONCAT_WS() function to concatenate the columns, although it doesn't really shorten your code and I don't think it will affect performance noticeably:

->where('CONCAT_WS( " ", ' . join( ', ', [ 'users.name', 'cost_centres.name', 'departments.name' ] ) . ' )', 'LIKE', '%'.$filterValue.'%')

Of course, you'll want to change this array [ 'users.name', 'cost_centres.name', 'departments.name' ] to include all the columns you want to search in.

Note that depending on the value of $filterValue it may match on boundaries between concatenated columns. To avoid this, replace " " in the join() with some sentinel string that is unlikely to be present in the search string, e.g. "###".

Upvotes: 1

Karol Sobański
Karol Sobański

Reputation: 434

Just use orWhere.

$collection->where('sth', '=', '%'.$xxxx.'%')
           ->orWhere('sth_other', '=', '%'.$xxxx.'%')
           ->orWhere(...)
           ...
           ->get();

Upvotes: 2

Related Questions