在Laravel 5.4列中应用字符串函数

Question

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?

Answer 1

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 ?"