Search string in multiple columns of relational table using Laravel whereRelation

Question

I have a relationship with spatial models,

public function user()
{
  return $this->belongsTo(User::class)->withTrashed();
}

In my controller, I want to search for a string from the "first_name" and "last_name" columns in the "users" table.

But I didn't find the syntax to write "or" condition in whereRelation.

$query = new Space();

$query = $query->with('user')->whereRelation('user', 'first_name', 'like', '%' . $request->search . '%');

How to use whereRelation to search for strings in multiple columns of a relational table?

Answer 1

Option 1. You can search from two fields separately:

Space::with('user')->whereHas('user', function ($query) use ($request) {
    $query->where('first_name', 'LIKE', '%' . $request->search . '%')
        ->orWhere('last_name', 'LIKE', '%' . $request->search . '%');
});

Option 2. I guess what you really want is to concatenate the two fields together (with a space in between) and then compare your search to that field:

use Illuminate\Support\Facades\DB;

// ...

Space::with('user')->whereHas('user', function ($query) use ($request) {
    $query->where(
        DB::raw('CONCAT(first_name, " ", last_name)'),
        'LIKE',
        '%' . $request->search . '%'
    );
});