Reduce database touches for one to many data pull I have the following function which works properly and returns all of the rows from a specific table, then loops through each row to add data related to it as a subarray.
public function fetch_products() {
    $return = $this-&gt;db-&gt;all(&quot;SELECT * FROM consumables_products&quot;);
    
    foreach($return as &amp;$r) {
        $r['consumables'] = $this-&gt;db-&gt;all(&quot;SELECT A.*, B.description FROM consumables_product_consumables as A, consumables_consumables as B WHERE product_id=? AND B.uid=A.consum_id&quot;, [$r['uid']]);
    }
    
    return $return;
}

The result looks like this:
Array
(
    [0] =&gt; Array
        (
            [uid] =&gt; 1
            [description] =&gt; Test
            [consumables] =&gt; Array
                (
                    [0] =&gt; Array
                        (
                            [uid] =&gt; 1
                            [consum_id] =&gt; 186
                            [product_id] =&gt; 1
                            [amount] =&gt; 5
                            [description] =&gt; 3 X 1&quot; GREEN Direct Thermal
                        )

                    [1] =&gt; Array
                        (
                            [uid] =&gt; 2
                            [consum_id] =&gt; 185
                            [product_id] =&gt; 1
                            [amount] =&gt; 1
                            [description] =&gt; 3 X 1&quot; ORANGE Direct Thermal
                        )

                )

        )

)

This could get very inefficient with hundreds or thousands of rows in my consumables_products table, and an undefined number of rows in the consumables_product_consumables table.
I'm wondering if there is a way that I can do this in a single query, so that I don't have to do a separate call for every row in my consumables_products table.
A single query could be used to get all results. This would require formatting the results differently...
The table consumables_products could be added as a (n INNER) JOIN.
JOIN consumables_products P on P.uid = A.product_id --or is it B. product_id instead of A.uid??

The implicit join on consumables_consumables could also be written in a similar fashion.
$results = $this-&gt;db-&gt;all(&quot;SELECT P.uid as productUid, P.description as productDesc, A.*, B.description 
    FROM consumables_product_consumables as A
    JOIN consumables_consumables as B ON B.uid = A.consum_id
    JOIN consumables_products P on P.uid = A.product_id
&quot;);

From there array_reduce() (or else a foreach loop) could be used to return the data in the specified format:
Note this is untested code...
return array_values(array_reduce(function($carry, $row) {
    $key = $row['productUid'];
    if (!isset($carry[$key])) {
        $carry[$key] = [
            'uid' =&gt; $row['productUid'],
            'description' =&gt; $row['productDesc'],
            'consumables' =&gt; []
        ];
    }  
    $carry[$key]['consumables'][] = array_diff_key($row, array_flip(['productUid', 'productDesc']));
    return $carry;
}, []));

That array_flip(['productUid', 'productDesc']) could be stored in a variable outside the loop though it would need to be brought in using a use statement or if PHP 7.4+ is used then an arrow function could be used, which would allow use of that variable without the need for a use statement.
e.g.
$keysToUnset = array_flip(['productUid', 'productDesc']);
return array_values(array_reduce(function($carry, $row)  {
    ...
    $carry[$key]['consumables'][] = array_diff_key($row, $keysToUnset);
    return $carry;
}, []));


Also consider if all fields from consumables_product_consumables are needed. If not, select only the fields needed in order to minimized the returned data set.