SQL query with two orWhere-CodePudding

I need your help.

I have a problem with a sql query that I can't do.

I have two tables, patients and user, in the patients table I have the fields, name,last name, personal_id, strikes and in the users table I have a field called status. I clarify, personal_id is a code assigned by the company, different from the PK.

In system logic, a user can be 'disabled' for two reasons.

1.-The administrator put the user's status in Inactive 2.-The user has 3 strikes or more.

So the query I am trying to do is the following.

Given a value, return me all the users that match in personal_id OR last name AND WHAT ALSO users are INACTIVE (Either for reasons 1 or 2)

$patients= DB::table('patiens')
    ->join('users','patiens.User_ID','=','users.id')
    ->where('users.status','=','Inactive')
    ->orwhere('patiens.strikes','>=',3)
    ->where('patiens.last_name','like','%'.$date.'%')
    ->orwhere('patiens.personal_id','like','%'.$date.'%')
    ->get();

basically I need my query to fulfill this:

 This...
 ->where('users.status','=','Inactive')
    ->orwhere('patiens.strikes','>=',3)

 AND ALSO 

 ->where('patiens.last_name','like','%'.$date.'%')
 ->orwhere('patiens.personal_id','like','%'.$date.'%')

Basically, which first extracts All Inactive and then in that list look for all matches

CodePudding user response：

You can nest the conditions within a callback:

$patients= DB::table('patiens')
    ->join('users','patiens.User_ID','=','users.id')
    ->where(function ($query) { 
           $query->where('users.status','=','Inactive')
              ->orwhere('patiens.strikes','>=',3);
    })->where(function ($query) use ($date) {
         $query->where('patiens.last_name','like','%'.$date.'%')
           ->orwhere('patiens.personal_id','like','%'.$date.'%');
    })
    ->get();