在Laravel中写一个查询join sum groupby [英] Write a query join sum groupby in laravel

问题描述

请帮我写查询.
我有2个表格:项目"和债务"
债务"表有

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
...

我需要写查询以从项目中获取1000行，并对所有总计"(按"currency_list"分组)进行求和

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

非常感谢:)

推荐答案

嘿，我为您尝试了希望它起作用的方法:) 首先，你应该有两个表的模型 在您的Project模型调用中

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

并在您的控制器中调用

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

检查您的 dd($ projects)为数组

在您的控制器功能中使用

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

然后在您的视图中使用

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

这篇关于在Laravel中写一个查询join sum groupby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！