Laravel Query Builder Foreach Loop how do I reference a field that exists in both join tables-CodePudding

The id field in the foreach loop exists in both of the tables that I am joining, I need to reference it to one table only (ie. program_types.id) in the foreach loop:

$program_details = DB::table('program_allocation')
                ->where('twilio_wa_from_no', '=', $tophonenumber)
                ->where('twilio_sid' , $account_sid)
                ->join('program_types', 'program_allocation.program_type', '=', 'program_types.id')
                ->get();

foreach ($program_details as $program) {  
        $program_allocation_id = $program->id;
}

CodePudding user response：

You can use select('program_allocation.*', 'program_types.id AS program_type_id') to specify which columns of which tables included in query you want to use.

$program_details = DB::table('program_allocation')
    ->where('twilio_wa_from_no', '=', $tophonenumber)
    ->where('twilio_sid', $account_sid)
    ->join('program_types', 'program_allocation.program_type', '=', 'program_types.id')
    ->select('program_allocation.*', 'program_types.id AS program_type_id')
    ->get();

You can check it on official Laravel documentation: https://laravel.com/docs/9.x/queries#inner-join-clause

CodePudding user response：

Select field and give one of the join table id an alias

$program_details = DB::table('program_allocation')
                ->join('program_types', 'program_allocation.program_type', '=', 'program_types.id')
                ->select('program_types.*', 'program_allocation.id as allocationID', )
                ->where('twilio_wa_from_no', '=', $tophonenumber)
                ->where('twilio_sid' , $account_sid)
                ->get();