我有 4 個 MySQL 表,使用 PHP 和 Laravel 7
現在我想連續顯示每個會員的單筆付款和所有其他扣除額。 (假設一個人只有一筆付款)
資料庫架構如下
這是我要顯示的 HTML 表格
這是我正在使用的查詢,但它重複了資料。
$payments = Payment::leftJoin('members', 'payments.member_id', '=', 'members.id') ->leftJoin('payment_deductions', 'payments.id', '=', 'payment_deductions.payment_id') ->leftJoin('deductions', 'payment_deductions.deduction_id', '=', 'deductions.id') ->select( 'members.*', 'payment_deductions.*', ) ->orderBy("member_id", "ASC") ->get()->toArray();
結果陣列根據每個成員的推導重複每個成員。
有什麼辦法可以更好地取得這些數據嗎?類似於每個成員的嵌套扣除數組?
這是模型
會員
namespace App; use IlluminateDatabaseEloquentModel; use CarbonCarbon; class Member extends Model { protected $fillable = [ 'full_name', 'email', 'created_by', ]; }
付款
namespace App; use IlluminateDatabaseEloquentModel; class Payment extends Model { protected $fillable = [ 'member_id', 'total_amount', 'payable_amount', 'created_by', ]; public function deductions() { return $this->belongsToMany(Deduction::class,'payment_deductions')->withTimestamps(); } }
扣除
namespace App; use IlluminateDatabaseEloquentModel; class Deduction extends Model { protected $fillable = [ 'title', 'priority', 'created_by', ]; }
P粉2390894432024-01-17 09:20:05
在建立模型時,您非常接近並且處於正確的軌道上,您缺少的是如何在不創建另一個查詢的情況下加載關係,如果您查看控制器,您將看到加載內部關係的標準方法。希望這是一個更好的概念來解決您的擔憂。
供參考:https://laravel.com/ docs/9.x/eloquent-relationships#lazy-eager-loading
這樣做也可以避免未來的 N 1
問題,請參閱什麼是 ORM(物件關係映射)中的「N 1 選擇問題」? 有關 N 1
的詳細資訊< /p>
會員模型
public class Member extends Model { protected $fillable = [ 'full_name', 'email', 'created_by', ]; public function payments(){ return $this->hasMany(Payment::class); } }
支付模式
public class Payment extends Model { protected $fillable = [ 'member_id', 'total_amount', 'payable_amount', 'created_by', ]; public function member(){ return $this->belongsTo(Member::class); } public function deductions() { return $this->belongsToMany(Deduction::class,'payment_deductions')->withTimestamps(); } }
推演模型
#public class Deduction extends Model { protected $fillable = [ 'title', 'priority', 'created_by', ]; public function payments() { return $this->belongsToMany(Payment::class,'payment_deductions')->withTimestamps(); } }
成員控制器:
#/** * Show the specified model. * * @param \Illuminate\Http\Request $request * @param Member $member * @return \Illuminate\Http\Response */ public function show(Request $request, Member $member){ // This will load all of the inner relationships in a single query. $member->load('payments.deductions'); //Assign the loaded payments to be used $payments = $member->payments; /* You can acess the payments -> deductions in a foreach loop, in php or blade foreach($payments->deductions as $deduction){ //$deduction->id } */ return view('sampleView', compact('member', 'payments')); }