Retrieve joined table(s) in a nested array structure in codeigniter

Question

What I want to have is get records from database against a specific id and in that obtained record, I want to get further more records against a specific id from those gotten records which might be like a nested array structure. let me clarify with an example.

I have three tables, one is of orders, other is of items and the last one is for sub items.

The orders table store unique orders, items table store multiple items against a specific order id and sub items table store multiple sub items against a specific item_id and sub item table might or might not store anything depending on the requisites/need of the item.

My tables are as follows.

Orders tables :-

id	order_id	a	b	c
1	123456	xyz	xyz	xyz
2	413211	xyz	xyz	xyz

Items table :-

id	order_id	a	b	c
5	123456	xyz	xyz	xyz
7	123456	xyz	xyz	xyz
8	413211	xyz	xyz	xyz

Sub items table :-

id	order_id	item_id	b	c
1	123456	5	xyz	xyz
2	123456	5	xyz	xyz
3	123456	7	xyz	xyz
4	123456	7	xyz	xyz
5	123456	7	xyz	xyz
6	413211	8	xyz	xyz
7	413211	8	xyz	xyz

The item_id in sub items table is a foreign key that maps to the items in items table. Now I dont want to run 3 queries to get the job done, What i would like to have is a single query which gives me a nested array which has a something like the following structure.

array(
    order_id_1 => array(
        item_1 => array(
            sub_item_1 => array(
            ),
            sub_item_2 => array(
            ),
        ),
        item_2 => array(
            sub_item_1 => array(
            ),
            sub_item_2 => array(
            ),
            sub_item_3 => array(
)
        ),
    ),
    order_id_2 => array(
        item_3 => array(
            sub_item_1 => array(
            ),
            sub_item_2 => array(
            ),
        )
    )
)

Is something like this possible using a single query?

PS: The title might be unclear and there might be mistakes in asking question as this is my first question on stackoverflow, any edits and corrections would be highly appreciated.

Answer 1

With regard to combining the queries together, I think you could just left join all the tables together:

SELECT * FROM orders O
LEFT JOIN items I ON O.id = I.order_id
LEFT JOIN sub_items SI ON I.id = SI.item_id AND O.id = SI_order_id 

However, that won't return a nested array. You would have to write code to nest the array in PHP.

Some frameworks support this kind of query using ORM (it will return objects instead of arrays). For example, in Yii2 it's called eager loading. https://www.yiiframework.com/wiki/834/relational-query-lazy-loading-and-eager-loading-in-yii-2-0

Answer 2

Possible duplicate of this S.O question. You need to write few lines of script to tweak