Как выбрать последнюю запись с внешним ключом, по которому фильтруем?

Question

[Игорь]

Проблема заключается в плохом знании mysql и очень стыдно.

Хочу разобраться вот с чем.
Нужно составить запрос для регулярного использования в дальнейшем.
Я выделил строки красным прямоугольником, это записи истории одного контакта.
Так вот, мне нужно выбрать все последние записи с определённым (ВНИМАНИЕ) последним статусом.

О чём я.

Приведу пример.

SELECT ch.
FROM contacts_history ch 
WHERE ch.created_at BETWEEN '2021-02-05 15:56:57' AND '2021-02-20 13:44:09' AND ch.status_id = 37

Когда я фильтрую по статусу, фактически я должен взять только самые новые записи, а у меня получается, что я беру последние записи со статусом 37. Но 37 не является последней записью.



Прилагаю схему

CREATE TABLE `contacts_history` (
	`id` BIGINT(19) NOT NULL AUTO_INCREMENT,
	`contact_id` BIGINT(19) NULL DEFAULT NULL,
	`owner_id` INT(10) NULL DEFAULT NULL,
	`status_id` INT(10) NULL DEFAULT NULL,
	`session_start_time` DATETIME NULL DEFAULT NULL,
	`session_end_time` DATETIME NULL DEFAULT NULL,
	`call_start_time` DATETIME NULL DEFAULT NULL,
	`call_end_time` DATETIME NULL DEFAULT NULL,
	`direction` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_direction_type)',
	`originator` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_originator_type)',
	`cause` TINYINT(3) NULL DEFAULT '0' COMMENT '(DC2Type:contact_call_cause_type)',
	`target` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`comment` LONGTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`status_result` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`status_color` VARCHAR(9) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`type` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_history_type)',
	`created_at` DATETIME NULL DEFAULT NULL,
	`audio_recording_id` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
	`organization_id` INT(10) NULL DEFAULT NULL,
	`project_id` BIGINT(19) NULL DEFAULT NULL,
	PRIMARY KEY (`id`) USING BTREE,
	INDEX `IDX_AD127C166BF700BD` (`status_id`) USING BTREE,
	INDEX `IDX_AD127C167E3C61F9` (`owner_id`) USING BTREE,
	INDEX `IDX_AD127C16E7A1254A` (`contact_id`) USING BTREE,
	INDEX `IDX_AD127C1632C8A3DE` (`organization_id`) USING BTREE,
	INDEX `IDX_AD127C16166D1F9C` (`project_id`) USING BTREE
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1;

INSERT INTO `contacts_history` VALUES (8, 11, 27, '2021-02-05 12:56:34', '2021-02-05 12:56:56', '2021-02-05 12:56:57', '2021-02-05 12:56:57', 2, 2, 6, '[phone]', 'Нет гудков', 'Недозвон', '#808080FF', 3, '2021-02-05 15:56:57', '965f0d2b-969e-4fbe-9999-9a21b0490a67', 1, 9);
INSERT INTO `contacts_history` VALUES (8, 11, 25, '2021-02-05 13:00:17', '2021-02-05 13:10:45', '2021-02-05 13:00:30', '2021-02-05 13:10:45', 2, 2, 5, '[phone]', 'Адекватный собеседник', 'Получил. Активировал.', '#00FF4FFF', 3, '2021-02-05 16:10:46', 'bc0303e8-91a3-445b-acc7-3abea5628629', 1, 9);
INSERT INTO `contacts_history` VALUES (10, 13, 37, '2021-02-05 14:56:41', '2021-02-05 14:57:41', '2021-02-05 14:57:41', '2021-02-05 14:57:41', 2, 1, 20, '[phone]', 'недозвон без гудков ', 'Текущий недозвон', '#808080FF', 3, '2021-02-05 17:57:41', 'f5fe529b-7130-400e-a9e4-ec09491c5e9a', 1, 9);
INSERT INTO `contacts_history` VALUES (11, 13, 35, '2021-02-05 15:00:29', '2021-02-05 15:02:30', '2021-02-05 15:00:38', '2021-02-05 15:02:30', 2, 2, 5, '[phone]', '6 .02 с 12 до 15.00 Денис Михайлович Ставропольский край, г Пятигорск, пр-кт 40 лет Октября, д 85', 'Обещает забрать', '#008CFFFF', 3, '2021-02-05 18:02:29', '7e1fc9df-7bdb-4ec3-b8ff-f30de49660c5', 1, 9);
INSERT INTO `contacts_history` VALUES (12, 13, 35, '2021-02-05 15:05:38', '2021-02-05 15:08:04', '2021-02-05 15:05:58', '2021-02-05 15:08:04', 2, 2, 5, '[phone]', 'Владимир Иванович 10.02 Краснодарский край, ст-ца Полтавская, ул Красная, д 123', 'Обещает забрать', '#008CFFFF', 3, '2021-02-05 18:08:04', '5e395258-dddb-4b18-80c8-21a6d0fc2bff', 1, 9);
INSERT INTO `contacts_history` VALUES (13, 13, 35, '2021-02-05 15:10:21', '2021-02-05 15:11:23', '2021-02-05 15:10:33', '2021-02-05 15:11:23', 2, 2, 5, '[phone]', '8 курьер ', 'Обещает забрать', '#008CFFFF', 3, '2021-02-05 18:11:22', '6933293e-c4e8-461e-a0d0-2803257958c3', 1, 9);
INSERT INTO `contacts_history` VALUES (9, 13, 37, '2021-02-05 15:12:40', '2021-02-05 15:14:18', '2021-02-05 15:14:17', '2021-02-05 15:14:17', 2, 2, 6, '[phone]', 'перезвонить ', 'Текущий недозвон', '#808080FF', 3, '2021-02-05 18:14:17', '6b9f9b01-9bb3-4fe0-89ae-109467db6f06', 1, 9);
INSERT INTO `contacts_history` VALUES (14, 13, 27, '2021-02-18 10:43:33', '2021-02-18 10:43:40', '2021-02-18 10:43:40', '2021-02-18 10:43:40', 2, 2, 6, '[phone]', '', 'Недозвон', '#808080FF', 3, '2021-02-18 13:43:40', '9000b39a-70ef-41cb-9819-84685d61f699', 1, 9);
INSERT INTO `contacts_history` VALUES (11, 13, 27, '2021-02-18 10:44:06', '2021-02-18 10:44:09', '2021-02-18 10:44:09', '2021-02-18 10:44:09', 2, 2, 6, '[phone]', '', 'Недозвон', '#808080FF', 3, '2021-02-18 13:44:09', 'e58d722a-b916-455a-80a5-5fbb84600ca1', 1, 9);
INSERT INTO `contacts_history` VALUES (15, 13, 37, '2021-02-20 09:54:13', '2021-02-20 09:54:48', '2021-02-20 09:54:49', '2021-02-20 09:54:49', 2, 2, 6, '[phone]', '', 'Текущий недозвон', '#808080FF', 3, '2021-02-20 12:54:49', 'dc16deb9-0498-4677-925f-54026fc5d6da', 1, 9);
INSERT INTO `contacts_history` VALUES (15, 13, 35, '2021-02-20 09:53:45', '2021-02-20 09:55:36', '2021-02-20 09:54:03', '2021-02-20 09:55:36', 2, 2, 5, '[phone]', '23 в банк на мира, 47', 'Обещает забрать', '#008CFFFF', 3, '2021-02-20 12:55:37', '674cac3a-0ce7-4a05-98d5-f466ac2c1875', 1, 9);
INSERT INTO `contacts_history` VALUES (17, 13, 41, '2021-02-20 10:13:34', '2021-02-20 10:15:17', '2021-02-20 10:13:47', '2021-02-20 10:15:17', 2, 2, 5, '[phone]', '', 'Отказ клиента', '#C20505FF', 3, '2021-02-20 13:15:18', '151e46b8-c6d8-4181-ae79-52bd2fb38308', 1, 9);
INSERT INTO `contacts_history` VALUES (13, 13, 34, '2021-02-20 10:32:44', '2021-02-20 10:33:50', '2021-02-20 10:32:54', '2021-02-20 10:33:50', 2, 2, 5, '[phone]', '8 получила с лимитом ', 'Получил. НЕ активировал', '#00FF4FFF', 3, '2021-02-20 13:33:50', '694aba45-5470-47c3-b871-e19624163327', 1, 9);
INSERT INTO `contacts_history` VALUES (18, 13, 37, '2021-02-20 10:39:58', '2021-02-20 10:40:06', '2021-02-20 10:40:06', '2021-02-20 10:40:06', 2, 2, 6, '[phone]', '', 'Текущий недозвон', '#808080FF', 3, '2021-02-20 13:40:06', 'e917a82a-883f-403c-af64-ded691ff288e', 1, 9);
INSERT INTO `contacts_history` VALUES (19, 13, 41, '2021-02-20 10:40:26', '2021-02-20 10:41:29', '2021-02-20 10:40:42', '2021-02-20 10:41:29', 2, 2, 5, '[phone]', 'передумал негативный возр.отраб', 'Отказ клиента', '#C20505FF', 3, '2021-02-20 13:41:29', '071d29b4-c220-4eb3-86ef-1b706a2b6144', 1, 9);
INSERT INTO `contacts_history` VALUES (20, 13, 37, '2021-02-20 10:42:29', '2021-02-20 10:44:08', '2021-02-20 10:44:09', '2021-02-20 10:44:09', 2, 1, 20, '[phone]', '', 'Текущий недозвон', '#808080FF', 3, '2021-02-20 13:44:09', 'ea8b153e-a7a3-4dc8-944d-23d5abad28db', 1, 9);

Если простыми словами, мне нужно выбрать только САМЫЕ Последние, самые новые записи, даже если я фильтрую по статусу, который старше.

Тупость написал. ))

Comments

[Иван Шумов]

В нормальных условиях для этого готовят отдельную таблицу в которую вносят именно эту информацию на основе внутренней бизнес-логики приложения транзакцией

[Игорь]

Иван Шумов, К этому склоняюсь, точнее даже реализовал и это всё даже работало.

То-есть, я тупо за контактом закреплял последний статус и легко мог фильтровать. Но появилась проблема.
Бизнес логика подразумевает передачу контакта между пользователями с обнулением статуса.
Фактически предыдущий оператор после передачи контакта другому, лишался доступа к последнему статусу.

Как итого видел искажённую статистику.

Потом, я подумал, что на каждом шаге после передачи контакта, за контактом будет закрепляться последний статус + user_id.

[Игорь]

[Иван Шумов]

Игорь, стоит пересмотреть предметную область. Данные перемешаться не могут. Могут изменять права на доступ к данным. Возможно сущности перенасыщены двойственностью или ещё что-то в этом духе

[Игорь]

Но мне кажется я переусложню.
Я уже даже подумал о том, что можно было бы хранить в JSON ссылки на статусы, но это плохая идея.

[Игорь]

Ну что-то типа такого замутил.
НО это как-то сыро да и результат не тот который я ожидал.

[Игорь]

Визуально представлю
Так, может будет яснее...



Всё хорошо, и на изображении именно последние звонки, но стоит мне отфильтровать по статусу, так сразу получу не верный результат.

Должен получить только последние либо не чего.

Короче думаю через таблицу попытаться ещё раз сделать.

[Akina]

мне нужно выбрать все последние записи с определённым (ВНИМАНИЕ) последним статусом.

Надо ли это понимать так: получить самый последний статус, затем вернуть все записи с таким статусом?

Покажите требуемый результат запроса для приведённых данных, ну и поясните, почему именно так.

Прилагаю схему

Column count doesn't match value count at row 1

[Игорь]

Иван Шумов, Потыкался, помыкался и всё же нужно в отдельной таблице хранить.

Answer 1

[Rsa97]

Для MySQL 8

WITH `cte` AS (
  SELECT *,
         ROW_NUMBER() OVER (
           PARTITION BY `contact_id` ORDER BY `created_at` DESC
         ) AS `row`
    FROM `contacts_history`
    WHERE `created_at` BETWEEN '2021-02-05 15:56:57' AND '2021-02-20 13:44:09'
) SELECT *
  FROM `cte`
  WHERE `status_id` = 37
    AND `row` = 1

Только учитывайте, что это будут последние записи в заданном интервале. Если статус контакта менялся позже, то это не будет отражено в запросе.

Comments

[Игорь]

. Если статус контакта менялся позже, то это не будет отражено в запросе.

Плохая новость.

[Игорь]

В общем я разобрался как это работает.
Немного почитал про данный механизм.

Это конечно не решает мою проблему и дело не в том что запрос неверно работает, а как раз наоборот.

В общем спасибо, буду применять в других работах.