SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `products` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(250) NOT NULL, `description` text, `price` decimal(10,2) NOT NULL, `img` varchar(250) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb3; CREATE TABLE `categories` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `title_for_get` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb3; CREATE TABLE `product_category` ( `product_id` int NOT NULL, `category_id` int NOT NULL, KEY `product_id` (`product_id`), KEY `category_id` (`category_id`), CONSTRAINT `product_category_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `product_category_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; CREATE TABLE `filters` ( `id` int NOT NULL AUTO_INCREMENT, `title` varchar(255) NOT NULL, `input_name` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb3; CREATE TABLE `product_filter` ( `product_id` int NOT NULL, `filter_id` int NOT NULL, KEY `product_id` (`product_id`), KEY `filter_id` (`filter_id`), CONSTRAINT `product_filter_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`), CONSTRAINT `product_filter_ibfk_2` FOREIGN KEY (`filter_id`) REFERENCES `filters` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3; SELECT p.*, category_titles, filter_titles FROM products as p LEFT JOIN ( SELECT product_id, json_arrayagg(c.title) category_titles from product_category as pc JOIN categories as c ON pc.category_id=c.id GROUP BY product_id ) pc ON p.id=pc.product_id JOIN ( SELECT product_id, json_arrayagg(f.title) filter_titles FROM product_filter as pf JOIN filters as f ON pf.filter_id=f.id GROUP BY product_id ) pf ON p.id=pf.product_id WHERE p.id='2'