Eloquent query will only prepare, not execute

Question

This is for Laravel 5.2. My question is very similar to this question. Basically, I'm trying to run a query that was suggested to me in another question, which returns data when it actually executes:

>>> App\Models\User::whereRaw('CONCAT(name_first, " ", name_last) LIKE "%?%"', ['test'])->get()
=> Illuminate\Database\Eloquent\Collection {#769
     all: [],
   }

This is what the mysql log shows:

Prepare   select * from `users` where CONCAT(name_first, " ", name_last) LIKE "%?%" and `users`.`deleted_at` is null
Close stmt

However, this statement works:

>>> App\Models\User::whereRaw('CONCAT(name_first, " ", name_last) LIKE "%test%"')->get()
=> Illuminate\Database\Eloquent\Collection {#770
     all: [
       App\Models\User {#767
         id: 1,
         name_first: "test",
         name_middle: null,
         name_last: "user",
         email: "test@test.com",
       },
     ],
   }

And the associated mysql log entries:

Prepare   select * from `users` where CONCAT(name_first, " ", name_last) LIKE "%test%" and `users`.`deleted_at` is null
Execute   select * from `users` where CONCAT(name_first, " ", name_last) LIKE "%test%" and `users`.`deleted_at` is null
Close stmt

Any information or advice about why this is happening would be greatly appreciated.

score 1 · Accepted Answer · edited May 23 '17 at 12:15

Actually there's a small problem there (which was from my solution on your previous question). When you use this:

User::whereRaw('CONCAT(name_first, " ", name_last) LIKE "%?%"', ['test']);

The resulting SQL query string will be this (after the binding would be applied):

select * from `users` where CONCAT(name_first, " ", name_last) LIKE "%'test'%" and `users`.`deleted_at` is null

Since the value quoted would be test you'd end up with "%'test'%" which will match nothing. To fix that you should move the wildcard characters into the bindings array like so:

 User::whereRaw('CONCAT(name_first, " ", name_last) LIKE ?', ['%' . $value . '%']);

Now the value would be quoted correctly:

select * from `users` where CONCAT(name_first, " ", name_last) LIKE '%test%' and `users`.`deleted_at` is null

There is also the alternative of using DB::raw for places where you specifically want the parameter passed to the Query Builder to not be escaped. So in this case you could use the regular where like so:

User::where(DB::raw("CONCAT(name, ' ', email)"), 'LIKE', '%' . $value . '%');

That will make sure the concatenation part will not be escaped.

After putting the wildcards in the bindings array, everything worked perfectly. I can't believe I didn't think to try that on my own. I'm still confused why the statement was never executed even if the wrong value would have been used, though. Thanks for your help! — Anthony, Mar 08 '16 at 19:03
You're very welcome. I've fixed my answer to your previous question as well :). — Bogdan, Mar 08 '16 at 19:05

Eloquent query will only prepare, not execute

1 Answers1