Return an array of array of arrays of objects

Question

I have some hierarchical data, created its thus:

CREATE TABLE `departments`
 (`deperatment_id` INTEGER NOT NULL, 
 `department_name` varchar(32) NOT NULL);
 INSERT INTO `departments`(`deperatment_id`, `department_name`)
 VALUES (1, "HR"), (2, "Software"), (3, "Accounts");
CREATE TABLE `jobs` (`deperatment_id` INTEGER NOT NULL,
 `job_id` INTEGER NOT NULL,
 `job_name` varchar(32) NOT NULL);
INSERT INTO `jobs` (`deperatment_id`, `job_id`, `job_name`)
 VALUES (1, 1, "Idiot"),
 (1, 2, "Fool"),
 (2, 3, "PHB"),
 (2, 4, "Software guru"),
 (2, 5, "PFY"),
 (3, 6, "Number cruncher");
CREATE TABLE `peeps` (`job_id` INTEGER NOT NULL,
 `peep_name` varchar(32) NOT NULL);
INSERT INTO `peeps`(`job_id` , `peep_name` )
 VALUES(1, "Smith"),
 (2, "Jones Major"),
 (2, "Jones Minor"),
 (4, "Mr. In-the-wrong-department"),
 (4, "Mawg"),
 (5, "William Topaz McGonagall"),
 (6, "Blaise Pascal"),
 (6, "Isaac Newton");

So, as you can see, there can be one or more departments, each of which can have one or more jobs, done by one or more people.

It's a neat tree hierarchy and I want to return it in response to an AJAX request, so I have three nested for loops, each issuing a SELECT statement (coed unavailable, as it is at home & I am in the office, but I am sure that you can visualize it; it's straightforward enough).

I was having some problems client side, trying to add a new, blank entry, and it was suggested that I should be retiring an array of objects, as shown here.

However, that shows only on level of depth, so a single SELECT with while ($row = $stmt->fetch(PDO::FETCH_OBJ)) was enough to build the return value - an array of objects.

How do I build my return value, which will be a nested array of array of arrays of objects?

---

[Update] I have removed the previous reference to a fiddle, as it confused at least one person.

I want to return an array of departments, each containing data for that department, plus an array of jobs, each containing data for that job, plus an array of people who perform that job

---

[Update] Here's an easy 50 points for someone.

@YeldarKurmangaliyev 's answer is 90% complete, but I just need some enlightenment on two small points.

• his SQL doesn't show the data associated with departments. I suspect that I just need to (INNER?) JOIN departments.*. But what is the exact SQL command?

• what's the PHP code? I suspect $result = $sqlQuery->fetchAll(PDO::FETCH_ASSOC); or similar

Btw, there's a fiddle for the SQL

My best efforts only return a flat array, not a nested tree as the posted answer shows :-(

---

[Update] Thanks for the great answer. In order to help others, I have posted a working fiddle at http://phpfiddle.org/main/code/xfdj-wthc

Note that the solution supports multiple foreign keys, where I only have one. I could simplify the code for personal use, but thank @trincot for making it so flexible as this might be of use to others.

• David Demetradze: I can't understand what's problem here. If you need to return full data, it's possible by selecting departments, than for each department selecting jobs and for each job selecting people. After that you json_encode it and return as string to JavaScript Ajax function.
• Mawg: Whooops!! just SELECT * FROM departments? Do you know, that sounds correct!! Sorry, it's 7am over here and I had a 2.5 hour drive to work. I must still be asleep. Feel free to post that as an answer, in light of my update to the question.
• trincot: @DDeme, that would work, but with larger data sets this would result in many query executions (for each job in each department you would execute a query to retrieve the peeps). I think it is more efficient to get all the data with one query and then build the output structure from that.
• Mawg: Can you show me how? Please post an answer. Pleeeeaaaase
• David Demetradze: @Mawg If you are interested I will write that for you tomorrow, and if will have some time, will try to write like trincot suggested, in addition to my solution

Answer 1

If you want to output the tree structure, then you don't need to use loops. You can simply make the following query:

SELECT p.`peep_name`, j.* FROM `peeps` p INNER JOIN `jobs` j ON j.job_id = p.job_id

which will return a structure like:

peep_name deperatment_id job_id job_name
Smith 1 1 Idiot
Jones Major 1 2 Fool
Jones Minor 1 2 Fool
Mr. In-the-wrong-department 2 4 Software guru
Mawg 2 4 Software guru
William Topaz McGonagall 2 5 PFY
Blaise Pascal 3 6 Number cruncher
Isaac Newton 3 6 Number cruncher

Then, you will be able to output this data in this format or combine this data into a tree structure of desirable format. For example, you can output it in the following format:

{
 "departments": 
 [
 {
 "ID": 1,
 "jobs": 
 [ 
 { 
 "ID": 1,
 "Name": "Idiot"
 "Peeps": 
 [
 "Smith"
 ]
 },
 {
 "ID": 2,
 "Name": "Fool"
 "Peeps": 
 [
 "Jones Major",
 "Jones Minor"
 ]
 }
 ]
 },
 // etc.
 ]
}

• Mawg: Please accept my apologies. That fiddle was misleading. I only meant to show the data and had forgotten about the SELECT there, which is not relevant to this question. My bad :-(
• Mawg: And I return that to my client how? DO I use while ($row = $stmt->fetch(PDO::FETCH_OBJ)) ?
• Mawg: Alas, that does not show the data about the departments (name and Id)
• Mawg: How do I get that structure, including the data of the departments? What's the SQL and PHP?

Answer 2

Here is some code that retrieves the data from each table in separate arrays, and then builds the final data structure from it.

It is quite generic, as you only have to specify which fields are the common keys (primary and foreign keys, which are assumed to have the same name) for each pair of tables:

function loadTable($dbh, $table) {
 // Perform simple table select, and return result set
 $sth = $dbh->prepare("SELECT * FROM $table");
 $sth->execute();
 $rows = $sth->fetchAll(PDO::FETCH_ASSOC);
 return $rows;
}
function connectChildren($parents, $children, $name, $common_keys) {
 /* Returns $parents array, but with each element extended with
 * a $name key, which is an array of matching $children elements.
 * The match is made by comparing the values for each of the $common_keys 
 * in both arrays.
 * When a $children element is added to the $name array, its $common_keys
 * are removed from it as they are already known in the $parents element.
 * (this removal behaviour is optional and could be left out)
 */ 
 $index = [];
 // Build a temporary index to associate $parents elements by their 
 // primary key value (can be composite) 
 foreach ($parents as $i => $parent) {
 $primary_key = [];
 foreach ($common_keys as $common_key) {
 $primary_key[] = $parent[$common_key];
 }
 $index[implode("|", $primary_key)] = $i;
 $parents[$i][$name] = [];
 }
 // Main algorithm: inject $children into $parents
 foreach($children as $child) {
 $foreign_key = [];
 // Collect foreign key value
 foreach ($common_keys as $common_key) {
 $foreign_key[] = $child[$common_key];
 // Remove foreign key from child
 unset($child[$common_key]);
 }
 // Find the corresponding $parents element via the index
 $i = $index[implode("|", $foreign_key)];
 $parents[$i][$name][] = $child;
 }
 return $parents;
}
// Step 1: load all the table data 
$rows_dep = loadTable($dbh, "departments");
$rows_job = loadTable($dbh, "jobs");
$rows_peep = loadTable($dbh, "peeps");
// Step 2: connect the data, layer by layer, in bottom-up order: 
$rows_job = connectChildren($rows_job, $rows_peep,
 "peeps", ["deperatment_id", "job_id"]);
$rows_dep = connectChildren($rows_dep, $rows_job,
 "jobs", ["deperatment_id"]);
print_r ($rows_dep);

Output on the test data:

Array
(
 [0] => Array
 (
 [deperatment_id] => 1
 [department_name] => HR
 [jobs] => Array
 (
 [0] => Array
 (
 [job_id] => 1
 [job_name] => Idiot
 [peeps] => Array
 (
 [0] => Array
 (
 [peep_name] => Smith
 )
 )
 )
 [1] => Array
 (
 [job_id] => 2
 [job_name] => Fool
 [peeps] => Array
 (
 [0] => Array
 (
 [peep_name] => Jones Major
 )
 [1] => Array
 (
 [peep_name] => Jones Minor
 )
 )
 )
 )
 )
 [1] => Array
 (
 [deperatment_id] => 2
 [department_name] => Software
 [jobs] => Array
 (
 [0] => Array
 (
 [job_id] => 4
 [job_name] => Software guru
 [peeps] => Array
 (
 [0] => Array
 (
 [peep_name] => Mr. In-the-wrong-department
 )
 [1] => Array
 (
 [peep_name] => Mawg
 )
 )
 )
 [1] => Array
 (
 [job_id] => 5
 [job_name] => PFY
 [peeps] => Array
 (
 [0] => Array
 (
 [peep_name] => William Topaz McGonagall
 )
 )
 )
 )
 )
 [2] => Array
 (
 [deperatment_id] => 3
 [department_name] => Accounts
 [jobs] => Array
 (
 [0] => Array
 (
 [job_id] => 6
 [job_name] => Number cruncher
 [peeps] => Array
 (
 [0] => Array
 (
 [peep_name] => Blaise Pascal
 )
 [1] => Array
 (
 [peep_name] => Isaac Newton
 )
 )
 )
 )
 )
)

You could then proceed with json_encode($departments), etc.

• Mawg: Thais looks great! However, can it be made generic? Obviously, there are foreign keys (department_id and job_id), but I would like to be able to add more columns to the tables without updating the code. Also, I am curious as to why you need the loops while@Yeldar did not. However, I just want it working.
• trincot: I updated my answer completely, now with a more generic solution. I didn't see any PHP code from Yeldar, so I am not sure we can say he could do it without loops.
• Mawg: Thanks VERY much. I have posted a working fiddle at phpfiddle.org/main/code/xfdj-wthc