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

0
votes

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

3
sqllaravellaravel-5.3

3 Answers

1
votes

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();
0
votes

I got the answer

$result = DB::select(DB::raw("select  month([Date]) as Date, 
avg(Oil) as Oil,
avg(Oil_Parity) as Gas,
 from [mydatabase].[dbo].[Price] 
 where year([Date]) = :year
group by (month([Date]))"),['year'=>$year]);

dd($result);
0
votes

You can use laravel's built in function DB::raw("complex query goes here").

For your query something like below can be possible

$data = DB::raw("select  month([Date]) as Date, 
avg(Oil) as Oil,
avg(Gas) as Gas,
 from [mydatabase].[dbo].[Price] 
 where year([Date]) = ?
group by (month([Date]))", ['2017']);

For DB::raw() More Check Here