Optimize Laravel Join to retrieve all data in a single row without duplicating tables

Question

I have 4 MySQL tables, using PHP and Laravel 7

1. member
2. deduct
3. Payment
4. Payment Deduction

Now I want to display each member's single payment and all other deductions continuously. (Assume one person only has one payment)

The database structure is as follows

This is the HTML table I want to display

This 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 each member based on its derivation.

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

This is the model

member

namespace App;

    use IlluminateDatabaseEloquentModel;
    use CarbonCarbon;

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

Payment

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

deduct

namespace App;

    use IlluminateDatabaseEloquentModel;

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

Answer 1

You are very close and on the right track when building the model, what you are missing is how to load the relationships without creating another query, if you look at the controller you will see the criteria for loading the relationships inside method. Hopefully this is a better concept to address your concerns.

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

Doing this will also avoid future N 1 problems, see What is the "N 1 selection problem" in ORM (Object Relational Mapping)? Details about N 1< /p>

Member Model

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

Payment Mode

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

Member Controller:

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