Using multi where queries Laravel relationships

I have two tables (Table1, Table2). I want to print the sum of the records whose properties match Table1 in Table2 while listing the Table1 table.

My two tables contain very large records, performance is important to me.

// Model -> relationships
  public function cars()
  {
    return $this->hasMany('AppModelsTable2', 'list_id', 'list_id');
  }

// Controller 
$table1  = Table1::with('cars' => function($query){
        $query->where('table2.color','=', 'table1.color')
        $query->where('table2.year','=', 'table1.year')
    }])
    ->get();

I’m adding the sample database pictures:

Thank you.

Answer

First, add below use statement to the top of your controller:

use DB;

Now, If you only want to get the records amount, you can do this:

$amount = DB::table('table1')
->join('table2', function($join){
    $join->on('table1.color', '=', 'table2.color');
    $join->on('table1.year', '=', 'table2.year');
})->count();

But if you want to get the list of the table1 records, you can change the query a bit like below:

$records = DB::table('table1')
->select('table1.*')
->join('table2', function($join){
    $join->on('table1.color', '=', 'table2.color');
    $join->on('table1.year', '=', 'table2.year');
})->count();

Please follow Laravel’s official guide on this topic: https://laravel.com/docs/8.x/queries#advanced-join-clauses