Laravel 5.6 querying related tables to filter results based on multiple columns from both the tables

Hello guys I am working on a project build using laravel 5.6, I want to filter results in search, based on conditions that applies to different columns from both the tables.

I have two tables one is Adverts and other is Vehicle_Specifications

Adverts has columns:

[id, user_id, category_id, status_id, promotion_id,title, description, state, city, cost, slug]

Adverts table is also related to Ad_Images table which holds all the images of the adverts according to their ids.

Vehicle_Specifications has columns:

[id, advert_id, brand, model, year, body_type, doors, color, milage, fuel_type]

Scenario: Now I want to filter data from adverts table based on status_id, state, city then get those adverts which has filters matching in vehicle_specifications table like brand = honda, model = civic etc. and also paginate the result.

Query that I have tried:

$filtered_vehicles = Ad::with('vehiclespecification')->where([['state', $state]])->whereHas('vehiclespecification', function ($query) {
                $query->where('brand', 'like',$_GET['make'].'%');
            })->paginate(50);

the above query does give me some results but it is not perfect.

my query log:

^ array:3 [▼
  0 => array:3 [▼
    "query" => "select count(*) as aggregate from `ads` where (`state` = ?) and exists (select * from `vehiclespecifications` where `ads`.`id` = `vehiclespecifications`.`ad_id` ▶"
    "bindings" => array:2 [▼
      0 => "Punjab"
      1 => "Honda%"
    ]
    "time" => 95.87
  ]
  1 => array:3 [▼
    "query" => "select * from `ads` where (`state` = ?) and exists (select * from `vehiclespecifications` where `ads`.`id` = `vehiclespecifications`.`ad_id` and `brand` like ?) ▶"
    "bindings" => array:2 [▼
      0 => "Punjab"
      1 => "Honda%"
    ]
    "time" => 79.32
  ]
  2 => array:3 [▼
    "query" => "select * from `vehiclespecifications` where `vehiclespecifications`.`ad_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ▶"
    "bindings" => array:28 [▼
      0 => 369
      1 => 384
      2 => 391
      3 => 396
      4 => 517
      5 => 755
      6 => 964
      7 => 997
      8 => 998
      9 => 1029
      10 => 1050
      11 => 1054
      12 => 1060
      13 => 1078
      14 => 1127
      15 => 1148
      16 => 1155
      17 => 1279
      18 => 1322
      19 => 1387
      20 => 1465
      21 => 1467
      22 => 1470
      23 => 1473
      24 => 1474
      25 => 1475
      26 => 1476
      27 => 1478
    ]
    "time" => 0.87
  ]
]

Any help would be much appreciated.

Thank you

via Chebli Mohamed