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.
Leave a Comment