how to Passing MySQL functions to Eloquent ORM query?

Question

Passing MySQL functions to Eloquent ORM query

I am trying to execute the following query in Eloquent ORM and cannot seem to execute the MySQL function.

The exception I am getting is as follows - Message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'YEAR(created_at)' in 'where clause'

SQL: SELECT * FROM `posts` WHERE `slug` = ? AND `YEAR(created_at)` = ? LIMIT 1

Bindings: array (
  0 => 'placeholder',
  1 => 2013,
)

So, basically, it is encapsulating the YEAR() MySQL function as a column. Is there any way to do this without using a raw query?

Answer 1

To use a function in eloquent, you can use RAW as the below code.

$post = Post::where('slug', $slug)
     ->where(DB::raw('YEAR(created_at)'), $year)
     ->first();

But if you want to use date functions and you using the laravel 5.3 or higher, just use the builtin function like (whereDate / whereMonth / whereDay / whereYear) as below code.

$post = Post::where('slug', $slug)
      ->whereYear('created_at', $year)
      ->first();

For more information about RAW read this, and for more information about the builtin functions read this.