Django filter to check if there are any other booking between the given dates

Question

I am trying to make a hotel booking app. So I have two models, i.e., Room and Booking.

# models
class Room(models.Model):
    name = models.CharField(max_length=50)

class Booking(models.Model):
    room = models.ForeignKey(Room, on_delete=models.CASCADE)
    booked_for_datetime = models.DateTimeField()
    booked_till_datetime = models.DateTimeField()

I would like to inform if the room is unavailable when there is already another booking for the required room, or else if it is actually available then create a booking.

I am working with django rest framework, so I will have to apply these validation on the create and update method, maybe something like this:

# serializers

def roomAvailable(validated_data):
    available = False

    # ...
    # validation check here...
    # ...

    return available


class BookingSerializer(serializers.ModelSerializer):
    class Meta:
        model = Booking
        fields = '__all__'

    def create(self, validated_data):
        if roomAvailable(validated_data):
            return Booking.objects.create(**validated_data)
        else:
            raise serializers.ValidationError({
                "detail": "Room is not available for these dates."
            })

    def update(self, instance, validated_data):
        if roomAvailable(validated_data):
            ...
            instance.save()
        else:
            raise serializers.ValidationError({
                "detail": "Room is not available for these dates."
            })
        return instance

Eg:

Room 101 was already booked between 01-12-2019 to 04-12-2019.

I would like to block the booking if someone's trying to book the same room (101)

• between 29-11-2019 to 01-12-2019
• between 29-11-2019 to 02-12-2010
• between 30-11-2019 to 05-12-2019
• between 01-12-2019 to 04-12-2019
• between 02-12-2019 to 03-12-2019
• between 04-12-2019 to 07-12-2019

For now this is how I am filtering, but I am not sure if this is the best approach as I might be missing other bookings which will lead to double bookings, and maybe there is another way to approach this problem?

def roomAvailable(validated_data):
    available = True
    ...
    if room.booking_set.filter(
            Q(booked_till_datetime__date__gte=validated_data['booked_for_datetime'],
              booked_till_datetime__date__lte=validated_data['booked_till_datetime']) |

            Q(booked_for_datetime__date__gte=validated_data['booked_for_datetime'],
              booked_for_datetime__date__lte=validated_data['booked_till_datetime']) |

            Q(booked_for_datetime__date__gte=validated_data['booked_for_datetime'],
              booked_till_datetime__date__lte=validated_data['booked_till_datetime']) |

            Q(booked_for_datetime__date__lte=validated_data['booked_for_datetime'],
              booked_till_datetime__date__gte=validated_data['booked_till_datetime'])
    ).exists():
        available = False

    return available

Could you please help me how can I check if there are any booking between the new dates?

Answer 1

Short answer

start_date = '2019-11-29'
end_date = '2019-12-01'
filter_params = dict(booked_for_datetime__date__lte=end_date, booked_till_datetime__date__gte=start_date) # just for redability
is_occupied = Booking.objects.filter(**filter_params, room__name=101).exists()

Notes:
1. is_occupied will be a boolean
2. I have used the date filter, since OP provided only date, not a datetime

---

Explanation

Someone booked the room (101) between 01-12-2019 and 06-12-2019, which is marked in Red. According to the conditions given in the OP, the available slots are marked in Green.

Logic

1. input "end time" should be less than available booked_for_datetime__date
   
2. input "start time" should be greater than available booked_till_datetime__date

Shell output

In [2]: date_list = [ 
   ...:     dict(start='2019-11-29', end='2019-12-01'), 
   ...:     dict(start='2019-11-29', end='2019-12-02'), 
   ...:     dict(start='2019-11-30', end='2019-12-05'), 
   ...:     dict(start='2019-12-01', end='2019-12-04'), 
   ...:     dict(start='2019-12-02', end='2019-12-03'), 
   ...:     dict(start='2019-12-02', end='2019-12-04'), 
   ...:     dict(start='2019-12-03', end='2019-12-04'), 
   ...:     dict(start='2019-12-02', end='2019-12-05'), 
   ...:     dict(start='2019-12-04', end='2019-12-06'), 
   ...:     dict(start='2019-12-04', end='2019-12-07'), 
   ...:     dict(start='2019-12-06', end='2019-12-07'), 
   ...:     dict(start='2019-12-07', end='2019-12-10'), 
   ...:     dict(start='2019-11-29', end='2019-11-30'), 
   ...: ]                                                                                                                                                                                                          

In [3]:                                                                                                                                                                                                            

In [3]: Booking.objects.all()                                                                                                                                                                                      
Out[3]: <QuerySet [<Booking: Booking object (1)>]>

In [4]: Booking.objects.all()[0].__dict__                                                                                                                                                                          
Out[4]: 
{'_state': <django.db.models.base.ModelState at 0x7efee64d5a58>,
 'id': 1,
 'room_id': 1,
 'booked_for_datetime': datetime.datetime(2019, 12, 1, 2, 45, 9, tzinfo=<UTC>),
 'booked_till_datetime': datetime.datetime(2019, 12, 5, 2, 45, 8, tzinfo=<UTC>)}

In [5]: # booked the room from 01-12-2019 to 05-12-2019                                                                                                                                                            

In [6]: for date in date_list: 
   ...:     start_date, end_date = date.values() 
   ...:     filter_params = dict(booked_for_datetime__date__lte=end_date, booked_till_datetime__date__gte=start_date) 
   ...:     is_occupied = Booking.objects.filter(**filter_params, room__name=101).exists() 
   ...:     if is_occupied: 
   ...:         print('Not Available on this range, start: {} end: {}'.format(start_date, end_date)) 
   ...:     else: 
   ...:         print('Available on this range, start: {} end: {}'.format(start_date, end_date)) 
   ...:                                                                                                                                                                                                            
Not Available on this range, start: 2019-11-29 end: 2019-12-01
Not Available on this range, start: 2019-11-29 end: 2019-12-02
Not Available on this range, start: 2019-11-30 end: 2019-12-05
Not Available on this range, start: 2019-12-01 end: 2019-12-04
Not Available on this range, start: 2019-12-02 end: 2019-12-03
Not Available on this range, start: 2019-12-02 end: 2019-12-04
Not Available on this range, start: 2019-12-03 end: 2019-12-04
Not Available on this range, start: 2019-12-02 end: 2019-12-05
Not Available on this range, start: 2019-12-04 end: 2019-12-06
Not Available on this range, start: 2019-12-04 end: 2019-12-07
Available on this range, start: 2019-12-06 end: 2019-12-07
Available on this range, start: 2019-12-07 end: 2019-12-10
Available on this range, start: 2019-11-29 end: 2019-11-30

Answer 2

Rather than looking for the bookings that overlap the given date range, it's easier to exclude those that don't:

def roomAvailable(validated_data):
    ...

    bookings = room.booking_set.exclude(
        booked_for_datetime__date__gt=validated_data['booked_till_datetime'],
    ).exclude(
        booked_till_datetime__date__lt=validated_data['booked_for_datetime'],
    )

    return not bookings.exists()

In this way you are basically excluding from your queryset those bookings that end before or start after the given date period, if there is any bookings that don't respect this condition, then the room is not available.

Note that the two conditions need to live in two different exclude to have an OR between them, if you put them together what you will obtain is an AND which is not what you want.