orWhere not working with Laravel eloquent relationship query

Question

In Laravel, I have two tables named Booking and Clients. For a specific booking, I have belongsTo relation with a single Client. Everything works well when I query a client for a booking except when I include orWhere. Please look at the code snippet below.

if($client_name!=null)
        {
            $client_name='%'.$client_name;
            $bookings=$bookings->whereHas('client',function($q) use ($client_name){
                $q->where('first_name','LIKE',$client_name)
                    //->orWhere('last_name','LIKE',$client_name);                 
            });
        }

With the commented out orWhere line, I get proper bookings with the client with first_name as specified by user. But when I use or in the query for last_name, all the rows are displayed as if last_name matched for every row.

What's wrong with it? Help please.

Answer 1

The problem is that you have such code now:

WHERE foreignKey = X AND first_name = Y OR last_name = Z

So obviously it's not returning what it should.

Now, to make it work, you need to add those constraints as a sub where:

$bookings=$bookings->whereHas('client',function($q) use ($client_name){
            $q->where( function ($q) use ($client_name) {
                $q->where('first_name','LIKE',$client_name)
                    ->orWhere('last_name','LIKE',$client_name);
            });
        });

This will result in a query like:

WHERE foreignKey = X AND (first_name = Y OR last_name = Z)

which is what you need.