How to create SQL query with nested data?

0 like 0 dislike
20 views
The goal is to get in the cycle data of each vessel (figure). The problem with getting the subarray engines.

5b83f4da0ae3b625122051.png

How to use sql query to obtain array data, and to include the subarray engin, i.e., to obtain the following structure:

spoiler
Array
(
[0] => Array
(
[id] => 1
[name] => SL78
[width] => 5,74 m
[engin] => Array(
[0]=>2 x MTU10V2000M96 - 1523HP,
[1]=>2 x MTU10V2000M96 - 1523HP)
)
... etc.
)
by | 20 views

1 Answer

0 like 0 dislike
Mysql does not allow to store the tree structure.
Most likely then you will have to store the engines in a separate table and auxiliary table relationship many-to-many, and then do something like this:
Select * from boats limit 10; - choose the boat.
Further, these id boats gather in one line
Select * from engine_to_boats as etb as a left join engine e on etb.engine_id = e.id where etb.boat_id in 2,3,4,5,6,7,8,9;
And then, when you otrisovat, it prescribes their engines.

Of course, you can use ajax data field on the engines, but this is not good as it leads to denormalization of the database and with the same success it is better to use plain text.
by

Related questions

0 like 0 dislike
1 answer
0 like 0 dislike
1 answer
0 like 0 dislike
3 answers
0 like 0 dislike
3 answers
asked May 22, 2019 by Dvoeglazov
0 like 0 dislike
2 answers
asked Apr 4, 2019 by AronTito
110,608 questions
257,186 answers
0 comments
32,723 users