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.

• GrumpyCrouton: 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.
• Sᴀᴍ Onᴇᴌᴀ: 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.