how to query the right way.

So, each reservation has 1 database entry for a date range? If so, querying for a date range gets a bit tricky with this set up. Without getting complicated, simple query scopes could easily find themselves outside of the date range. For example, if you made a query for all reservations between Dec 15-20, a reservation with a Start Date of Dec 14 and an End Date of Dec 20 would not be included.

Someone else might have a better idea, but what I would consider is changing your Reservation model to be based upon a single day, and scrap the Start/End fields. Have your application logic handle creating multiple reservation entries for each day within the reservation range.

This way, you could get a collection of all days by doing something like this:

Room::where('id', $id)->with(['reservations' => function($query) {
    
    $query->whereBetween('reservation_date', [$startDate, $endDate]);

}])->get();

Or...

Reservations::where('room_id', $id)
        ->whereBetween('reservation_date', [$startDate, $endDate])
        ->get();

Then within your blade, you could just iterate over all the reserved days within the date-range filtered collection.

HI thx for your reply. Yes you are right each room has many reservations and a reservation has an start and an end date in the database. On my room model I have a scopeGetReservations function which looks like

$reservations =  $this->hasMany(Reservation::class, 'room_id', 'id')->whereRaw('? <= anreise', array($anreise))
                            ->whereRaw('? >= abreise', array($abreise))
                            ->orWhere(function($query) use ($anreise, $abreise){
                              $query->whereRaw('? >= anreise', array($anreise))
                                    ->whereRaw('? <= abreise', array($abreise))
                                    ->orWhere(function($query) use ($anreise, $abreise){
                                      $query->whereRaw('? >= anreise', array($anreise))
                                            ->whereRaw('? >= abreise', array($abreise))
                                            ->whereRaw('? < abreise', array($anreise))
                                            ->whereRaw('? < anreise', array($abreise))
                                            ->orWhere(function($query) use ($anreise, $abreise){
                                              $query->whereRaw('? > anreise', array($abreise))
                                                    ->whereRaw('? < abreise', array($abreise))
                                                    ->orWhere(function($query) use ($anreise, $abreise){
                                                      $query->whereRaw('? > anreise', array($anreise))
                                                            ->whereRaw('? < abreise', array($anreise))
                                                            ->whereRaw('? != anreise', array($anreise))
                                                            ->whereRaw('? != abreise', array($abreise));
                                                    });
                                            });
                                    });
                            })->get();

everything is working fine. I can not use the whereBetween method because the enddate from one reservation could be the start date from another reservation. The whereBetween method ignores this "cenario".

If i am calling this above method inside a blade file, for some reason...this above method returns all reservations but ignores the room ID. So I cant query foreach room.

The method below i use inside my blade file to check if a room model has reservations on a given daterange. But it ignores the room ID.

@if ($r->getReservations($booking->anreise_datum->toDateString(), $booking->abreise_datum->toDateString())->count() > 0)

Well i figured it out myself.

I had to place the room id in every new orWhere function.

public function GetReservations($anreise, $abreise)
    {
      $anreise = Carbon::parse($anreise);
      $abreise = Carbon::parse($abreise);

      return $this->reservation()
                            ->where('room_id', $this->id)
                            ->whereRaw('? <= anreise', array($anreise))
                            ->whereRaw('? >= abreise', array($abreise))
                            ->orWhere(function($query) use ($anreise, $abreise){
                              $query->where('room_id', $this->id)
                                    ->whereRaw('? >= anreise', array($anreise))
                                    ->whereRaw('? <= abreise', array($abreise))
                                    ->orWhere(function($query) use ($anreise, $abreise){
                                      $query->where('room_id', $this->id)
                                            ->whereRaw('? >= anreise', array($anreise))
                                            ->whereRaw('? >= abreise', array($abreise))
                                            ->whereRaw('? < abreise', array($anreise))
                                            ->whereRaw('? < anreise', array($abreise))
                                            ->orWhere(function($query) use ($anreise, $abreise){
                                              $query->where('room_id', $this->id)
                                                    ->whereRaw('? > anreise', array($abreise))
                                                    ->whereRaw('? < abreise', array($abreise))
                                                    ->orWhere(function($query) use ($anreise, $abreise){
                                                      $query->where('room_id', $this->id)
                                                            ->whereRaw('? > anreise', array($anreise))
                                                            ->whereRaw('? < abreise', array($anreise))
                                                            ->whereRaw('? != anreise', array($anreise))
                                                            ->whereRaw('? != abreise', array($abreise));
                                                    });
                                            });
                                    });
                            })->get();

    }

I dont know why, but this solved my problem. Maybe somebody has an idea why i had to do this.

Thx in advance.