How can I find the mix of 2 fields with a like in SQL/Laravel?

I have a table posts which contains posts (id field, candidate_id field and text field). I also have a candidate table (id, first_name, last_name).

Now, I want to implement a method that would let me put some text, for example: “Mike Doe” and it would give me all the posts that contain “Mike Doe” or that the candidate first_name + last_name contains “Mike Doe”.

So, I know how to serach if it is in the text, in the first_name or the last_name. However, let’s say I have “Mike Doe” and the first_name is Mike and the last name is Doe, it won’t be retrieved since it searches for “Mike Doe” in every field.

How can I, using SQL/Laravel, search for first_name + last_name and not the fields individually?

Here’s my code so far:

    public function search(Request $request) {
        Post::where('text', '%', $request->search)->orWhereHas('candidate', function ($query) use ($request) {
            $query->where('first_name', '%', $request->search)->orWhere('last_name', '%', $request->search);
        });
    }

Thanks!

Answer

Use MySQL’s CONCAT() function (or the equivalent in your dbms of choice.

public function search(Request $request) {
    Post::where('text', 'like', "%$request->search%")
        ->orWhereHas('candidate', function ($query) use ($request) {
            $query->whereRaw('CONCAT(first_name, ' ', last_name) LIKE "%?%"', [$request->search])
        });
    }