0

I have two tables with this informations:

Room

id room name
1 room 1
2 room 2

and a child table with this information:

Reservation

id room id (reserved_rooms) from date to date
1 1 2022-05-20 2022-05-22
2 1 2022-05-23 2022-05-25

I want to get available rooms for a certain amount of time:

  1. Witch doesn't have any reservation
  2. rooms where no reservation has been made at a specific time such as ‍‍2022-05-24

I tried the following code, but the problem is that the booked room is returned with id=1, while it has a reservation on the desired date.

Room.objects.filter(Q(reserved_rooms__isnull=True) | 
                    Q(reserved_rooms__from_date__gt=date) | 
                    Q(reserved_rooms__to_date__lt=date))

This query ignored reservation with id=2, because 2022-05-23 < 2022-05-24 < 2022-05-25, but reservation with id=1 causes that room with id=1 is returned. because 2022-05-24 not in range 2022-05-20 and 2022-05-22. so I expected that the only room with id=2 would be returned, but both 1 and 2 were returned.

What is your suggestion to solve this challenge?

Amirhossein Azhdari
  • 170
  • 1
  • 3
  • 20
  • 1
    This seems like two different queries, but you only show one. I wouldn't try to combine the two conditions. Or at least try to do them separately before combining them. – Code-Apprentice May 26 '22 at 17:25
  • 1
    Trying to do each condition separately will show you that you have an "or" where you should have an "and". – Code-Apprentice May 26 '22 at 17:26
  • Thanks for your answer @Code-Apprentice, I did what you said – Amirhossein Azhdari May 27 '22 at 13:33
  • When I said two separate queries, I meant one for what you describe in #1 and another for #2. Separating the two conditions will help you find the error in your logic. Then you can recombine in a single query. – Code-Apprentice May 28 '22 at 15:34

1 Answers1

2

What about

qs1 = Room.objects.filter( reserved_rooms__isnull=True ) 
qs2 = Room.objects.exclude( reserved_rooms__isnull=True
      ).exclude( reserved_rooms__from_date__gt=date,
                 reserved_rooms__to_date__lt=date )
      )
result = q1.union(q2)

I'm not sure if one has to .exclude( reserved_rooms__isnull=True) in qs2, but I doubt it's worth the effort of finding out! The second exclude with two arguments is the usual and-logic.

nigel222
  • 7,582
  • 1
  • 14
  • 22
  • The first query shouldn't require a `Q` object. – Code-Apprentice May 26 '22 at 17:23
  • True. I copied the OP's code then modified it. Edited. – nigel222 May 27 '22 at 08:24
  • Thanks for your response, I handled it with two separate queries: `Room.objects.all()` and `Reservation.objects.filter(from_date__lte=date, to_date__gte=date)`. Then I removed rooms from the first query response, which these rooms' IDs are in the second query response. – Amirhossein Azhdari May 27 '22 at 13:41
  • @AmirhosseinAzhdari note that this is less efficient than using relationships directly in your query such as `reserved_rooms__from_date__gt` and `reserved_rooms__from_date__lt` because it requires two separate queries. When I mentioned two queries, I was referring to the two criteria that you describe in words in your question. The problem with your original code is in the boolean logic. – Code-Apprentice May 28 '22 at 15:27