Как создать SQL запрос с вложенными данными?

Question

[Dvoeglazov]

Задача: вывести в цикле данные каждой яхты (рис). Проблема с получением подмассива двигателей.



Как с помощью sql запроса получить массив данных, и включить в него подмассив engin, т.е. получить следующую структуру:

spoiler

Array
(
[0] => Array
(
[id] => 1
[name] => SL78
[width] => 5,74 м
[engin] => Array(
[0]=>2 x MTU10V2000M96 - 1523HP,
[1]=>2 x MTU10V2000M96 - 1523HP)
)
... и т.д.
)

База данных Mysql:

spoiler

CREATE TABLE `sl` (
  `id` int(11) NOT NULL,
  `order_yacht` int(11) NOT NULL,
  `name` varchar(100) NOT NULL,
  `img` varchar(255) NOT NULL,
  `length` varchar(100) NOT NULL,
  `width` varchar(100) NOT NULL,
  `draft` varchar(100) NOT NULL,
  `guests` varchar(100) NOT NULL,
  `command` varchar(100) NOT NULL,
  `fuel` varchar(100) NOT NULL,
  `max_speed` varchar(100) NOT NULL,
  `flag` tinyint(4) NOT NULL DEFAULT '0',
  `premiere` tinyint(4) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sl` (`id`, `order_yacht`, `name`, `img`, `length`, `width`, `draft`, `guests`, `command`, `fuel`, `max_speed`, `flag`, `premiere`) VALUES
(1, 1, 'SL78', 'sl-1.jpg', '24,64 м', '5,74 м', '1,80 м', '8', '2', '5,350 л', '28 -30 узлов', 1, 0),
(2, 2, 'SL86', 'sl-2.jpg', '26,76 м', '6,35 м', '1,90 м', '8', '3', '8,100 л', '30 -32 узла ', 1, 0),
(3, 4, 'SL106', 'sl-3.jpg', '32,2 м', '7,05 м', '2,00 м', '8', '4/5', '8,300 л', '28 - 30 узлов', 0, 0),
(4, 5, 'SL118', 'sl-4.jpg', '36,50 м', '7,60 м', '2,25 м', '10', '5', '13,700 л', '27 узлов', 1, 0),
(5, 3, 'SL102', 'sl-5.jpg', '31,10 м', '7,10 м', '2 м', '-', '-', '12,100 л', '28 узлов', 1, 1);


CREATE TABLE `engin_sl` (
  `id_eng` int(11) NOT NULL,
  `engin_name` text NOT NULL,
  `yacht_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `engin_sl` (`id_eng`, `engin_name`, `yacht_id`) VALUES
(1, '2 x MTU10V2000M96 - 1523HP', 1),
(2, '2 x MTU10V2000M96L - 1622HP', 1),
(3, '2 x MTU 12V2000 M96 - 1822 HP ', 2),
(4, '2 x MTU 12V2000 M96L - 1947 HP', 2),
(5, '2 x MTU 16V2000M96 - 2434 HP ', 3),
(6, '2 x MTU 16V2000M96L - 2637 HP', 3),
(7, '2 x MTU 16V 2000 M96L ', 4),
(8, '2 x MTU 16V2000 M86 – 2216 HP', 5);

Проблема заключается в том, что я не могу (не знаю как) добавить в [engin] массив двигателей, это возможно сделать с помощью sql? Если нет посоветуйте как решить данную задачу

Answer 1

[Anton fon Faust]

Mysql не позволяет хранить древовидные структуры.
Скорее всего тут у вас надо будет хранить движки в отдельной таблице и вспомогательную таблицу отношений многие ко многим, а потом делаете что-то вроде такого:
Select * from boats limit 10; - выбираете лодки.
Дальше эти id лодок собираете в одну строку и
Select * from engine_to_boats as etb left join engine as e on etb.engine_id = e.id where etb.boat_id in 2,3,4,5,6,7,8,9;
И дальше уже, когда будете отрисоывать, прописываете там свои двигатели.

Можно, конечно, использовать ajax-поле с данными по движкам, но это не есть гуд, так как приводит к денормализации базы и с таким же успехом лучше использовать обычный текст.