Passing MySQL functions to Eloquent ORM query

Question

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?

What are you trying to achieve exactly by YEAR(created_at)? And what is the value of ''''$year'''' usually? — Ibrahim AshShohail, Jan 10 '13 at 21:15
`$year` would be just that - a variable with the year, such as 2013. `created_at` is a MySQL timestamp column, `YEAR(created_at)` would return `2013`, for example. — NightMICU, Jan 11 '13 at 13:05

ULazdins · Answer 1 · 2015-05-21T06:21:46.813

35

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

edited May 21 '15 at 06:21

answered Jun 13 '13 at 12:36

ULazdins

1,975
4
25
31

2

This 3 years old post save my life today. Thank you so much good sir :-) – Imam Assidiqqi Dec 17 '16 at 05:52
This post help me when struggling on save data using mariadb dynamic column, thanks sir! – omdjin Sep 07 '17 at 06:56
2

@ImamAssidiqqi this post from ULazdins also saved me - and it's 2023. – skidadon Jan 04 '23 at 21:49

score 5 · Accepted Answer · answered Jan 10 '13 at 20:09

5

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.

answered Jan 10 '13 at 20:09

Mike Brant

70,514
10
99
103

I'm aiming to have a URL along these lines = http://example.com/blog/2013/01/01/blog-post, so I need some way to dissect the `created_at` column to filter the year, month, and day. – NightMICU Jan 10 '13 at 20:16
OK, but what does that have to do with the question? It seems your query was simply looking for the first post with a certain slug and a year and return the first one. If you needed to sort on Year and month, you would just change the like condition to `$year . '-' . $month` if you have the day value, you can add that as well (or perhaps just use `=` at that point of the DB field is a date field). It is probably not meaningful to do a WHERE on the month or day only, so just build your date criteria from left to right, allowing you to use the index on the field (you do have index right?) – Mike Brant Jan 10 '13 at 20:20
I'm trying to reduce the possibility of pulling a post with a duplicate slug, that's the goal here. – NightMICU Jan 10 '13 at 20:28
@NightMICU Why would you not just put a unique index on `slug` and just lookup using that value? Or treat the entire URI as a slug (again with unique index)? – Mike Brant Jan 10 '13 at 20:30

score 4 · Answer 3 · answered Sep 05 '17 at 16:40

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)

Alex Benincasa Santos · Answer 4 · 2017-06-03T04:52:59.430

1

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

edited Jun 03 '17 at 04:52

answered Jun 03 '17 at 03:13

Alex Benincasa Santos

850
8
9

1

[Stack Overflow generally works in English](https://stackoverflow.blog/2009/07/23/non-english-question-policy/); please make sure your post is as useful as possible to others by translating it. – Nathan Tuggy Jun 03 '17 at 03:41

Passing MySQL functions to Eloquent ORM query

4 Answers4

Linked