How to make a selection from a database with relationships and apply filters?

Question

MySQL

There are 2 tables in the database:
1) cars - stores car records:
id | manufacturer | model | description ...

2) cars_prices_history - stores the history of car prices (the price may be promotional, or it may not be promotional)

id  |  car_id  |  price    |  is_sale  |  sale_expires  |  date         |
-------------------------------------------------------------------------
1   |       1  |    23000  |        1  |    27-08-2021  |   27-04-2020  |
-------------------------------------------------------------------------
2   |       1  |    34000  |        0  |          NULL  |   27-04-2020  |
-------------------------------------------------------------------------
3   |       2  |    27500  |        0  |          NULL  |   28-04-2020  |

There are 2 models with relationships (Laravel Relationships):
Car:

class Car extends Model
{
    protected $table = 'cars';

    public function priceHistory() {
        return $this->hasMany(Car_price_history::class);
    }
}

Car_price_history:

class Car_price_history extends Model
{
    protected $table = 'cars_prices_history';

    public function car() {
        return $this->belongsTo(Car::class);
    }
}

The controller that handles the `api/v1/cars` route request:

public function index(Request $request, Car $cars)
{
    $cars = Car::with('priceHistory');  // получаю билдер

    if ($request->has('minPrice') && !empty($request->minPrice))
    {
     // здесь должен быть фильтр, который должен через модель Car_price_history
     // сходить к БД, найти все цены, у которых car_id будет равно id-шнику авто (непонятно, откуда
     // брать id авто, если в $cars сидит билдер, а коллекцию я получить не могу до запроса,
     // а даже если получу, то мне придётся запускать foreach для каждого авто и перелопачивать всю коллекцию??
     // или делать это при помощи методов коллекций, но всё равно выглядит странно), взять последнюю 
     // акционную цену, а если акционной нет, то взять последнюю не акционную. И только потом, получив цену, 
     // применить фильтр, который отсеит авто. И такая логика для каждого фильтра.

    $cars->where('price', '>=', $request->minPrice);  // не работающий фильтр
    }

    $cars = $cars->get();  // получаю коллекцию
    return new CarCollection($cars);
}

I need to add filters to filter all cars by several filters:
1) minimum price - minPrice
2) maximum price - maxPrice
3) discount - sale
and others. For the controller to process a route like `api/v1/cars?minPrice=1000&maxPrice=21000&sale=1` and apply the appropriate filters, and then return to the CarCollection (Eloquent API Resourse) only those results that have passed through the filters.


In general, it is not at all clear how you can do this kind of filtering? How can I get the latest price and make a filter for it? Or maybe it's easier to add auto columns to the migration priceandsale_price? But in addition to cars_prices_history, I also have tables that are related to the Car model, and adding columns, firstly, will cause data to be duplicated, and secondly, it violates the table normalization rules, that is, the idea is strange too.

How to make filters? Maybe there is a simpler option to implement a filter by price? Recommend articles, videos on this topic

Answer 1

instead of this
$cars->where('price', '>=', $request->minPrice);

$cars->whereHas('priceHistory', function ($q) {
            $q->where('price', '>=', $request->minPrice);
        });