Search code examples
phplaravellaravel-5eloquentsql-like

Applying string function in column Laravel 5.4

I'm using the latest Laravel 5.4

I am trying to make a simple query to search users by name. The query written for MySQL looks like this:

SELECT *
FROM users
WHERE upper(name) LIKE '%FOO%';

I'm trying to make it work with Eloquent. Things I've tried but failed:

  1. User::where('upper(name)', 'LIKE', '%FOO%')->get()

  2. DB::table('users')->where('upper(name)', 'LIKE', '%FOO%')->get()

Both fail with the following error:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'upper(name)' in 'where clause' (SQL: select * from users where upper(name) LIKE %FOO%)'

The query seems to fail because Eloquent wraps the upper(email) statement with backticks (" ` ", " ` "). Is there a way to go around this issue or do I have to use a particular eloquent function to get convert a column to uppercase, lowercase, e.t.c?

Solution

  • Use DB::raw()

    User::where(DB::raw('upper(name)'), 'LIKE', '%FOO%')->get()

    It would generate query like this 

    "select * from `users` where upper(name) LIKE ?"