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

Question

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

Sanzeeb Aryal · Accepted Answer · 2017-01-09 12:50:02Z

1

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

answered Jan 9, 2017 at 12:50

Sanzeeb Aryal

3,2663 gold badges23 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Razi Kallayi · Accepted Answer · 2017-01-09 12:47:20Z

0

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);

answered Jan 9, 2017 at 12:47

Razi Kallayi

91010 silver badges19 bronze badges

Comments

Binit Ghetiya · Accepted Answer · 2017-01-09 12:59:30Z

0

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

answered Jan 9, 2017 at 12:59

Binit Ghetiya

1,9792 gold badges22 silver badges33 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related