Modify the structure of data returned by $wpdb

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?

Solutions Collecting From Web of "Modify the structure of data returned by $wpdb"

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.