mysql - 在 Rails 中计算日期范围内每天记录数的有效方法，无需每天进行一次查询

Question

Rails 应用程序使用fullcalendar.js生成一个预订日历，显示每天的入住、退房和进行中 reservations 每个日期。

这是一个示例结果:

使用 SQL，我可以按 check-in 对预订进行分组和check-out并轻松生成包含每天计数的 JSON(因为这两个日期 - checkin 和 checkout - 是数据库中的实际列)。只需进行 2 次查询(一次用于 checkin ，另一次用于 checkout )。

但我不知道如何处理 in progress计数，因为日期不在数据库中，为 void check_in之间和check_out日期。

我当前的解决方案(我能想到的唯一一个)需要循环每个 day日历并为 check_in < day AND check_out > day 的预订发出 SQL 计数，但这需要大量 SQL 查询(日历 View 中的每一天一个)。这是当前的工作代码:

# Start_date and end_date are provided by fullcallendar.js params
start_date, end_date = Date.parse(start_date_string), Date.parse(end_date_string)

# In progress
last_in_progress_event = nil

array = []

(start_date..end_date).each do |day|

  count = current_user.reservations.where("DATE(check_in) < :date AND DATE(check_out) > :date", date: day).size

  if count > 0
    # If the count > 0, there's an active reservation on this day. If the
    # count changed when compared to the previous day, we create a new event
    # on fullcalendar (to change the `title` of the event with the new count).
    # If the count remained the same as the previous event, we can just update
    # it's end date to the current date being analyzed so se get a nice UI
    # (continuous event while the count doesn't change instead of one event
    # per day)

    if last_in_progress_event && last_in_progress_event[:count] == count
      # Day + 1 is required due to fullcalendar.js cutting the event short one day
      last_in_progress_event[:end] = (day + 1)
    else
      last_in_progress_event = {
        title: "#{count} in progress",
        start: day,
        end: (day + 1),
        allDay: true,
        className: 'bgm-lightblue',
        count: count
      }

      array.push(last_in_progress_event)
    end

  end # if count > 0

end

array

有没有办法构建 {day: in_progress_count} 的哈希值？每月没有 30 多个查询？

Answer 1

你能尝试一下这样的东西吗？

CREATE PROCEDURE `GetInprogressCount`(m_DateFrom DATE,
                                       m_DateTo DATE)
BEGIN

    DECLARE m_Counter INTEGER;
    DECLARE m_SQLDates LONGTEXT;
    DECLARE m_CheckDate DATE;
    SET m_Counter = 0;
    SET m_SQLDates = '';

    CreateDates: LOOP
      SET m_CheckDate = DATE_ADD(m_DateFrom, INTERVAL m_Counter DAY);
      SET m_SQLDates = CONCAT(m_SQLDates, IF(Trim(m_SQLDates) <> "", " UNION ALL ",""),"(SELECT '", m_CheckDate,
                              "' AS CheckDate )");
      SET m_Counter = m_Counter + 1;
      IF m_Counter < DATEDIFF(m_DateTo, m_DateFrom)+1 THEN
          ITERATE CreateDates;
      ELSE
          LEAVE CreateDates;
      END IF;

    END LOOP CreateDates;
    SET @m_SQLInProgress = CONCAT("SELECT v.CheckDate, Count(*) InProgressCount FROM (",m_SQLDates,") v ",
                                  "LEFT JOIN yourtable t ON t.check_in < v.CheckDate AND ",
                                  "t.check_out > v.CheckDate GROUP BY v.CheckDate"); 

    PREPARE m_SQLDates FROM @m_SQLInProgress;
    EXECUTE m_SQLDates;
    DEALLOCATE PREPARE m_SQLDates;
END

根据您的要求修改代码。不断尝试，直到获得所需的输出。