Reduce database touches for one to many data pull

Question

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->db->all("SELECT * FROM consumables_products");
    
    foreach($return as &$r) {
        $r['consumables'] = $this->db->all("SELECT A.*, B.description FROM consumables_product_consumables as A, consumables_consumables as B WHERE product_id=? AND B.uid=A.consum_id", [$r['uid']]);
    }
    
    return $return;
}

The result looks like this:

Array
(
    [0] => Array
        (
            [uid] => 1
            [description] => Test
            [consumables] => Array
                (
                    [0] => Array
                        (
                            [uid] => 1
                            [consum_id] => 186
                            [product_id] => 1
                            [amount] => 5
                            [description] => 3 X 1" GREEN Direct Thermal
                        )

                    [1] => Array
                        (
                            [uid] => 2
                            [consum_id] => 185
                            [product_id] => 1
                            [amount] => 1
                            [description] => 3 X 1" ORANGE Direct Thermal
                        )

                )

        )

)

This could get very inefficient with hundreds or thousands of rows in my consumables_products table, and an unknown 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.

Answer 1

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->db->all("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
");

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' => $row['productUid'],
            'description' => $row['productDesc'],
            'consumables' => []
        ];
    }  
    $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.