Laravel SQL Query with similar words search

Question

What I am trying to do:

Hello, I'm trying to build an SQL Query in Laravel, with the following criteria:

The user will enter values into 3 search parameters:

(s1) Company Name

(s2) Department

(s3) UserName / UserEmail

I have to write a laravel query that will get the "user_id", "company_name", "department", "user_name", "email", "manager_type" of all users who:

• Have (s1) in their company name name, and

• Have (s2) in their department name, and

• Have (s3) in either their email or user name.

My Implementation:

$list = DB::  table("company")
            ->leftJoin("user", "company.company_id", "=", "user.company_id")
            ->select(
                "user.user_id",
                "company.company_name",
                "user.department",
                "user.user_name",
                "user.mail",
                "user.manager_type",
            )
            ->where(
                "company.company_name", "LIKE", "%".$companyName."%", "AND",
                "user.department", "LIKE", "%".$department."%", "AND",
                "(", "user.user_name", "LIKE", "%".$name_or_mail."%", "OR", "user.mail", "LIKE", "%".$name_or_mail."%", ")" 
            )
            ->whereNull("user.deleted_at")
            ->orderBy('user.created_at', 'desc')
            ->get();

The Output: While this query runs, it gives only partially correct answers. Some of the values are also irrelevant to the query. I am really new to laravel, and would appreciate all the help I can get.

Answer 1

Use where closure like this:

DB::table("company")
    ->leftJoin("user", "company.company_id", "=", "user.company_id")
    ->select(
         "user.user_id",
         "company.company_name",
         "user.department",
         "user.user_name",
         "user.mail",
         "user.manager_type",
     )->where("company.company_name", "LIKE", "%".$companyName."%")
      ->where("user.department", "LIKE", "%".$department."%")
      ->where(function ($query) use ($name_or_mail){
           $query->where("user.user_name", "LIKE", "%".$name_or_mail."%")
                 ->orWhere("user.mail", "LIKE", "%".$name_or_mail."%");
      })->whereNull("user.deleted_at")
        ->orderBy('user.created_at', 'desc')
        ->get();