Modify the structure of data returned by $wpdb

Question

Modify the structure of multidimensional array outputed by SQL via $wpdb

The output that I need from $wpdb is easily achievable by running multiple queries within foreach. However, I am trying to avoid the same for performance issues.

I have two custom tables:

items
- item_id
- item_name

sub_items
- sub_item_id
- item_id
- sub_item_name

The query I am running:

$items_tbl = $wpdb->prefix . 'items';
$sub_items_tbl = $wpdb->prefix . 'sub_items';
$results = $wpdb->get_results($wpdb->prepare("SELECT * from $sub_items_tbl LEFT JOIN $items_tbl ON $items_tbl.item_id = $sub_items_tbl.item_id"), ARRAY_A);

This is the resultant output of the above query:

array(
    [0] => array(
        ['sub_item_id'] => 1
        ['item_id'] => 1
        ['sub_item_name'] => 'Lorem Ipsum'
        ['item_name'] => Some Item Name
    )
    [1] => array(
        ['sub_item_id'] => 2
        ['item_id'] => 1
        ['sub_item_name'] => 'Lorem Ipsum Ornare Parturient'
        ['item_name'] => Some Item Name
    )
    [2] => array(
        ['sub_item_id'] => 3
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Adipiscing Malesuada'
        ['item_name'] => Some Item Name
    )
    [3] => array(
        ['sub_item_id'] => 4
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Ligula'
        ['item_name'] => Some Item Name
    )
    [4] => array(
        ['sub_item_id'] => 5
        ['item_id'] => 2
        ['sub_item_name'] => 'Lorem Ipsum Sit Adipiscing'
        ['item_name'] => Some Item Name
    )
)

And this is the output I need:

array(
    [0] => array(
        ['item_id'] => 1
        ['item_name'] => Some Item Name
        ['sub_items'] =>[0] => array(
                            ['sub_item_id'] => 1
                            ['sub_item_name'] => 'Lorem Ipsum'
                        )
                        [1] => array(
                            ['sub_item_id'] => 2
                            ['sub_item_name'] => 'Lorem Ipsum Ornare Parturient'
                        )
    )
    [1] => array(
        ['item_id'] => 2
        ['item_name'] => Some Item Name
        ['sub_items'] =>[0] => array(
                            ['sub_item_id'] => 3
                            ['sub_item_name'] => 'Lorem Ipsum Adipiscing Malesuada'
                        )
                        [1] => array(
                            ['sub_item_id'] => 4
                            ['sub_item_name'] => 'Lorem Ipsum Ligula'
                        )
                        [2] => array(
                            ['sub_item_id'] => 5
                            ['sub_item_name'] => 'Lorem Ipsum Sit Adipiscing'
                        )
    )
)

I'd preferably want to modify the sql to give me the resultant output. However, if that cannot be achieved then how can the output be altered in PHP?

Answer 1

You can do this one of two ways, as far as I can see:

1. Use PHP to combine the current results to the one you want. Basically:

   $records = array();

   foreach( $results as $result )
   {
       $record_id = $result['item_id'];
       if (empty($records[$record_id])) {
           $records[$record_id]['item_id'] = $result['item_id'];
           $records[$record_id]['item_name'] = $result['item_name'];
           $records[$record_id]['sub_items'] = array();
       }
       $records[$record_id]['sub_items'][] = array( 
          'sub_item_id' => $result['sub_item_id'],
          'sub_item_name' => $result['sub_item_name']  
       );
   }
   

2. Other way is doing 2 queries. first one finds the main items. One you have the item ids of the main items, you do a secondary query where you get all the sub-items whose item_id is in the ones you queries before. Then, you use PHP to put it together in nested arrays, similar to the first option.

As far as I can tell, you can't really do nested results in mySQL like you want, so you'll have to do the legwork in PHP.