Laravel Query with Implode-CodePudding

i have an array with values

Array
(
    [0] => Python
    [1] => PHP
    [2] => MySQL
)

So i want search these three items from tables using like query

$employees = DB::table('audit_employee_basics')
                    ->select('audit_employee_basics.id as empid', 'emp_name', 'emp_code', 'designation_name', 'emp_company_email_id', 'emp_contact_number', 'emp_gender', 'emp_location'
                            , 'department_name', 'emp_joining_date', 'fk_emp_previous_exp', 'image')
                    ->join('audit_department', 'audit_employee_basics.emp_fk_dep', '=', 'audit_department.id')
                    ->join('audit_employee_skillset', 'audit_employee_skillset.fk_emp_id', '=', 'audit_employee_basics.id')
                    ->join('audit_designation', 'audit_designation.id', '=', 'audit_employee_basics.emp_fk_des_id')
                    ->where('primary_skill ', 'like', '%' .  implode("' OR primary_skill LIKE '%", $skill_name) .'%')
                    ->distinct()
                    ->get();

I tried where('primary_skill ', 'like', '%' . implode("' OR primary_skill LIKE '%", $skill_name) .'%') which is not working for me.Any help would be highly appreciated.

CodePudding user response：

->where('primary_skill ', 'like', '%' . implode("' OR primary_skill LIKE '%", $skill_name) .'%')

Above line is incorrect since you can't inject multiple where inside a single where condition. QueryBuilder will just take that as a string for comparison.

You can use regexp to match each value with a regular expression created from your $skill_name array as

->where('primary_skill ', 'regexp', '(' .  implode("|", $skill_name) .')')

This would get compiled as

 where primary_skill regexp '(Python|PHP|MySQL)'

CodePudding user response：

implode() is not how the way one should build such DB queries in Eloquent/Laravel.

Instead it should look like this:

...
->where(function($query) {
  $query->where('primary_skill', 'like', '%Python%');
  $query->orWhere('primary_skill', 'like', '%PHP%');
  $query->orWhere('primary_skill', 'like', '%MySQL%');
})
...

Of course, you will need to make this dynamic, e.g. using a foreach.

...
->where(function($query) {
  foreach(['Python', 'PHP', 'MySQL'] as $keyword) {
    $query->orWhere('primary_skill', 'like', "%$keyword%");
  }
})
...

CodePudding user response：

You can do this with a foreach loop and ->orWhere.

$employees = DB::table('audit_employee_basics')
                    ->select('audit_employee_basics.id as empid', 'emp_name', 'emp_code', 'designation_name', 'emp_company_email_id', 'emp_contact_number', 'emp_gender', 'emp_location'
                            , 'department_name', 'emp_joining_date', 'fk_emp_previous_exp', 'image')
                    ->join('audit_department', 'audit_employee_basics.emp_fk_dep', '=', 'audit_department.id')
                    ->join('audit_employee_skillset', 'audit_employee_skillset.fk_emp_id', '=', 'audit_employee_basics.id')
                    ->join('audit_designation', 'audit_designation.id', '=', 'audit_employee_basics.emp_fk_des_id')
                    ->where(function ($query) use ($book, $skills_array) {
                          foreach($skills_array as $skill_name) {
                              $query->orWhere('primary_skill', 'LIKE', '%' . $skill_name . '%');
                          }
                    })
                    ->distinct()
                    ->get();

where $skills_array is your array.