返回一个对象数组的一个数组的一个数组 [英] Return an array of array of arrays of objects
问题描述
我有一些分层数据，因此创建了它:
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.
这是一个整洁的树层次结构，我想将其返回以响应AJAX请求，因此我有三个嵌套的for循环，每个循环都发出SELECT语句(coed不可用，因为它在家里，我现在在办公室，但我敢肯定您可以将其形象化；这很简单).
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.
但是，仅在深度级别上显示，因此带有while ($row = $stmt->fetch(PDO::FETCH_OBJ))的单个SELECT足以构建返回值-对象数组.
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
[更新]给某人一个简单的50分.
[Update] Here's an easy 50 points for someone.
@YeldarKurmangaliyev的回答完成了90％，但是我只需要对两个小问题有所启发.
@YeldarKurmangaliyev 's answer is 90% complete, but I just need some enlightenment on two small points.
-
他的SQL未显示与
departments关联的数据.我怀疑我只需要(INNER?) JOIN departments.*.但是确切的SQL命令是什么?
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?
什么是PHP代码?我怀疑$result = $sqlQuery->fetchAll(PDO::FETCH_ASSOC);或类似的
what's the PHP code? I suspect $result = $sqlQuery->fetchAll(PDO::FETCH_ASSOC); or similar
顺便说一句，有 SQL的小提琴
我尽力而为，只返回一个平面数组，而不是嵌套树，如发布的答案所示:-(
My best efforts only return a flat array, not a nested tree as the posted answer shows :-(
[更新]非常感谢您的答复.为了帮助他人，我在 http://phpfiddle.org/main上发布了一个有用的小提琴/code/xfdj-wthc
[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
请注意，该解决方案支持多个外键，而我只有一个.我可以简化代码以供个人使用，但是感谢@trincot使其具有很高的灵活性，因为它可能对其他人有用.
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.
推荐答案
下面是一些代码，这些代码从单独的数组中的每个表中检索数据，然后从中构建最终的数据结构.
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);
输出测试数据:
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
)
)
)
)
)
)
然后您可以继续进行json_encode($departments)等.
You could then proceed with json_encode($departments), etc.
这篇关于返回一个对象数组的一个数组的一个数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
