SQLize | PHPize | SQLtest

CREATE TABLE `adverts` ( `id` int(11) UNSIGNED NOT NULL, `client_id` int(9) UNSIGNED NOT NULL, `ad_type` varchar(255) NOT NULL DEFAULT 'sell', `title` varchar(255) NOT NULL, `brand` varchar(255) NOT NULL, `model` varchar(255) NOT NULL, `carcas` varchar(255) DEFAULT NULL, `production_year` int(4) UNSIGNED NOT NULL, `mileage` varchar(255) NOT NULL DEFAULT '0', `gear` varchar(255) DEFAULT NULL, `color` varchar(255) NOT NULL, `engine_volume` varchar(255) DEFAULT NULL, `fuel` varchar(255) DEFAULT NULL, `condition` varchar(255) NOT NULL, `price_sell` varchar(255) DEFAULT NULL, `price_prepayment` varchar(255) DEFAULT NULL, `price_monthly` varchar(255) DEFAULT NULL, `rent_period` varchar(255) DEFAULT NULL, `is_price_negotiable` varchar(255) DEFAULT NULL, `location` varchar(255) NOT NULL, `hashtags` varchar(255) NOT NULL, `comment` varchar(255) DEFAULT NULL, `lang` varchar(255) NOT NULL DEFAULT 'ru', `tg_image_ids` text, `tg_message_object` text, `ad_id` int(9) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `adverts` (`id`, `client_id`, `ad_type`, `title`, `brand`, `model`, `carcas`, `production_year`, `mileage`, `gear`, `color`, `engine_volume`, `fuel`, `condition`, `price_sell`, `price_prepayment`, `price_monthly`, `rent_period`, `is_price_negotiable`, `location`, `hashtags`, `comment`, `lang`, `tg_image_ids`, `tg_message_object`, `ad_id`) VALUES (1, 5, 'sell', 'Advertisement 1', 'Mercedes', 'C-180', 'Хэтчбек', 2020, '4646', 'Вариатор', 'Мокрый асфальт', '234', 'Газ', 'Хорошее', '2222', NULL, NULL, NULL, 'Без скидки', 'City', '#продаётся #mercedes #c-180 #мокрыйасфальт #city', '234', 'ru', 'AgACAgIAAxkBAAIoC2IGraYCoP0vrQdfgEDeRnjVHWwyB56AALTuDEbVEMxSEsfciNuWD2yAQADAgADeAADIwQ;AgACAgIAAxkBAAIoCmIGraZtk_Bc0pT1L6x1hMdWouAVAALRuDEbVEMxSKffNMwIFG7IAQADAgADeAADIwQ;AgACAgIAAxkBAAIoDGIGraZwfn0fYNBpl56LlwHMPNcyAALbuDEbVEMxSLGgWZ3WsoMwAQADAgADeQADIwQ;', NULL, 1), (3, 5, 'sell', 'Advertisement 2', 'Chevrolet', 'Gentra', 'Универсал', 2012, '545788', 'Механика', 'Стальной', 'Gvuvuvu', 'Газ', 'Среднее', '45678', NULL, NULL, NULL, 'Скидка есть!', 'City', '#продаётся #chevrolet #gentra #стальной #city', 'Без комментариев', 'ru', 'AgACAgIAAxkBAAIohmIGsKH5a6dfgT-a9kf4AMUuAPFD59mAAIUujEbypQ4SF2C6jox0dfgOQHAQADAgADeQADIwQ;AgACAgIAAxkBAAIoh2IGsKHfKnBY2Z_buv9Yj-o4_fpyAAIVujEbypQ4SB397PsZAAGAdgEAAwIAA3kAAyME;AgACAgIAAxkBAAIoiGIGsKFEtbwIVOCE0DK6SZpyTrWgAAIWujEbypQ4SGkfVhqvLkssAQADAgADeQADIwQ;', NULL, 2), (4, 5, 'sell', 'Advertisement 3', 'Mercedesxcv', 'C-180xcv', 'Хэтчбекxcv', 2024, '4646xcv', 'Вариаторxcv', 'Мокрый асфальт', '234', 'Газ', 'Хорошее', '2222sdf', NULL, NULL, NULL, 'Без скидки', 'City', '#продаётся #mercedes #c-180 #мокрыйасфальт #city', '234', 'ru', 'AgACAgIAAxkBAAIoCdfg2IGraYCoP0vrQEDeRnjVHWwyB56AALTuDEbVEMxSEsdfgfciNuWD2yAQADAgADeAADIwQ;AgACAgIAAxkBAAIoCmIGraZtk_Bc0pT1L6x1hMdWouAVAALRuDEbVEMxSKffNMwIFG7IAQADAgADeAADIwQ;AgACAgIAAxkBAAIoDGIGraZwfn0fYNBpl56LlwHMPNcyAALbuDEbVEMxSLGgWZ3WsoMwAQADAgADeQADIwQ;', NULL, 3), (5, 5, 'sell', 'Advertisement 4', 'Daewoo', 'Tico', 'Кроссовер', 2012, '4567', 'Вариатор', 'Бирюзовый', 'Tghk', 'Бензин', 'Среднее', '4578', NULL, NULL, NULL, 'Без скидки', 'City', '#продаётся #daewoo #tico #бирюзовый #city', 'Без комментариев', 'ru', 'AgACAgIAAxkBAAIpO2IGvjOln790XPtxrUeIPvr5i_UlAAIdfgUujEbypQ4SF2C6jox0OQHAQADAgADeQADIwQ;AgACAgIdfgAAxkBAAIpPGIGvjNmUYr8Wb5yThWwfOSqDX1tAAIVujEbypQ4SB397PsZAAGAdgEAAwIAA3kAAyME;AgACAgIAAxkBAAIpPWIGvjP2OjIe8T0Ofqdsv0uuVjY6AAIWujEbypQ4SGkfVhqvLkssAQADAgADeQADIwQ;', NULL, 4), (6, 5, 'sell', 'Advertisement 5', 'Chevrolet', 'Malibu', 'Минивен', 2012, '3775', 'Робот', 'Бирюзовый', 'Dutdhhdt', 'Бензин', 'Отличное', '4567', NULL, NULL, NULL, 'Скидка есть!', 'City', '#продаётся #chevrolet #malibu #бирюзовый #city', 'Без комментариев', 'ru', 'AgACAgIAAxkBAAIpdfgiWIGwzdO4URdw93VdYz8UUGIUYCrAAIVujEbypQ4SB397PsZAAGAddfggEAAwIAA3kAAyME;AgACAgIAAxkBAAIpiGIGwzf18mXD64jlpz9bgJLKEC7zAAIUujEbypQ4SF2C6jox0OQHAQADAgADeQADIwQ;AgACAgIAAxkBAAIpimIGwzfKFZxlzuz72VQgZVhdUo_VAAIWujEbypQ4SGkfVhqvLkssAQADAgADeQADIwQ;', NULL, 5); CREATE TABLE `sys_advert_statuses` ( `id` int(9) UNSIGNED NOT NULL, `ad_id` int(9) UNSIGNED NOT NULL, `status_id` int(9) UNSIGNED NOT NULL, `admin_id` int(9) UNSIGNED DEFAULT NULL, `comment` varchar(255) DEFAULT NULL, `date` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sys_advert_statuses` (`id`, `ad_id`, `status_id`, `admin_id`, `comment`, `date`) VALUES (1, 1, 1, NULL, NULL, '2022-02-11 23:40:28'), (2, 1, 3, NULL, NULL, '2022-02-11 23:44:19'), (3, 3, 1, NULL, NULL, '2022-02-11 23:53:14'), (4, 3, 3, NULL, NULL, '2022-02-11 23:55:28'), (5, 4, 1, NULL, NULL, '2022-02-12 00:51:10'), (6, 4, 3, NULL, NULL, '2022-02-12 01:12:39'), (7, 4, 5, NULL, NULL, '2022-02-12 05:12:39'), (8, 4, 6, NULL, NULL, '2022-02-14 08:12:39'); CREATE TABLE `sys_advert_status_types` ( `id` int(9) UNSIGNED NOT NULL, `status` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sys_advert_status_types` (`id`, `status`) VALUES (1, 'CREATED_BY_USER_TELEGRAM'), (2, 'CREATED_BY_ADMIN'), (3, 'APPROVED_BY_ADMIN'), (4, 'DECLINED_BY_ADMIN'), (5, 'UPDATED_BY_USER_TELEGRAM'), (6, 'UPDATED_BY_ADMIN'), (7, 'REMOVED_BY_USER_TELEGRAM'), (8, 'REMOVED_BY_ADMIN'); WITH records AS ( SELECT a.title, s.date, t.status, ROW_NUMBER() OVER (PARTITION BY a.id ORDER BY s.date DESC) rn FROM adverts a LEFT JOIN sys_advert_statuses s ON a.id = s.ad_id LEFT JOIN sys_advert_status_types t ON s.status_id = t.id WHERE s.status_id IN (3, 5, 6) AND a.id = 4 ) SELECT title, date, status FROM records WHERE rn = 1;