Search code examples
sqllaravellaravel-5.3

How can I write complex sql query in Laravel Elequent Query Builder


Below shown is my query in SQL Server. It is working fine in SQL server studio. How can I write this in Laravel eloquent format?

I tried both DB::table('Price')... method and Price::where()... Model method. By using both methods I was not able to find the average.

Is this a limitation of the eloquent Query builder? What is the best way to get output for this in Laravel?

select  month([Date]) as Date, 
avg(Oil) as Oil,
avg(Gas) as Gas,
 from [mydatabase].[dbo].[Price] 
 where year([Date]) = 2017
group by (month([Date]))

This is the output I get from sql


Solution

  • You can acheive this using Raw expressions

      $query = DB::table('Price')
                             ->select(DB::raw('avg(Oil) as Oil, avg(Gas) as Gas, month'))
                             ->where('year', '==', '2017')
                             ->groupBy('month')
                             ->get();