Question 17388 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel Select Fields from Two different Table as per Condition

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_members3.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?

Tags:

2

Answers


  1. 0

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

    And you need to use inner join to get data from both tables tbl_members and tbl_members3

      $payout=Payout::Select(DB::Raw('tbl_payout.mem_id, 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'))
    ->join('tbl_members', function($join){
    $join->on('tbl_payout.mem_id', '=', 'tbl_members.id')
    ->where('tbl_payout.plan_id', '=', '1');
    })       
    ->join('tbl_members3', function($join){
    $join->on('tbl_payout.mem_id', '=', 'tbl_members3.id')
    ->where('tbl_payout.plan_id', '=', '3');
    })       
->get();
    Login or Signup to reply.
  2. 0

    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;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search