Как сделать SQL запрос, который возвращал бы все записи за текущий месяц и несколько записей из предыдущего и следующего, как в календаре
Как составить запрос так, чтобы он возвращал данные не только для дней в текущем месяце, а также и для нескольких дней предыдущего месяца и следующего, то есть для серых дней на картинке
Текущий запрос выглядит так:
SELECT e.id, e.name, e.city_id, e.address, e.description, e.url, array(SELECT tag_id FROM event_tags WHERE event_id = e.id),
e.age, jsonb_agg(jsonb_build_object('eventDate', dh.event_date, 'startTime', dh.start_time, 'endTime', dh.end_time))
FROM events e
INNER JOIN dates_hours dh on e.id = dh.event_id
WHERE extract(year from dh.event_date) = 2021 and extract(month from dh.event_date) = 7 and city_id = 1
group by e.id
и возвращает:
id | name | city_id | address | description | url | array | age | jsonb_agg
----+--------+---------+---------------+---------------+-----------------------+---------+-----+-------------------------------------------------------------------------------
4 | 123321 | 1 | улица Пушкина | Awesome event | http://event_url.com/ | {4,5,6} | 0 | [{"endTime": "16:10:00", "eventDate": "2021-07-01", "startTime": "15:10:00"}]
а хотелось бы, чтобы возвращал:
id | name | city_id | address | description | url | array | age | jsonb_agg
----+--------+---------+---------------+---------------+-----------------------+---------+-----+-------------------------------------------------------------------------------
4 | 123321 | 1 | улица Пушкина | Awesome event | http://event_url.com/ | {4,5,6} | 0 | [{"endTime": "16:10:00", "eventDate": "2021-07-01", "startTime": "15:10:00"}, {"endTime": "14:10:00", "eventDate": "2021-06-30", "startTime": "15:10:00"}, {"endTime": "14:10:00", "eventDate": "2021-06-29", "startTime": "15:10:00"}, {"endTime": "14:10:00", "eventDate": "2021-06-28", "startTime": "15:10:00"}, {"endTime": "14:10:00", "eventDate": "2021-08-01", "startTime": "15:10:00"}]
Ответы (2 шт):
Если решением будет выбрать события 5 дней прошлого месяца, и 5 дней следующего месяца, попробуйте такой код:
SELECT e.id, e.name, e.city_id, e.address, e.description, e.url, array(SELECT tag_id FROM event_tags WHERE event_id = e.id),
e.age, jsonb_agg(jsonb_build_object('eventDate', dh.event_date, 'startTime', dh.start_time, 'endTime', dh.end_time))
FROM events e
INNER JOIN dates_hours dh on e.id = dh.event_id
WHERE extract(year from dh.event_date) = 2021 and extract(month from dh.event_date) in (6, 7, 8) and dh.event_date between ('2021-06-26' and '2021-08-05') and city_id = 1
group by e.id
Старайтесь при выборках никогда не применять функции к колонкам базы данных в различных условиях, это сильно замедляет работу запроса. Стройте условия так, что бы колонка сравнивалась с заранее подготовленными граничными условиями.
Исходя из этого нам надо придумать формулы, которые вычислят понедельник календарной недели начала месяца и воскресенье календарной недели окончания месяца. Для их получения нам надо знать только номер дня недели первого и последнего дня, после чего вычесть/добавить их к этим дням.
SELECT e.id, e.name, e.city_id, e.address, e.description, e.url, array(SELECT tag_id FROM event_tags WHERE event_id = e.id),
e.age, jsonb_agg(jsonb_build_object('eventDate', dh.event_date, 'startTime', dh.start_time, 'endTime', dh.end_time))
FROM events e
INNER JOIN dates_hours dh on e.id = dh.event_id
WHERE city_id = 1
and dh.event_date between
'2021-07-01'::date - make_interval(days => extract(isodow from '2021-07-01'::date)::int-1)
and '2021-07-01'::date + interval '1 MONTH - 1 day' + make_interval(days => 7 - extract(isodow from '2021-07-01'::date + interval '1 MONTH - 1 day')::int)
group by e.id