php - MySQL如何从年份(参数)、weekOfYear(参数)、时间(数据库)和dayofweek(数据库)创建时间戳？

Question

我需要在现有项目上实现一些事件的显示。我无法更改数据库结构。

在我的 Controller 中，我(从 ajax 请求)传递了一个时间戳，并且我需要显示之前的 8 个事件。因此，如果时间戳是(转换后)2017-12-12 00:00:00，我需要显示 2017-12-12 之前的 8 个事件，按时间 DESC 排序。

这就是我的表格的设置方式:

-calendarrepeat
 -dayofweek // int 1-7; 1 = monday, 7 = sunday
 -event_date_begin // date "2016-05-01" - indicates from which day the event recurrs every week (dayofweek)
 -event_date_end // date "2017-05-02" - indicates until which day the event recurrs every week (dayofweek)
 -event_hour_begin // time "11:00:00"
 -event_hour_end // time "12:00:00"

我可以用时间戳做任何我想做的事。我用Carbon因此，获取一年中的星期、一年中的某一天或我需要的其他任何内容都不是问题。

供引用$fullTs是我传递到 Controller 的时间戳。连接部分工作正常。

我需要帮助的部分是:/* THIS PART has to be rewritten (it's COMPLETELY wrong at the moment) ! */

我需要从年份(作为参数传递)、weekOfYear(作为参数传递)、星期几(从数据库列)和时间(数据库列)生成时间戳图。我需要这样的东西(pseudoSQL):

->where(\DB::raw('THISTHINGY_TO_DATE($fullTs->year $fullTs->weekOfYear calendarrepeat.dayofweek calendarrepeat.event_hour_begin, "%Y %week-of-year %day-of-week %H:%i:%s), '<', $fullTs)))

这是我当前正在运行的完整查询(UNION 的第一部分，第二部分按预期工作):

$eventsRepeat = CalendarRepeat::join('calendarrepeat_translations', function ($j) use ($locale) {
        $j->on('calendarrepeat.id', '=', 'calendarrepeat_translations.calendarrepeat_id')
            ->where('calendarrepeat_translations.locale', '=', $locale);
    })
    ->orderBy('calendarrepeat.event_date_begin', $orderBy)
    /* THIS PART has to be rewritten (it's COMPLETELY wrong at the moment) ! */
    /* the STR_TO_DATE(..) part has to get date from a) week passed in $fullTs b) dayoftheweek written in calendarrepeat.dayoftheweek c) time written in calendarrepeat.event_hour_begin */
    ->where(\DB::raw("STR_TO_DATE(CONCAT(calendarrepeat.event_date_begin, ' ', calendarrepeat.event_hour_begin), '%Y-%m-%d %H:%i:%s')"), '<', $fullTs)
    ->where('event_date_begin', '>', $fullTs)
    ->where('event_date_end', '<', $fullTs)
    ->limit(8)
    ->select([
        'calendarrepeat.event_date_begin as date', 'calendarrepeat.event_hour_begin as start',
        'calendarrepeat.event_hour_end as end', 'calendarrepeat_translations.title as title', \DB::raw("CONCAT(calendarrepeat.event_date_begin, ' ', calendarrepeat.event_hour_begin) as date_whole") // This is also wrong
    ]);

这可能吗？如何？有没有更好的方法来做到这一点？

另一件需要注意的事情是，在我们的数据库中，1 = 星期一，7 = 星期日，据我了解，这不是枚举工作日的常用方法。

提前致谢。

Answer 1

尝试一下并检查您得到的结果:

$eventsRepeat = CalendarRepeat::join('calendarrepeat_translations', function ($j) use ($locale) {
$j->on('calendarrepeat.id', '=', 'calendarrepeat_translations.calendarrepeat_id')
    ->where('calendarrepeat_translations.locale', '=', $locale);
})->select([
  'calendarrepeat.id',
  'calendarrepeat_translations.calendarrepeat_id',
  'calendarrepeat_translations.locale','calendarrepeat.event_date_begin as date',
  'calendarrepeat.event_hour_begin as start',
  'calendarrepeat.event_hour_end as end',
  'calendarrepeat_translations.title as title',
  \DB::raw("CONCAT(calendarrepeat.event_date_begin, ' ', calendarrepeat.event_hour_begin) as date_whole")
])->where(\DB::raw("STR_TO_DATE(CONCAT(date, ' ', start), '%Y-%m-%d %H:%i:%s')"), '<', $fullTs)
->where('date', '>', $fullTs)
->where('end', '<', $fullTs)
->orderBy('date', $orderBy)
->limit(8);