優化 Laravel Join 以檢索單行中的所有資料而不重複表

Question

我有 4 個 MySQL 表，使用 PHP 和 Laravel 7

1. 成員
2. 扣除
3. 付款
4. 付款扣除額

現在我想連續顯示每個會員的單筆付款和所有其他扣除額。 （假設一個人只有一筆付款）

資料庫架構如下

這是我要顯示的 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',
        ];
    }

#

Answer 1

在建立模型時，您非常接近並且處於正確的軌道上，您缺少的是如何在不創建另一個查詢的情況下加載關係，如果您查看控制器，您將看到加載內部關係的標準方法。希望這是一個更好的概念來解決您的擔憂。

供參考：https://laravel.com/ docs/9.x/eloquent-relationships#lazy-eager-loading

這樣做也可以避免未來的 N 1 問題，請參閱什麼是 ORM（物件關係映射）中的「N 1 選擇問題」？ 有關 N 1 的詳細資訊

會員模型

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