Eloquent Laravel multiple like where and where clause

Question

I have the below Mysql query that works.

mysql:

select * from operatories op
left join locations al on op.location = al.location
where op.opname like '%NP%' or op.opname like '%OPEN%'
and  al.isOfficeClosed = 0;

This works and i get the data that I expected but I am trying to write that in Laravel query builder but its not picking up the last and al.isOfficeClose = 0. is there anything you all can catch and see that I am doing wrong?

Laravel:

    $locs = DB::table('operatories as op')
        ->leftJoin(DB::raw('locations al'), function($join) {
            $join->on('op.Location', '=', 'al.location');

        })
        ->Where('op.opname','LIKE','%NP%')
        ->orWhere('op.opname','LIKE','%OPEN%')
        ->where('al.isOfficeClosed', '=','0');

Maybe the uppercasse on `op.Location` makes the join fail? – porloscerros Ψ Oct 30 '20 at 21:25 — porloscerros Ψ, Oct 30 '20 at 21:25

score 0 · Accepted Answer · answered Oct 30 '20 at 21:45

You can define a function for where clause, like this:

$locs = DB::table('operatories as op')
    ->leftJoin(DB::raw('locations al'), function($join) {
        $join->on('op.Location', '=', 'al.location');

    })
    ->where(function($q) { 
         $q->where('op.opname','LIKE','%NP%');
         $q->orWhere('op.opname','LIKE','%OPEN%');
    })
    ->where('al.isOfficeClosed', '=','0');

By the way,

select * from operatories op
left join locations al on op.location = al.location
where op.opname like '%NP%' or op.opname like '%OPEN%'
  and  al.isOfficeClosed = 0;

is different than (mind the parenthesis)

select * from operatories op
left join locations al on op.location = al.location
where (op.opname like '%NP%' or op.opname like '%OPEN%')
  and  al.isOfficeClosed = 0;

Eloquent Laravel multiple like where and where clause

1 Answers1