Write a query join sum groupby in laravel

Question

Please help me write query.
I have 2 table: "projects" and "debts"
"debts" table have

id | project_id | currency_list | total
1  | 1          | 1             | 1000
2  | 1          | 2             | 500
3  | 2          | 1             | 1000
4  | 2          | 2             | 500
...

I need write query to take 1000 rows from projects and SUM all "total" with group by "currency_list"

Thanks a lot :)

Answer 1

Hey i tried for you its hope working :) First you should have tow models for tables In your Project model call like this

    public function debts(){
      return $this->hasMany('App\Debt','project_id')->selectRaw('debts.*,sum(total) as sum')->groupBy('currency_list');
   }

and call this in your controller

$projects = Project::with('debts')->get()->toArray();

check your dd($projects) as array

EDIT : Use this in your controller function

$projects = DB::table('projects')
            ->join('debts', 'projects.id', '=', 'debts.projects_id')
            ->select('projects.*','debts.*', DB::raw('sum(total) as sum'))
            ->groupBy('currency_list')
            ->get();

then in your view use

@foreach($projects as $project)
{
{{$project->sum}}
}