CODEWITHSUNDEEP
pythondjangodjango-modelsdjango-views
smack
smack

Reputation: 950

lte & gte query inconsistent when Crosschecking for dates in Django

I want users to be able to check the calendar for dates already booked so the person won't be able to book a room if it has been booked already. I used the lte and gte to check for dates but it's very inconsistent.

See what my DB looks like below.

enter image description here

The main problem I'm facing,

From June 2 to June 13 have been booked for hotelroom id 1. It's returning available instead of not available.

>>> start_date='2016-06-02'
>>> end_date='2016-06-13'
>>>    check_for_bookings=HotelCalendar.objects.filter(Q(checkin_booked_date__gte=start_date) | Q(checkout_booked_date__lte=end_date), hotelrooms_id=1)
>>> if check_for_bookings:
...     print 'not available'
... else:
...     print 'available'
...
available
>>>

I selected from June 3 to June 14 and tested it with the below query and it worked. It showed that the room is not available.

>>> start_date='2016-06-03'
>>> end_date='2016-06-14'
>>> check_for_bookings=HotelCalendar.objects.filter(Q(checkin_booked_date__gte=start_date)|Q(checkout_booked_date__lte=end_date), hotelrooms_id=1)
>>> if check_for_bookings:
...     print 'not available'
... else:
...     print 'available'
...
not available

The question is why did the first query failed to return 'not available' when the dates have been booked.?

What other query can I run to make it efficient?

Upvotes: 1

Views: 403

Answers (1)

Moses Koledoye
Moses Koledoye

Reputation: 78564

Your conditions have been swapped gte <> lte . The second query worked because there is a matching date '2016-06-14' for hotelrooms_id=1.

But you want to check if start_date and end_date are within range checkin_booked_date to checkout_booked_date:

check_for_bookings = HotelCalendar.objects.filter(checkin_booked_date__lte=start_date, 
                                                  checkout_booked_date__gte=end_date,
                                                  hotelrooms_id=1)

Use exists if you only need to check and not fetch the objects:

if HotelCalendar.objects.filter(checkin_booked_date__lte=start_date,
                                checkout_booked_date__gte=end_date, 
                                hotelrooms_id=1).exists():

Update:

From this SO answer, we can tell if start and end dates overlap with the dates of occupancy of a client:

from datetime import datetime as dt

hotelcalendar = HotelCalendar.objects.filter(hotelrooms_id=1)

start_date = dt.strptime(start_date, '%Y-%m-%d')
end_date = dt.strptime(end_date, '%Y-%m-%d')

if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
     print "not available"
else:
     print "available"

Update:

I tweaked it this way: I changed 'filter' to 'get' because it will return 'AttributeError'. And I used datetime.date() directly. And it worked fine so far!

>>> import datetime
>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=1)
>>> start_date= datetime.date(2016, 06, 14)
>>> end_date= datetime.date(2016, 06, 19)
>>> if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
not available

>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=1)
>>> start_date= datetime.date(2016, 06, 15)
>>> end_date= datetime.date(2016, 06, 19)
>>> if hotelcalendar.checkin_booked_date <= end_date and  hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
available
>>> hotelcalendar= HotelCalendar.objects.get(hotelrooms_id=3)
>>> start_date= datetime.date(2016, 06, 02)
>>> end_date= datetime.date(2016, 06, 10)
>>> if hotelcalendar.checkin_booked_date <= end_date and hotelcalendar.checkout_booked_date >= start_date:
...     print 'not available'
... else:
...     print 'available'
...
not available
>>>

Upvotes: 2

Related Questions