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',
        ];
    }

CodePudding user response：

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