CODEWITHSUNDEEP
phpdatabaselaraveleloquent
NightMICU
NightMICU

Reputation: 9230

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 - 

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

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?

Upvotes: 10

Views: 11537

Answers (4)

vipmaa
vipmaa

Reputation: 1122

You have two ways to do this and its depend on your laravel version

First use Raw method to pass function as below example

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

ِAlso you can use it in (select,group,order) Methods, for more information about Raw Laravel Docs its start from v 4.2

Second use whereYear Method

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

This method start from V 5.3 for more information Read Where section you will found all methods for dates (whereDate / whereMonth / whereDay / whereYear)

Upvotes: 4

Alex Benincasa Santos
Alex Benincasa Santos

Reputation: 860

I use Laravel 5.3

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

This guy helped me >> https://stackoverflow.com/a/32843415/7752468

Upvotes: 1

ULazdins
ULazdins

Reputation: 2025

To prevent Eloquent ORM from wrapping first variable with apostrophes, you can use DB:raw function like:

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

And you'll get query:

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

Upvotes: 36

Mike Brant
Mike Brant

Reputation: 71384

You probably don't want to use the YEAR() function in your WHERE clause anyway. This would prevent you from using any index on the created_at column. I would suggest you use LIKE instead:

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

You can also just use raw SQL queries as well (using query() method) if you had need to utilize unsupported MySQL functions in your queries.

Upvotes: 5

Related Questions