Hope all is well.
Anyone can tell me how can I get to run this SQL query in a the right way in Laravel?
Controller:
$data=DB::raw("SELECT name as 'name' FROM invoices WHERE country='$country'; SELECT SUM(amount) as 'income' FROM invoices WHERE (country='$country' AND type='income'); SELECT SUM(amount) as 'outcome' FROM invoices WHERE (country='$country' AND type='outcome')") ->groupBy('name') ->get(); return view('accounting.accounts') ->with('accounts',$data);
I expect to use it in my view as follows:
@foreach($accounts as $account) <tr> <th>{{$account->name}}</th> <td>{{$account->income}}</td> <td>{{$account->outcome}}</td> </tr> @endforeach
I’m new to Laravel, I would appreciate your help. Thank you in advance.
Advertisement
Answer
I believe you want an agrregated query with conditional sum of amount for each user within a specific country.
In pure SQL it can done using CASE
statements as
select name, sum(case when type='income' then amount else 0 end) as income, sum(case when type='outcome' then amount else 0 end) as outcome from invoices where country = :country group by name order by name
In query builder it can be tranformed as
$accounts= DB::table("invoices") ->where("country", $country) ->select([ "name", DB::raw("sum(case when type='income' then amount else 0 end) as income"), DB::raw("sum(case when type='outcome' then amount else 0 end) as outcome") ]) ->groupBy("name") ->orderBy("name") ->get();