How to add information from the DB to an existing array of objects?

Question

I have an array in the following format;

// echo '<pre>' . var_export($this->viewableFields, true) . '</pre>';

array (
  0 => 
  (object) array(
     'formId' => '4',
     'userId' => '7'
  ),
  1 => 
  (object) array(
     'formId' => '4',
     'userId' => '4'
  )
) 

I need to amend the data and add another key/value to this array. I need to use the userId value from the array, query a MySQL database and return the value. I need to do this for each array element.

So for each array element I want to run a query like;

SELECT group from users WHERE userId = [userId in array]

I then want to add this value to the array, the final array should look like this;

array (
  0 => 
  (object) array(
     'formId' => '4',
     'userId' => '7',
     'group' => 'Registered'
  ),
  1 => 
  (object) array(
     'formId' => '4',
     'userId' => '4',
     'group' => 'Admin'
  )
) 

I know I can add an additional value to the array elements by using array_walk, like this;

array_walk($this->viewableFields, function(&$arr) {
    $arr->group = 'Registered';
});

I'm not sure how to retrieve the values from the database though and insert into the existing array.

How can I achieve this?

Answer 1

Doing one additional query for each record is a very bad idea, and a clear example of the N+1 problem: where you need to get "N" children of an entity, and only the parent entity was fetched initially.

If your initial query has 100, users, you'll end making 101 queries. If you later need some other unrelated field, this starts to grow out of hand quickly.

This is very bad for performance, and as your application grows in complexity it can very easily have a very serious impact for it.

Assuming you can't modify the initial query and make a simple JOIN query to fetch the required data, it's better to simply make two queries and stitch the resulting data. Even if you can modify the original query this could end up performing better.

How to go about it:

Assuming you have your initial array of objects with a formId and userId public properties, first fetch all the user IDs you are interested in:

$index = [];
$ids = array_reduce($array, function($ids, $object) use (&$index) {
    $ids[]                  = $object->userId;
    $index[$object->userId] = $object;

    return $ids;
});

$ids_for_sql = '(' . implode(', ', $ids) . ')';

You are also building an "index" ($index) to be able to access the objects directly in the array without having to iterate it yet again.

Now you can make the second query you need to get your "group" property:

$groups_query = "SELECT userId, group from users WHERE userId IN $ids_for_sql";

Now it's simply a matter to of "stiching" the data from the two queries:

foreach ($conn->query($groups_query) as $row) {
    $index[$row['userId']]->group = $row['group'];
}

With this, now your original $array containing "user" objects will be correctly updated, and making only 2 queries instead of 11. This performs and scales better, and without adding significant complexity.

You can see a simple, working demo (with arrays instead of SQL queries) here.