Как вывести записи по ближайшей дате сгруппированные по категориям в MySQL 5.7?

Question

[busidoway]

Есть три таблицы: events, events_categories, event_category_joins (связующая). Необходимо вывести записи с ближайшей датой сгруппированные по категориям. Версия mysql: 5.7.

Пример вывода без группировки:


Код с группировкой:

select `events`.`id`,
	`events`.`date_public`,
	`event_categories`.`id` as `cat_id`,
    `event_categories`.`title` as `cat_title`
	from `events`
    inner join `event_category_joins` on `events`.`id` = `event_category_joins`.`event_id` 
    inner join `event_categories` on `event_categories`.`id` = `event_category_joins`.`event_category_id` 
    where `events`.`date_public` >= CURDATE()
    group by `event_categories`.`id`
    order by `events`.`date_public` asc;

Вывод:


Выводит не по ближайшей дате, а по наименьшему events.id.

Пробовал группировать через join, находить наименьшую дату и сравнивать с датой основной таблицы :

select `events`.`id`,
	`events`.`date_public`,
	`event_categories`.`id` as `cat_id`,
    `event_categories`.`title` as `cat_title`
	from `events`
    inner join `event_category_joins` on `events`.`id` = `event_category_joins`.`event_id` 
    inner join `event_categories` on `event_categories`.`id` = `event_category_joins`.`event_category_id` 
    inner join (select `events`.`id` as `e_id`, min(`events`.`date_public`) as `min_date_public` 
                from `events` 
                inner join `event_category_joins` on `events`.`id` = `event_category_joins`.`event_id` 
                inner join `event_categories` on `event_categories`.`id` = `event_category_joins`.`event_category_id` 
                where `events`.`date_public` >= CURDATE()
                group by `event_categories`.`id`) as `events_cat` 
    on `events`.`id` = `events_cat`.`e_id`
    where `events`.`date_public` = `events_cat`.`min_date_public`
    order by `events`.`date_public` asc;

Выводит таким образом:


Как выловить-таки минимальную дату?

Comments

[Akina]

по ближайшей дате

Дайте ТОЧНОЕ определение того, какая дата является ближайшей.

А заодно, чтобы уж два раза не бегать - и К ЧЕМУ она должна быть ближайшей. По тексту я вижу литерал '2024-04-05 19:00:00' - то есть точка будет задаваться извне?

А, да! Ещё укажите ТОЧНО версию MySQL.

[busidoway]

Akina, да, сравнивается с текущей датой. Поправил в коде. Ближайшая, это та которая раньше наступит.
Версия mysql 5.7

[Akina]

busidoway, тогда добавьте в вопрос CREATE TABLE всех трёх таблиц. Лишние для вопроса поля, все индексы и прочую хрень - удалите.

[busidoway]

Akina,

CREATE TABLE `events` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `date_public` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `event_categories` (
  `id` bigint UNSIGNED NOT NULL,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `event_category_joins` (
  `id` bigint UNSIGNED NOT NULL,
  `event_id` bigint UNSIGNED NOT NULL,
  `event_category_id` bigint UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[Михаил Ливач]

Выводит не по ближайшей дате, а по наименьшему events.id.

busidoway, вообще-то нет. Собирает данные в порядке их чтения с диска, потом группирует, оставляя значения первой записи в группе. А уже к результату применилось ORDER BY .

Порядок автоинкрементых ID и порядок размещения на диске обычно совпадают. Но если одна или несколько записей из середины будет удалена, а потом вставлена обратно, то порядок нарушится.

[busidoway]

Михаил Ливач, да, скорее всего так и есть. Я вообще об этом не задумывался, пока не начали добавлять в базу записи с датами не по порядку, а вразброс.

Answer 1

[busidoway]

Кому интересно, вот решение:

SELECT 
    e.id,
    e.date_public,
    ec.id AS cat_id,
    ec.title AS cat_title
FROM 
    events e
INNER JOIN 
    event_category_joins ecj ON e.id = ecj.event_id
INNER JOIN 
    event_categories ec ON ec.id = ecj.event_category_id
INNER JOIN 
    (
        SELECT 
            ec.id AS cat_id, 
            MIN(e.date_public) AS min_date_public
        FROM 
            events e
        INNER JOIN 
            event_category_joins ecj ON e.id = ecj.event_id
        INNER JOIN 
            event_categories ec ON ec.id = ecj.event_category_id
        WHERE 
            e.date_public >= CURDATE()
        GROUP BY 
            ec.id
    ) ec_min ON ec.id = ec_min.cat_id AND e.date_public = ec_min.min_date_public
ORDER BY 
    e.date_public ASC;

Answer 2

[Akina]

На версии 8+ как-то так:

WITH cte AS (
    SELECT `events`.`id`,
           `events`.`date_public`,
	   `event_categories`.`id` as `cat_id`,
	   `event_categories`.`title` as `cat_title`
	   ROW_NUMBER() OVER (PARTITION BY `event_categories`.`id` ORDER BY `events`.`date_public` ASC) rn
    FROM `events`
    INNER JOIN `event_category_joins` ON `events`.`id` = `event_category_joins`.`event_id` 
    INNER JOIN `event_categories` ON `event_categories`.`id` = `event_category_joins`.`event_category_id` 
    WHERE `events`.`date_public` >= '2024-04-05 19:00:00'
--    WHERE `events`.`date_public` >= CURRENT_DATE
    )
SELECT id, date_public, cat_id, cat_title
FROM cte
WHERE rn = 1;

Comments

[busidoway]

Да, на 8+ работает. Но у меня на боевом сервере 5 версия.

Answer 3

[Rsa97]

"Ближайшая" - это только в будущее? Тогда

WHERE `date_public` = (
  SELECT MIN(`date_public`) FROM `events` WHERE `date_public` >= CURDATE()
)

Comments

[busidoway]

В этом случае только одну запись выводит.

[Михаил Ливач]

как я понял автора, ближайшая - это наименьшая

[busidoway]

Михаил Ливач, да, т.е. которая раньше наступит.