Reduce database touches for one to many data pull

I have the following function which works properly and grabs 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 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->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

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.

• This doesn't seem to work. See, consumables_product_consumables can have 1+ rows associated with the UID from the first table. Your answer seems to only return one of these rows. – GrumpyCrouton May 29 at 19:57
• Oops I made a mistake with the JOIN condition for consumables_products - it should be either P.uid = A.product_id or P.uid = B.product_id, depending on where that product_id field is. Please see revised answer, which also has a shorter method for adding consumables to the list. – Sᴀᴍ Onᴇᴌᴀ May 29 at 20:47