How to calculate and append a variable to a model in laravel

Considering I have 3 models with relation Hotel Room and Pricing in my app, I need to show the cheapest room for each hotel.

I want to calculate the pricing and get the lowest price for a certain date which is today (for first step it would be the user chosen day later on) or any date for now ,and get the lowest price room among all and append that to the hotel model so later on I can use it in my spatie query builder package like below to be more clear of what i want to do i paste my spatie query builder below :

 $data = QueryBuilder::for(Accommodation::class)
            ->allowedIncludes(['gallery','city','hotelRooms','hotelRooms.roomPricingHistorySearch'])
            ->allowedFilters([
            AllowedFilter::scope('bed_count'),
            AllowedFilter::scope('filter_price'),
            AllowedFilter::exact('city_id'),
            AllowedFilter::exact('is_recommended'),
            AllowedFilter::exact('accommodation_type_id'),
            'name',
            ])
            ->paginate(10);
        return $data;

Answer

Assuming you have a relationship set up to get hotel rooms (room belongs to hotel, hotel has many rooms), and a room has many pricings (depending on date):

$hotels = Hotel::all();

$cheapestRooms = [];

foreach ($hotels as $hotel) {
    $roomIds = $hotel->rooms()->pluck('id')->toArray();

    $cheapestRoom[$hotel->name] = Room::whereIn('id', $roomIds)
        ->join('pricings', function ($join) {
            $join->on('room.id', '=', 'pricings.room_id')
                ->where('pricings.date', Carbon::now())
         })
        ->orderBy('room.price', 'asc')
        ->first();
}

This creates an associative array where the key is the hotel name and the value is the cheapest room.

To append that to your Hotel model, in Hotel.php I would do:

public function cheapestRoom($date) {
    $roomIds = $this->rooms()->pluck('id')->toArray();

    return = Room::whereIn('id', $roomIds)
        ->join('pricings', function ($join) {
            $join->on('room.id', '=', 'pricings.room_id')
                ->where('pricings.date', $date)
         })
        ->orderBy('room.price', 'asc')
        ->first();
}

And then you can use this like: $hotel->cheapestRoom(Carbon::now);

When you want a date range, you can modify the function to take 2 dates and use whereBetween.