Django过滤器检查给定日期之间是否还有其他预订

Question

我正在尝试制作酒店预订应用程序。所以我有两个模型，即房间和预订。

# 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()

当所需房间已经有另一个预订时，我想通知房间是否不可用，或者如果它确实可用，则创建一个预订。

我正在使用 django rest 框架，所以我必须在 create 和 update 方法上应用这些验证，可能是这样的:

# 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

例如:

101 房间已在 01-12-2019 至 04-12-2019 之间预订。

如果有人试图预订同一个房间，我想阻止预订 (101)

29-11-2019 至 01-12-2019

29-11-2019 至 02-12-2010

30-11-2019 至 05-12-2019

01-12-2019 至 04-12-2019

02-12-2019 至 03-12-2019

04-12-2019 至 07-12-2019

现在这就是我的过滤方式，但我不确定这是否是最好的方法，因为我可能会错过其他会导致重复预订的预订，也许还有另一种方法可以解决这个问题？

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

你能帮我如何检查新日期之间是否有任何预订吗？

Answer 1

简答

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()

备注 :
1. is_occupied 将是一个 bool 值
2. 我用过 date filter ，因为 OP 仅提供 date ，不是 datetime

解释



有人在 之间订了房间(101) 01-12-2019 和 06-12-2019 ，用红色标记。根据 OP 中给出的条件，可用插槽标记为绿色。

逻辑

输入“结束时间”应该小于 可用 booked_for_datetime__date

输入“开始时间”应该大于 可用 booked_till_datetime__date

shell 输出

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