Laravel Query with Implode

Question

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.

mighty_dev · Accepted Answer · 2022-06-24 11:23:19Z

1

->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)'

answered Jun 24, 2022 at 11:23

mighty_dev

17.8k3 gold badges24 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zoli Szabó · Accepted Answer · 2022-06-24 11:16:34Z

0

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%");
  }
})
...

answered Jun 24, 2022 at 11:16

Zoli Szabó

4,5851 gold badge16 silver badges22 bronze badges

Comments

Laralex · Accepted Answer · 2022-06-24 11:18:45Z

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.

Collectives™ on Stack Overflow

Laravel Query with Implode

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related