Select joined table with same column name but different value using eloquent-CodePudding

I'm trying to call 2 columns from 2 different tables. applicants.ic_no and agents.ic_no. It have different values.

Using the following codes only displayed only ic_no from agents.ic_no

$claimLists = ClaimDetail::join('applicants', 'applicants.ic_no', '=', 'claim_details.ic_no')
        ->join('agents', 'agents.id', '=', 'claim_details.agent_id')
        ->where('claim_date', $cutt_off)
        ->groupBy('agents.id', 'claim_details.id', 'applicants.id')
        ->orderBy('agents.id')
        ->orderby('amount', 'desc')
        ->get();

How do i get both columns to display?

CodePudding user response：

This is because, you have ic_no in both tables. By default MYSQL picks one of it to display, it is the same as having id in both tables and from your results, how would you know which table's ic_no you are accessing while they have the same name?

Alternatively you can use select and DB::raw to change the name of one of the ic_no fields, and similiarly for any other similiar fields. For example;

$claimLists = ClaimDetail::join('applicants', 'applicants.ic_no', '=', 'claim_details.ic_no')
        ->join('agents', 'agents.id', '=', 'claim_details.agent_id')
        ->select('claim_details.*', DB::raw('agents.ic_no as agents_ic_no'), 'agents.XXX', 'agents.YYYY', 'applicants.XXX')
        ->where('claim_date', $cutt_off)
        ->groupBy('agents.id', 'claim_details.id', 'applicants.id')
        ->orderBy('agents.id')
        ->orderby('amount', 'desc')
        ->get();

instead of XXX and YYY, you can put the fields that you would like to get and you can get as many as you want or remove them, if you don't want to get any field from the second table, but the main thing here is you are access the agents.ic_no as agents_ic_no or whatever name you would like to give it.

CodePudding user response：

I solve this issue by adding select to rename the conflict column name. So this is the code:

        $processed = ClaimDetail::join('applicants', 'applicants.ic_no', '=', 'claim_details.ic_no')
                        ->join('agents', 'agents.id', '=', 'claim_details.agent_id')
                        ->select('*')
                        ->selectRaw('applicants.ic_no as nokp, agents.ic_no as agic')
                        ->where('claim_details.agent_id',$agent->id)
                        ->orderBy('claim_details.claimable', 'desc')
                        ->orderby('claim_details.amount', 'desc')
                        ->get();

Thanks guys for your time.