Laravel Join without repeating one table but get all data in single row

Question

I have 4 MySQL tables, using PHP and Laravel 7

1. members
2. deductions
3. payments
4. payment_deductions

Now I want to display every single member in a row with his/her single payment and all other deductions. (Assuming One person has only one payment)

The DB schema is as follows

and here is the HTML table I want to display

Here is the query I'm using but it duplicates the data.

        $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();

The resulting array repeats every member as per their deductions.

Is there any way to get this data better? Something like a nested array of deductions with each member?

Here are the models

Member

    namespace App;

    use Illuminate\Database\Eloquent\Model;
    use Carbon\Carbon;

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

Payment

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

Deduction

    namespace App;

    use Illuminate\Database\Eloquent\Model;

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

Answer 1

You were very close and on the right track when structuring models, what you were missing is how to load the relationships without creating another query, if you take a look at the controller you will see the standard way to load inner relationships. Hopefully this serves as a better concept to tackle your concerns.

For reference: https://laravel.com/docs/9.x/eloquent-relationships#lazy-eager-loading

Also doing it this way will avoid future N+1 issues see What is the "N+1 selects problem" in ORM (Object-Relational Mapping)? for details on N+1

Member Model

public class Member extends Model
{
    protected $fillable = [
       'full_name',
       'email',
       'created_by',
    ];
        
    public function payments(){
        return $this->hasMany(Payment::class);
    }
}

Payment Model

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

Deduction Model

public class Deduction extends Model
{
    protected $fillable = [
       'title',
       'priority',
       'created_by',
    ];
        
    public function payments() {
        return $this->belongsToMany(Payment::class,'payment_deductions')->withTimestamps();
    }
}

MemberController:

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