Mysql查询获取酒店房间可用性

Question

我正在实现一个预订平台，我有 3 个表:

• "hotel"- 保存酒店信息
• “hotel_room”- 保存每个酒店的房间信息
• “hotel_room_price”- 按日期、可用房间数量和价格显示可用性

我想按开始日期和结束日期、地区和房间数搜索(每个房间有成人人数和 child 人数)

这是我的表格的一些示例:

CREATE TABLE `hotel` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `rating` smallint(6) NOT NULL DEFAULT '3' COMMENT '0 - Not Rated | 1 - One Star | 2 - Two Stars | 3 - Three Stars | 4 - Four Stars | 5 - Five Stars',
  `local` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `hotel_room` (
  `id` int(11) NOT NULL,
  `hotel_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `max_capacity_adult` smallint(6) NOT NULL,
  `max_capacity_child` smallint(6) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `hotel_room` ADD CONSTRAINT `fk_hotel_room_hotel_id` FOREIGN KEY (`hotel_id`) REFERENCES `hotel` (`id`) ON DELETE CASCADE;

CREATE TABLE `hotel_room_price` (
  `id` int(11) NOT NULL,
  `hotel_room_id` int(11) NOT NULL,
  `price_adult` decimal(20,2) DEFAULT NULL,
  `price_child` decimal(20,2) DEFAULT NULL,
  `quantity` int(11) NOT NULL DEFAULT '1' COMMENT 'available rooms, 0 if there is no more available',
  `date` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
ALTER TABLE `hotel_room_price` ADD CONSTRAINT `fk_hotel_room_price_hotel_room_id` FOREIGN KEY (`hotel_room_id`) REFERENCES `hotel_room` (`id`) ON DELETE CASCADE;

当用户搜索时获取可用房间的更好方法是什么，请注意，例如可以搜索多个房间:

start_date = 2019-06-25
end_date = 2019-06-29
local = "Tomar"
Room=[
  [
     nr_adults = 2,
     nr_children=1
  ],
  [
     nr_adults = 4,
     nr_children=0
  ]
]

我认为首先要做的是只检查本地的酒店，然后检查房间是否可以容纳成人和 child 的人数，如果可以，请检查是否有空房。我在创建一个查询或多个查询以正确的方式处理这个问题时遇到了很多问题。

您可以在此处查看我的数据库示例 http://sqlfiddle.com/#!9/458be2c

Answer 1

这里是选择给定时间范围内所有可用房间 ID 的查询。在本例中，我选择了 6 月 26 日至 6 月 28 日。这应该是查询其余部分的良好起点。

SELECT hotel_room_id
FROM hotel_room_price
WHERE date between '2019-06-26' AND '2019-06-28'
AND quantity > 0
GROUP BY hotel_room_id
HAVING COUNT(*) > DATEDIFF('2019-06-28', '2019-06-26')

这里是一个有点老套的查询，用于获取有关房间的一些信息。请注意，此示例中没有搜索多个房间的功能:

SELECT h.name AS Name, h.rating AS Rating, sq.name AS Type
FROM hotel h
INNER JOIN
(SELECT * 
FROM hotel_room
WHERE hotel_room.id IN
(SELECT hotel_room_id
FROM hotel_room_price
WHERE date between '[START DATE]' AND '[END DATE]'
AND quantity > 0
GROUP BY hotel_room_id
HAVING COUNT(*) > DATEDIFF('[END DATE]', '[START DATE]'))
AND max_capacity_child >= [CHILD COUNT]
AND max_capacity_adult >= [ADULT COUNT]) sq
ON h.id = sq.hotel_id
WHERE h.local = "[LOCATION]"