Laravel雄辩查询从多个表中选择列

Question

我有一个Laravel后端和一个VueJS前端。

我试图在一个具有hasOne关系的模型上生成一个查询，但只从关系中选择特定的列，做一个sum和一个group By。

null

public function contractsByYear() {
   return Contract::where('CONTRACT_TYPE','LIKE','SP-%')->with(['AdditionalInfo' => function($query) {
       $query->selectRaw('MONTH(START_DATE) as Month')->whereYear('START_DATE','2019');
   }])->sum('CONTRACT_PRICE')->groupBy('Month');
}

MONTH | TOTAL
1     | 500
2     | 233
3     | 800
etc

Answer 1

收集方法可能会做你想做的事情

public function contractsByYear()
{
    return Contract::select('id', 'CONTRACT_PRICE') /* Only really need the id (or whatever else is the pk) and the price */
    ->where('CONTRACT_TYPE','LIKE','SP-%')
    ->with(['AdditionalInfo' => function($query) {
        $query->select('id', 'contract_id')         /* Only really need the id (or whatever else is the pk), and the Contract fk*/
        ->selectRaw('MONTH(START_DATE) as Month')   /* Aggregate the month */
        ->whereYear('START_DATE','2019');
    }])
    ->get()
    // From this point on, it's a Collection
    ->groupBy('AdditionalInfo.*.Month')             /* It's either this or 'AdditionalInfo.Month' */
    ->map(function($item, $key) {
        return [
            'month' => $key,
            'total' => $item->sum('CONTRACT_PRICE')
        ];
    });
}