Problem with a query when comparing dates in Laravel based project

Question

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 App\Http\Controllers;

    use App\Models\Room;
    use App\Models\Booking;
    use Carbon\CarbonPeriod;
    use Carbon\Carbon;
    use Illuminate\Http\Request;
    use Illuminate\Support\Facades\DB;

    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]);
    }

Answer 1

Thank you all for your feedback in the comments section. @Rene and @Tupkap got me thinking in a right direction - I was checking for a multiple days.day against one cell. I fixed that by moving the foreach loop. Below is the edited version of the code that's working for me. I guess it's not the most elegant way to solve the problem but since apartments aren't ment to be booked for long periods (usually for a couple of days and no longer than 1-2 weeks).

    namespace App\Http\Controllers;

use App\Models\Room;
use App\Models\Booking;
use Carbon\CarbonPeriod;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

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);
            })
            ->where(function ($query) use ($interval) {
                foreach ($interval as $date) {
                    $query->whereExists(function ($query) use ($date) {
                        $query
                            ->from('day_room')
                            ->join('days', 'days.id', '=', 'day_room.day_id')
                            ->whereColumn('day_room.room_id', 'rooms.id')
                            ->where('days.day', '=', $date->format('Y-m-d'));
                    });
                }
            })
            ->get('rooms.*');

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