我有 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',
];
}
在构建模型时，您非常接近并且处于正确的轨道上，您缺少的是如何在不创建另一个查询的情况下加载关系，如果您查看控制器，您将看到加载内部关系的标准方法。希望这是一个更好的概念来解决您的担忧。
供参考：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')); }