1
\$\begingroup\$

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.

\$\endgroup\$
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

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.

| improve this answer | |
\$\endgroup\$
  • \$\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\$ – GrumpyCrouton May 29 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\$ – Sᴀᴍ Onᴇᴌᴀ May 29 at 20:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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