YEAR(begin_date) unknown column in laravel

1
votes
SELECT COUNT(*) as count, MONTH(begin_date)
FROM `events`
WHERE (YEAR(begin_date) = YEAR(CURDATE())) 
      OR (YEAR(begin_date) = YEAR(CURDATE()) + 1)  
GROUP BY MONTH(begin_date)

Here is sql query, i want to write it in laravel eloquent.

what i try:

  $oncoming_events = DB::table('events')    
        ->select(DB::raw('count(*) as numOfOncomingEvents, MONTH(begin_date)'))    
    ->where('YEAR(begin_date)', '=',  'YEAR(CURDATE())')
    ->orWhere('YEAR(begin_date)', '=', 'YEAR(CURDATE()) +1')
    ->groupBy('MONTH(begin_date)')->get();

Error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'YEAR(begin_date)' in 'where clause' (SQL: select count(*) as numOfOncomingEvents, MONTH(begin_date) from events where YEAR(begin_date) =

laravel 5.6

btw sql query works..

1
phplaravel
you can use whereYear('begin_date', date('Y')) – RainDev
what is date('Y') ? – Nevermore
it returns current year – RainDev

1 Answers

2
votes

You need to use DB::raw() in where to tell the query builder that it is not column name it is data manipulation in query,

$oncoming_events = DB::table('events')->select(DB::raw('count(*) as numOfOncomingEvents, MONTH(begin_date)'))->where(DB::raw('YEAR(begin_date)'), '=', 'YEAR(CURDATE())')->orWhere(DB::raw('YEAR(begin_date)'), '=', 'YEAR(CURDATE()) +1')->groupBy(DB::raw('MONTH(begin_date)'))->get();