0

My code snippet:

boards_qs = BoardRate.objects.filter(
        Q(room_status="OP") &
        Q(hotel_log__date__range=[check_in_date, check_out_date]) &
        Q(hotel_log__hotel__pk__in=hotel_id_list) &
        Q(hotel_log__room_to_sell__gte=F("hotel_log__room_sold") + requested_similar_room_type_amount)
    )

In the above snippet I want the following line to work such a way that if some date is missing then it should not return others. What actually happens in range/in operator is that if it finds one or more matching data it returns those data in the queryset. But that's not what I need. I need to make sure that if all the date matches then only return the queryset otherwise it should be empty.

Q(hotel_log__date__range=[check_in_date, check_out_date])

We can also use "in" operator if the solution for in operator is possible. In this case code snippet will be:

Q(hotel_log__date__range=date_list)

For better understanding, I am adding the models also


class BoardRate(models.Model):
   STATUS_CHOICES = [("OP", "open"), ("CL", "close")]
   RATE_CHOICES = [("NOR", "refundable"), ("NRF", "non-refundable")]
   id = models.BigAutoField(primary_key=True)
   hotel_log = models.ForeignKey(
       "hotel_dashboard.HotelLogBook", on_delete=models.CASCADE, blank=True, null=True)
   room_status = models.CharField(
       max_length=25, choices=STATUS_CHOICES, default="OP")
   room_rate = models.DecimalField(default=0, max_digits=10, decimal_places=2)
   board_type = models.ForeignKey(
       UnifiedHotelBoardType, on_delete=models.CASCADE, related_name='hotel_log')
   rate_class = models.CharField(
       max_length=3, blank=True, choices=RATE_CHOICES, default="NRF")


class HotelLogBook(models.Model):
   log_id = models.BigAutoField(primary_key=True)
   hotel = models.ForeignKey(
       UnifiedHotel, on_delete=models.CASCADE, related_name='hotel_log')
   room = models.ForeignKey(
       UnifiedHotelRoomDetailsPerHotelPerRoom, on_delete=models.CASCADE, related_name='room_log')
   date = models.DateField(null=True, blank=True)
   room_to_sell = models.PositiveIntegerField(default=0)
   room_sold = models.PositiveIntegerField(default=0)
   board_rate = models.ManyToManyField(
       BoardRate, related_name="hotel_log_boards", blank=True)

Example:

suppose in the table we have 13,14,15,16,17 April and in the filter, we are searching for 13 to 19 April. Now if we use "in" operator we will get the data from 13 to 17 April but I don't want that. If any of the days of 13 to 19 April data is not available then I want an empty queryset.

Prediction:

I believe this can be done using "when" and "then" or "case" but I am lost here. Can someone guide me on that?

Navid
  • 53
  • 10
  • Are dates repeated? – Iain Shelvington Apr 05 '20 at 09:30
  • I'm not sure what you mean with "all the date matches", could you put an example? – gdvalderrama Apr 05 '20 at 09:42
  • suppose in the table we have 13,14,15,16,17 April and in the filter, we are searching for 13 to 19 April. Now if we use "in" operator we will get the data from 13 to 17 April but I don't want that. If any of the days of 13 to 19 April data is not available then I want empty queryset. Dates can be repeated. – Navid Apr 05 '20 at 10:16
  • @navid `range` and `in` just checks if the date matches any date defined in the list, it doesn't check that ALL dates in the list exist. Could you explain how your models are related? – gdvalderrama Apr 05 '20 at 11:04
  • @guival I have updated the question and added models, please let me know if you think anything is needed – Navid Apr 05 '20 at 11:17

1 Answers1

0

You could count the number of related dates and filter where the count is equal to the number of dates you expect

Model.objects.filter(
    model_two__date__range=[start_date, end_date]
).annotate(
    num_days=Count('model_two__date', distinct=True)
).filter(
    num_days=(end_date - start_date).days + 1
)
Iain Shelvington
  • 31,030
  • 3
  • 31
  • 50