I’m working on apartments rental project. As a part of the applications abilities there is a functionality for an apartments owner to select days that the apartment is available for rental by guests. I’m writing a search query to get apartments where it checks amongst other things for days available for rental. I have following tables: rooms, bookings, days, day_room (pivot table). Since I’m new here please let me know if I need to post anything else for you to get a better understanding of my problem.

In this query when I would write static date like for example ‘2024-06-27’ in the last Where method it would work just fine and produce desired results but when using $date->format(‘Y-m-d’) it doesn’t find anything. However in produced SQL query I don’t see that there would be any difference, i.e. in dates format.

namespace AppHttpControllers;

use AppModelsRoom;
use AppModelsBooking;
use CarbonCarbonPeriod;
use CarbonCarbon;
use IlluminateHttpRequest;
use IlluminateSupportFacadesDB;

class SearchController extends Controller
{
    public function index()
    {
        // Validate data later

        $city = request('city');
        $guests_number = request('guests_number');
        $object_type = request('object_type');
        $checkIn = request('checkIn');
        $checkOut = request('checkOut');

        $interval = CarbonPeriod::create($checkIn, $checkOut);

        $rooms = Room::query()
            ->with(['photos', 'bookings', 'days'])
            ->where('rooms.city_id', '=', $city)
            ->where('rooms.beds_num', '>=', $guests_number)
            ->where('rooms.object_type_id', '=', $object_type)
            ->join('bookings', 'bookings.room_id', '=', 'rooms.id')
            ->where(function ($query) use ($checkIn, $checkOut) {
                $query->where('checkIn', '>=', $checkOut)
                    ->orWhere('checkOut', '<=', $checkIn);
            })
            ->whereExists(function ($query) use ($interval) {
                $query
                    ->from('day_room')
                    ->join('days', 'days.id', '=', 'day_room.day_id')
                    ->whereColumn('day_room.room_id', 'rooms.id')
                    ->where(function ($query) use ($interval) {
                        foreach ($interval as $date) {
                            $query->where('days.day', '=', $date->format('Y-m-d'));
                            // when testing if I write static date like here, then it works.
                            // $query->where('days.day', '=', '2024-06-27');
                        }
                    });
            })
            ->get('rooms.*');

        return view('results', ['rooms' => $rooms]);
    }