Using multi where queries Laravel relationships

Question

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('App\Models\Table2', '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.

Ahmad Karimi · Accepted Answer · 2021-01-07 08:21:42Z

2

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

answered Jan 7, 2021 at 8:21

Ahmad Karimi

1,3913 gold badges15 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yunus Kocabay Over a year ago

I need to use with in this query and return data from another table. eg: with('test,'test2') Thank you for your answer

Ezekiel Arin · Accepted Answer · 2021-01-07 07:20:05Z

0

Use join to merge the two tables read more about join here https://www.w3schools.com/sql/sql_join.asp

answered Jan 7, 2021 at 7:20

Ezekiel Arin

1456 bronze badges

Collectives™ on Stack Overflow

Using multi where queries Laravel relationships

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related