获取日期范围的查询结果

问题描述

我有以下查询:

    $input_datestart = date("Y-m-d", strtotime($data["date_start"]));
    $input_dateend = date("Y-m-d", strtotime($data["date_end"]));

    //Calculate the recurrent dates
    $query = $query->join('events_dates_recurrent', 'events.id', '=', 'events_dates_recurrent.event_id')
        ->whereRaw("ABS(DATEDIFF('" . $input_datestart . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0");

一天之内，我现在就得到了期望的结果，那就是从数据库中获取给定start_date的重复事件.
但是，当给出日期范围(即开始和结束日期)时，我不确定如何在单个查询中获得所有结果，同时又避免了循环，但要考虑到仍然需要提取循环事件.

For a single day, I get the desired result now, which is to get recurrent events from my database for a given start_date.
When a date range is given however (ie. start AND end date), I'm unsure about how to get all results in a single query while avoiding a loop, taken into account that the recurrent events are still to be fetched.

有什么建议吗?

编辑:表的结构非常简单:

Edit: The table structure is rather simple:

表events_dates_recurrent:

event_id  |  start_date  |  end_date  |  repeat_interval

通过内部联接将事件ID链接到表events的位置.
我需要检索给定日期范围内的所有重复事件.
暂时可以忽略"events_dates_recurrent"表中的end_date，也许稍后可以达到目的，但此查询并非严格要求.

Where event id is being linked to the table events with an inner join.
I need to retrieve all recurrent events within a given date range.
The end_date from the 'events_dates_recurrent' table can be ignored for now, perhaps this may serve a purpose later on, but it isn't strictly required for this query.

推荐答案

我对这种解决方案并不特别满意，但是它可以做到:我基本上要做的是遍历输入日期间隔并创建一个动态查询字符串.
我的应用程序始终将请求的日期/时间间隔限制为@maximum 1个月，因此查询字符串不应超过最大字符串限制.
不过，我对性能并不是100％满意，我们将看看效果如何.

I am not particularly satisfied with this solution, but it does what it is supposed to do:What I basically do is loop through the input date interval and create a dynamic query string.
My application always limits the requested date / time interval to @maximum 1 month, so the query string should never exceed the max string limit.
I am not 100% about the performance though, we will see how that works out.

//Calculate the recurrent dates
$query = $query->join('events_dates_recurrent', 'events.id', '=', 'events_dates_recurrent.event_id')
    ->where(function($join) use ($input_date_start, $input_date_end) {
        //Create a dynamic query to get all recurrent dates within the input time interval
        $query_string = "ABS(DATEDIFF('" . $input_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
        $temp_date_start = $input_date_start;

        while(strtotime($temp_date_start) <= strtotime($input_date_end)){
            $temp_date_start = date('Y-m-d',strtotime($temp_date_start . " +1 day"));
            //Create a raw query string
            $query_string = $query_string . " OR ABS(DATEDIFF('" . $temp_date_start . "', CAST(events_dates_recurrent.start_date AS DATE)) % events_dates_recurrent.repeat_interval) = 0";
        }
        $join->whereRaw($query_string);
    });

这篇关于获取日期范围的查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！