Как правильно составить запрос к базе?

Question

[Виталий]

Задача получить программу передач канала но не всю, а только три записи.
1) Какой фильм закончился.
2) Какой фильм идёт (или если сейчас реклама то какой фильм следующий).
3) Какой следующий по програме.

База PGSQL
тип колонок start_time, end_time - TIMESTAMP
таблица channel_program:

-------------------------------------------------------
	channel_id | 	program_id | 	start_time  |  end_time |
-------------------------------------------------------
     1      |     1       |   9:00        |   10:00   |
-------------------------------------------------------
     1      |     2       |   10:02       |   11:00   |
-------------------------------------------------------
     1      |     3       |   11:10       |   11:30   |
-------------------------------------------------------
     1      |     4       |   11:35       |   12:00   |
-------------------------------------------------------
     1      |     4       |   12:00       |   12:30   |
-------------------------------------------------------

задача - получить ТЕКУЩУЮ запись и рядомстоящие по одной слева и справа.

например сейчас 10:30.
Получаем Текущую - это запись сo start_time = 10:02 и end_time = 11:00.

или например сейчас 11:05
Получаем Текущую - это запись сo start_time = 11:10 и end_time = 11:30.

Из всего что нужно сделать, получилось сделать самое малое. Вот

return $this->hasMany(ChannelProgram::class)->where('start','<', $date)->where('end','>', $date);

но под эту запись не попадает тот случай если сейчас например реклама... и как присоединить рядом стоящие записи?
Буду благодарен за любые подсказки.

Answer 1

[Виталий]

В итоге запрос получился таким

select "channels".*, 
(select "start" from "channel_program" where "channel_id" = "channels"."id" and "end" < '2019-07-02 06:10:21' order by "start" desc limit 1) as "start_time_show", 
(select "start" from "channel_program" where "channel_id" = "channels"."id" and "start" <= '2019-07-02 06:10:21' and "end" > '2019-07-02 06:10:21' limit 1) as "start_time_current", 
(select "start" from "channel_program" where "channel_id" = "channels"."id" and "start" > '2019-07-02 06:10:21' limit 1) as "start_time_notShow"
    from "channels" where "channels"."deleted_at" is null

Сделал через scope

public function scopeScheduleShows($query)
    {
        $date = \Carbon\Carbon::now();
        $query->addSubSelect('start_time_show', ChannelProgram::select('start')
            ->whereColumn('channel_id', 'channels.id')
            ->where('end','<', $date)
            ->latest('start')
        )->with('show.program');
    }
    public function scopeScheduleCurrents($query)
    {
        $date = \Carbon\Carbon::now();
        $query->addSubSelect('start_time_current', ChannelProgram::select('start')
            ->whereColumn('channel_id', 'channels.id')
            ->where('start','<=', $date)
            ->where('end','>', $date)
        )->with('current.program');
    }

    public function scopeScheduleNotShows($query)
    {
        $date = \Carbon\Carbon::now();
        $query->addSubSelect('start_time_notShow', ChannelProgram::select('start')
            ->whereColumn('channel_id', 'channels.id')
            ->where('start','>', $date)
        )->with('notShow.program');
    }

В контроллере вызываю так

Channel::scheduleShows()->scheduleCurrents()->scheduleNotShows()->paginate(10);

Подзапросы составить помогла эта статься https://laravelnews.ru/dinamicheskie-otnosheniya-v... В ней описывается откуда берётся "addSubSelect".

Answer 2

[Максим Y]

Раз тег не ORM, то можете погонять на своих тестовых данных SQL.
Подставьте ваш $date вместо timestamp '2019-06-27 11:15:00'

/*
with channel_program as (
  select 1 as channel_id, 1 as program_id, timestamp '2019-06-27 09:00:00' as start_time, timestamp '2019-06-27 10:00:00' as end_time union all
  select 1 as channel_id, 2 as program_id, timestamp '2019-06-27 10:02:00' as start_time, timestamp '2019-06-27 11:00:00' as end_time union all
  select 1 as channel_id, 3 as program_id, timestamp '2019-06-27 11:10:00' as start_time, timestamp '2019-06-27 11:30:00' as end_time union all
  select 1 as channel_id, 4 as program_id, timestamp '2019-06-27 11:35:00' as start_time, timestamp '2019-06-27 12:00:00' as end_time union all
  select 1 as channel_id, 4 as program_id, timestamp '2019-06-27 12:00:00' as start_time, timestamp '2019-06-27 12:30:00' as end_time
)
*/

select t.program_id
  from (
        select 
               program_id
              ,lag(start_time) over (order by start_time) lag_start_time
              ,lead(end_time) over (order by start_time) lead_end_time
          from channel_program
       ) t
 where timestamp '2019-06-27 11:15:00' between coalesce(t.lag_start_time, timestamp '1991-01-01 00:00:00') 
                                           and coalesce(t.lead_end_time, timestamp '2999-01-01 00:00:00')