Laravel Left Join overrides the id column

user_9982090 0
Viewed 914

I'm using Laravel 8.x and am trying to get some data from my database. I'm then joining another table, which is fine, except my ID column of my reports table is being overridden by my report_data column, the ID column there.

I've tried to use the ->select('report.*'), which seems to preserve my ID column, but then I don't have my columns from my report_data table included in my left join?

What am I doing wrong?

/**
 * View a report
 *
 * @return Response
 */
public function view($id)
{

    $report = DB::table('reports')
                ->where('id', $id)
                ->where('user_id', Auth::id())
                ->first();

    if (!empty($report->report_data_id)) {
      $report = DB::table('reports')
                  ->where('reports.id', $id)
                  ->where('reports.user_id', Auth::id())
                  ->leftJoin('report_data', 'report_data.id', '=', 'reports.report_data_id')
                  ->first();

      $report->discovery_filters = json_decode($report->discovery_filters);
      $report->report_data = json_decode($report->report_data);
    }

    return response()->json([
      'success' => true,
      'message' => 'Your report',
      'report' => $report
    ], 200);

}

I need to preserve the id column from my reports table.

laravel php
2 Answers

The problem is you're selecting both the id columns, which is overriding the one of the id. You can specify Eloquent to select the "id" column from "report_data" table AS "rd_id" and make joins on that:

$report = DB::table('reports')
    ->select('reports.*', 'report_data.id AS rd_id')
    ->where('reports.id', $id)
    ->where('reports.user_id', Auth::id())
    ->leftJoin('report_data', 'report_data.id', '=','reports.report_data_id')
    ->first();

Then you can use the report_data id as:

$report->rd_id;

And the report id as:

$report->id;
user_137435990 
$report = Report::find($id); //I guess that table have uniq value for IDs as default so you don't need 'where' checks.

if($report->report_data_id){

//Depend what columns you need, if you want all 'reports_data' columns then just add '.*' and replace id for it as bellow

  $report = Report::select('reports.*', 'reports_data.*', 'reports_data.id as rd_id')
                    ->join('reports_data', 'reports.report_data_id', 'reprots_data.id')
                    ->where('reports.id', $id)
                    ->where('reports.user_id', Auth::id())
                    ->first();
                    
  $report->discovery_filters = json_decode($report->discovery_filters);
  $report->report_data = json_decode($report->report_data);
  
}

// TRY dd($report) to see what is inside
// Your key from reports_data should be now 'rd_id'

return response()->json([
  'success' => true,
  'message' => 'Your report',
  'report' => $report
], 200);
user_20448820
Related