php - 结合两个 SQL 查询 PDO

Question

我对以下问题很困惑。我有一系列表格:

我想做的是获取房间的所有信息，假设预订量不超过该房间的可用房间号。

因此，为了获取我的房间详细信息，我的 SQL 是这样的:

  SELECT Rooms.RoomID as RoomID,
         RoomName, NumOfRooms, 
         MaxPeopleExistingBeds, 
         MaxExtraBeds, 
         MaxExtraPeople, 
         CostPerExtraPerson, 
         MaximumFreeChildren, 
         IncludeBreakfast, 
         MinRate 
    FROM Rooms, RoomDetails 
   WHERE Rooms.AccommodationID = :aid AND 
         Rooms.RoomID = RoomDetails.RoomID 
GROUP BY RoomName

返回后，我得到了这些房间的详细信息列表，如下所示:

然后我使用此查询获取预订数量和房间 ID:

  SELECT Booking.RoomID, 
         count(Booking.RoomID) as Bookings  
    FROM Booking 
   WHERE ArriveDate >= :aDate AND 
         DepartDate <= :dDate AND 
         AccommodationID = :aid 
GROUP BY RoomID

然后我将两者结合起来，并使用此函数将两个数组反馈到一个数组中:

public function get_availability($aid, $aDate, $dDate) {
        $stmt = $this->db->prepare('SELECT Rooms.RoomID as RoomID, RoomName, NumOfRooms, MaxPeopleExistingBeds, MaxExtraBeds, MaxExtraPeople, CostPerExtraPerson, MaximumFreeChildren, IncludeBreakfast, MinRate FROM Rooms, RoomDetails WHERE Rooms.AccommodationID = :aid AND Rooms.RoomID = RoomDetails.RoomID GROUP BY RoomName');
        $stmt->bindValue(':aid', $aid);
        $stmt->execute();
        $rooms = $stmt->fetchAll(PDO::FETCH_ASSOC);
        $stmt2 = $this->db->prepare('SELECT Booking.RoomID, count(Booking.RoomID) as Bookings  FROM Booking WHERE ArriveDate >= :aDate AND DepartDate <= :dDate AND AccommodationID = :aid GROUP BY RoomID');
        $stmt2->bindValue(':aid', $aid);
        $stmt2->bindValue(':aDate', $aDate);
        $stmt2->bindValue(':dDate', $dDate);
        $stmt2->execute();
        $bookings = $stmt2->fetchAll(PDO::FETCH_ASSOC);
        $room = array($rooms, $bookings);

        return (!empty($room)) ? $room : false;
    }

问题是，我真正想做的只是返回 NumOfRooms 小于预订数量的房间详细信息。

例如，如果我有 $bookings，如果它告诉我房间 ID 4，我在一段时间内有 3 个预订，而我的 NumOfRooms 是 1。那么我就知道我那周没有能力接受任何更多预订。但是，如果我有 1 个预订和一个容量，那么它仍然是满的。但是，如果我的 NumOfRooms 为 2，并且预订量为 1，我就知道我有房间。

所以基本上如果 NumOfRooms > BookingCount 那么房间是可用的。

我如何合并这两个查询并简化我的代码以实现这一点？

简单地说，我如何从 RoomDetails 中选择所有信息，给定 Booking 中的 ArriveDate 和 DepartDate 以及 RoomID，其中 NumOfRooms > count(Booking.RoomID)(它在这些日期和房间内的位置id 等于 Rooms 的房间 id)。

Answer 1

您的问题可以通过简单地更新 SQL 语句本身来解决:

SELECT r.RoomID AS RoomID,
       RoomName,
       NumOfRooms,
       MaxPeopleExistingBeds,
       MaxExtraBeds,
       MaxExtraPeople,
       CostPerExtraPerson,
       MaximumFreeChildren,
       IncludeBreakfast,
       MinRate
FROM Rooms r
JOIN RoomDetails rd
    ON r.RoomID = rd.RoomID
JOIN (
    SELECT b.RoomID,
           AccommodationID,
           count(b.RoomID) AS Bookings
    FROM Booking b
    WHERE ArriveDate >= :aDate
      AND DepartDate <= :dDate
    GROUP BY RoomID
) t
    ON t.AccommodationID = r.AccommodationID
WHERE r.AccommodationID = :aid
    AND t.Bookings < NumOfRooms
GROUP BY RoomName