how to retrieve multiple table data with multiple criteria in laravel eloquent?

Question

I want to retrieve data from 4 different table using laravel eloquent with multiple criteria.

an Overview of my table;

table 1

• id
• name

---

table 2

• id
• name
• year
• table1_id

---

table 3

• id
• name
• description
• table2_id

---

table 4

• id
• name
• quarter
• table3_id

below are their relations

---

table 1

hasMany -> table 2

table 2

 belongsTo ->table1
 HasMany->table2

table 3

 belongsTo ->table2
 HasMany->table3

table 4

belongsTo ->table3

I'd like to fetch the data by resource show with two parameters and i tried this

   $Report = Table1::whereHas('tabke1', function($query){
        $query->where('year', 'like','%'.$year.'%');
      })
      ->with('table2')->whereHas('table3', function($query){
        $query->where('quarter', 'like', '%'.$quarters.'%');
      })
      ->get();

but im receiving syntax error.

How can I retrieve the data from multiple table with multiple filter? i tried this table query to understand more what i expect

SELECT `table1`.*, `table2`.*, `table3`.*, `table4`.*
FROM `table1`, `table2`, `table3`, `table4`
WHERE ((`table2`.* year = 2019) AND (`table4`.* quarter = 1))

Answer 1

I reckon there are two queries to achieve the results.

The first query is something like:

Table1::whereHas('table2', function ($query) {
    $query->where('year', 2019);
})
->whereHas('table2.table3.table4', function ($query) {
    $query->where('quarter', 1);
})
->get();

The second query is something like:

Table1::select('table1.*')
->join('table2', 'table1.id', '=', 'table2.table1_id')
->join('table3', 'table2.id', '=', 'table3.table2_id')
->join('table4', 'table3.id', '=', 'table4.table3_id')
->where('table2.year', 2019)
->where('table4.quarter', 1)
->distinct()
->get();

Regarding performance, I prefer the second query.