热切加载参数Laravel

Question

我想做的是从参数为$date的分类账表中获取所有具有额外值的科目表，即余额。有没有合适的方法可以做到这一点，因为我在这里面临N+1查询问题。

控制器

public function get(Request $request){
$date = $request->date;
$coas = COA::where('parent_id', null)->get();
//return $coas;
$coas = $coas->map(function ($coa) use ($date) {
$coa['balance'] = $coa->balance($date);
return $coa;
});
return view('website.accounts.Trial.show', compact('coas', 'date'));
}

型号

public function balance($date){
$date = new Carbon($date);
$date= $date->addHours(23)->addMinutes(59)->addSeconds(59);
$balance = Ledger::where('c_o_a_id', $this->id)
->where('created_at' ,'<=', $date)
->orderBy('created_at', 'desc')
->orderBy('id', 'desc')
->pluck('balance')
->first();
if($balance){
return $balance;
}
return 0;
}

Answer 1

1。在COA和Ledger之间建立关系

COA型

public function ledgers()
{
return $this->hasMany(Ledger::class, 'c_o_a_id');
}

Ledger型

public function coa()
{
return $this->belongsTo(COA::class, 'c_o_a_id');
}

2.让balance()函数使用该关系以避免查询N+1次

COA Model

public function balance($date){
$date = new Carbon($date);
$date = $date->addHours(23)->addMinutes(59)->addSeconds(59);
if (!$this->relationLoaded('ledgers') $this->load('ledgers');
$balance = $this->ledgers->where('created_at' ,'<=', $date)
->sort(function ($a, $b) {
return [$b->created_at, $b->id] <=> [$a->created_at, $a->id];
})
->first()
->balance;

return $balance ?: 0;
}

控制器

$coas = COA::with('ledgers')->where('parent_id', null)->get();