问题:

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

回答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}}
}

回答2:

You can try this

App/Project

public function getDebts()
{
    return $this->hasMany('App\Debt');
}

Controller

use App\Project;

public function getProjects(Project $project ) 
{        
    $all =  $project->with(['getDebts'=>function($query){
                $query->selectRaw('*,sum(total) as sum')
                      ->groupBy('currency_list');
            }])->take(1000);
}