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.

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.
    ]
}

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.