Laravel连接不重复一个表，但在单行中获取所有数据

ac1kyiln 于 2022-12-27 发布在其他

关注(0)|答案(1)|浏览(109)

我有4个MySQL表，使用PHP和Laravel 7
1.成员
1.扣除额
1.付款
1.付款_扣款
现在，我想在一行中显示每个成员及其单次付款和所有其他扣减。（假设一个人只有一次付款）
数据库模式如下所示

这是我想显示的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 Illuminate\Database\Eloquent\Model;
    use Carbon\Carbon;

    class Member extends Model
    {
        protected $fillable = [
            'full_name',
            'email',
            'created_by',
        ];
    }

付款

namespace App;

    use Illuminate\Database\Eloquent\Model;

    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 Illuminate\Database\Eloquent\Model;

    class Deduction extends Model
    {
        protected $fillable = [
        'title',
        'priority',
        'created_by',
        ];
    }

laravel

来源：https://stackoverflow.com/questions/74917471/laravel-join-without-repeating-one-table-but-get-all-data-in-single-row

1条答案

按热度按时间

gmxoilav1#

当构建模型时，你已经非常接近并且走在正确的轨道上了，你缺少的是如何在不创建另一个查询的情况下加载关系，如果你看一下控制器，你会看到加载内部关系的标准方法。希望这是一个更好的概念来解决你的问题。
For reference: https://laravel.com/docs/9.x/eloquent-relationships#lazy-eager-loading
这样做还可以避免将来出现N+1问题，有关N+1的详细信息，请参见What is the "N+1 selects problem" in ORM (Object-Relational Mapping)?

- 成员模型**

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

赞(0）回复(0）举报 2022-12-27

我来回答

Laravel连接不重复一个表，但在单行中获取所有数据

1条答案

相关问题

热门标签

最新问答