1
\$\begingroup\$

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.

\$\endgroup\$

1 Answer 1

1
\$\begingroup\$

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.

\$\endgroup\$
2
  • \$\begingroup\$ 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. \$\endgroup\$ May 29, 2020 at 19:57
  • \$\begingroup\$ 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. \$\endgroup\$ May 29, 2020 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.