php - 从日期范围查找可用房间

Question

我有开始日期和结束日期。

 $s="YYYY-MM-DD";
 $e="YYYY-MM-DD";

我需要进行查询来检查该范围内的可用房间。

 cObjects , cBookings_details
 cObjects_id = cBookings_details_cObjects_id

我真的被困在这里，非常感谢一些帮助。

我当前的查询

 SELECT cObjects.*
FROM cObjects LEFT JOIN cBookings_details ON cObjects.cObjects_id = cBookings_details.cBookings_details_cObjects_id
WHERE cBookings_details.cBookings_details_arrival NOT BETWEEN '".$s."' AND '".$e."' 
AND cBookings_details.cBookings_details_departure NOT BETWEEN '".$s."' AND '".$e."' 
AND cBookings_details.cBookings_details_arrival <> '".$s."' AND cBookings_details_departure <> '".$s."'
AND cBookings_details.cBookings_details_arrival <> '".$e."' AND cBookings_details_departure <> '".$e."'
AND cObjects.cObjects_type < '2'

OR cBookings_details.cBookings_details_id is null AND cObjects_type < '2'

Answer 1

这是一个常见问题..实际上只是在工作中为此编写了一个指南:

Selecting rows with start and end dates that feature in a chosen interval 
------------------------------------------------------------------------- 

Chosen Interval -  :start_date    (S) 
                   :end_date      (E)

Rows            -  row.start_date (s)
                   row.end_date   (e)

Increasing date-->

         S       E
         |       |           Chosen Interval 

1.     s---e                 Rows we want
2.        s---e                    .
3.            s---e                .
4.     s----------e                .

5.  s--e                     Rows we don't want
6.                s--e             .

LOGIC:
row.end_date >= :start_date AND row.start_date <= :end_date   

如果您正在寻找可用房间，请使用上述逻辑将房间LEFT JOIN添加到预订中，并SELECT预订为NULL的行>。

应用到您的架构中，我相信结果会是:

   SELECT o.*
     FROM cObjects o 
LEFT JOIN cBookings_details bd
       ON bd.cBookings_details_cObjects_id = o.cObjects_id
      AND bd.cBookings_details_arrival <= :end_date
      AND bd.cBookings_details_departure >= :start_date 
    WHERE bd.cBookings_details_id IS NULL
     (AND o.cObjects_type < '2') # Double check this is what you want

顺便说一句，如果我必须使用你的模式，我会哭。为什么要在列中重复表名？它只会增加噪音。