Laravel Select Fields from Two different Table as per Condition-CodePudding

I have 3 tables (members, members3, payout)

Payout Table having records with the relationship of both other tables (members, members3)

I want to list the records with both table relationships.

For example:

Members Table (Plan 1)
id, name, mobile
1, Karthik, 9237493

Members3 Table (plan 3)
id, name, mobile
1, George, 923143422

Payout Table
id, mem_id, plan_id, Amount
1, 1, 1, 500
2, 1, 3, 1500

I want to fetch the records as below:
ID, Member, Amount
1, Karthik, 500
2, George, 1500

I have something already done it my Laravel Code. But Can't fetch the select fields as per relationship modal.

$payout=Payout::leftJoin('tbl_members', function($join){
        $join->on('tbl_payout.mem_id', '=', 'tbl_members.id')
        ->where('tbl_payout.plan_id', '=', '1');
        })       
        ->leftJoin('tbl_members3', function($join){
        $join->on('tbl_payout.mem_id', '=', 'tbl_members.id')
        ->where('tbl_payout.plan_id', '=', '3');
        })       
->Select(DB::Raw('tbl_payout.mem_id, tbl_members.username, tbl_members.name, tbl_payout.paid, tbl_members.city, tbl_members.mobile, tbl_members.bank, tbl_members.bank_number, tbl_members.bank_branch, tbl_members.bank_ifsc'))
->get();

In this Query, Eloquent fetches the tbl_members records only not tbl_members3 records. How could I achieve this?

CodePudding user response：

You have mistake in your join query at tbl_members.id you need to use tbl_members3 instead of tbl_members

        ->leftJoin('tbl_members3', function($join){
    $join->on('tbl_payout.mem_id', '=', 'tbl_members3.id')
    ->where('tbl_payout.plan_id', '=', '3');
    })

CodePudding user response：

in your model

 class Payout extends Model
 {
     public function member()
     {
         if ($this->plan_id == 1) {
             return $this->belongsTo(Members::class, 'mem_id');
         } else if ($this->plan_id == 3) {
             return $this->belongsTo(Members3::class, 'mem_id');
         }
     }
 }

fetch data:

$payouts = Payout::with('member')->get();
foreach($payouts as $payout) {
    $id = $payout->id;
    $name = $payout->member->name;
    $amount = $payout->amount;
}