php - Laravel 8 Inner Join Two Tables

Question

I'm trying to get different columns from two tables in my Laravel database, I'm new to joins but following the docs I think my syntax is correct, except I get the following error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'created_at' in on clause is ambiguous (SQL: select `data_source_one`.`event_count`, `data_source_two`.`sessions` from `data_source_one` inner join `data_source_two` on `created_at` >= `2021-01-11 10:57:45`)

I have two tables:

• data_source_one
• data_source_two

Each table has the Laravel created_at column, one table has a sessions column, the other has event_count, and I have data for 14 days prior to my query. So not sure why it can't get the data?

My PHP is:

public function getSources(Request $request)
{

    $data = DB::table('data_source_one')
          ->join('data_source_two', 'created_at', '>=', Carbon::now()->subDays(14))
          ->select('data_source_one.event_count', 'data_source_two.sessions')
          ->get();

    return response()->json($data, 200);

}

question from:https://stackoverflow.com/questions/65883461/laravel-8-inner-join-two-tables

Answer 1

As indicated in the error message, the "created_at" column is ambiguous because it's present in both tables. So you have to specify which "created_at" you are referring to.

Maybe like this:

$data = DB::table('data_source_one as DSO')
      ->join('data_source_two as DST', //here, join your two tables by a common column )
      ->select('DSO.event_count', 'DST.sessions')
      ->where('DST.created_at', '>=', Carbon::now()->subDays(14))
      ->get();