Using OR in a Where Clause with Laravel’s Query Builder

While upgrading to Laravel 5.3 last night, I ran across a case where I had used whereRaw() for a Select2 autocomplete lookup. It was pretty obvious I did this because I wanted to group two columns inside parenthesis and at the time I didn’t know how to do it with the query builder. Well, I do know now and I thought I’d share it here.

Let’s set the stage with the SQL I needed the query builder to produce:

SELECT
	id,
	CONCAT_WS(' ', first_name, last_name) AS text
FROM
	users
WHERE
	id = ?,
        affiliation = ?,
        active = ?
AND (
	first_name LIKE ?
	OR last_name LIKE ?
);

As you can see we have 4 conditions that must be met in order to return results. The 4th condition is met by matching either first_name OR last_name, but importantly, the preceding constraints must be met as well. Very straightforward SQL we’ve written a million times. But, we’re not writing raw SQL, we’re using Laravel’s query builder. Let’s knock it out.

After a bit of source code diving we find the orWhere() method and do something naive like::

$user = User::selectRaw("id, CONCAT_WS(' ', first_name, last_name) AS text")
    ->where('id', $id)
    ->where('affiliation', $request->affiliation)
    ->where('active', 1)
    ->orWhere('first_name', 'LIKE', '%' . $term . '%')
    ->orWhere('last_name', 'LIKE', '%' . $term . '%')
    ->toSql();

Go ahead dd($user) and to your dismay, you’ll see:

SELECT
	id,
	CONCAT_WS(' ', first_name, last_name) AS text
FROM
	users
WHERE
	id = ?
AND affiliation = ?
AND active = ?
OR first_name LIKE ?
OR last_name LIKE ?

Close but the problem is you aren’t constraining the query properly and will get any users that match the id, affiliation and active constraints OR any users where first_name matches OR any users where last_name matches. My first reaction when I saw that was to grab a sharp knife and use whereRaw() and be on my way. And, in fact, that’s exactly what I did. However, it’s not necessary and not very Eloquent. The trick is to pass a closure to where():

$user = User::selectRaw("id, CONCAT_WS(' ', first_name, last_name) AS text")
    ->where('id', $request->user()->id)
    ->where('active', 1)
    ->where('affiliation', $request->affiliation)
    ->where(function($q) use ($term) { // $term is the search term on the query string
       $q->where('first_name', 'LIKE', '%' . $term . '%')
       ->orWhere('last_name', 'LIKE', '%' . $term . '%');
    })
    ->toSql();

When we dd($user) now, we’ll see we have what we’re looking for:

SELECT
	id,
	CONCAT_WS(' ', first_name, last_name) AS text
FROM
	users
WHERE
	id = ?
AND affiliation = ?
AND active = ?
AND (
	first_name LIKE ?
	OR last_name LIKE ?
)

There you have it. Every time I’ve run into something I didn’t think the query builder could do, source code diving and closures have always been the answer.