MySQL-如何修改父项/子项选择查询以将更多子项添加到现有数组/JSON? [英] MySQL - How to modify parent/child select query to add more children to existing array/JSON?
问题描述
我有以下查询可以正常运行:
I have the following query working OK:
SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk
带有一些PHP的结果数组产生以下JSON，到目前为止，它是不错的，显示了主题"父母的子代"子代:
The resulting array, with some PHP, produces the following JSON, which is fine so far, showing the 'strand' children of 'theme' parents:
{
"name": "Condition",
"children": [{
"name": "Professional",
"children": [{
"name": "Professional Behavours"
}, {
"name": "Self-Care and Self-Awareness"
}, {
"name": "Medical Ethics and Law"
}]
}, {
"name": "Leader",
"children": [{
"name": "Teamwork and Leadership"
}, {
"name": "Collaborative Practice"
}, {
"name": "Health Systems and Careers"
}]
}, {
"name": "Advocate",
"children": [{
"name": "Health Advocacy"
}, {
"name": "Aboriginal Health"
}, {
"name": "Diversity and Inequality"
}, {
"name": "Health Promotion"
}]
}, {
"name": "Clinician",
"children": [{
"name": "Scientific Knowledge"
}, {
"name": "Patient Assessment and Clinical Reasoning"
}, {
"name": "Patient Management"
}, {
"name": "Patient Perspective"
}, {
"name": "Clinical Communication"
}, {
"name": "Quality Care"
}]
}, {
"name": "Educator",
"children": [{
"name": "Life-Long Learning"
}, {
"name": "Mentoring Relationships"
}, {
"name": "Patient Education"
}, {
"name": "Teaching and Learning"
}, {
"name": "Assessment and Evaluation"
}]
}, {
"name": "Scholar",
"children": [{
"name": "Research and Biostatistics"
}, {
"name": "Evidence-Based Practice"
}, {
"name": "Information Literacy"
}]
}]
}
我现在想将相同的子集:表strand.year中的'Year 1'，'Year 2'，'Year 3'和'Year 4'添加到每个strand.strand_name父级(例如，专业行为，医学道德与法律等).
I now want to add the same children set: 'Year 1', 'Year 2', 'Year 3' and 'Year 4', from table strand.year, to each strand.strand_name parent (e.g. Professional Behaviours, Medical Ethics and Law etc).
我尝试了以下修改后的查询:
I have tried the following modified query:
SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand, theme
UNION ALL
SELECT strand.year AS name, strand.strand_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk
但是，正如您在下面看到的，现在的关系是不完整的.前五个节点失去了孩子，只有一串信息素养拥有Year儿童.
But as you can see below, the relationships now are incomplete; the first five nodes have lost their children, and only one strand, Information Literacy, has the Year children.
{
"name": null,
"children": [{
"name": "Professional"
}, {
"name": "Leader"
}, {
"name": "Advocate"
}, {
"name": "Clinician"
}, {
"name": "Educator"
}, {
"name": "Scholar",
"children": [{
"name": "Professional Behavours"
}, {
"name": "Self-Care and Self-Awareness"
}, {
"name": "Teamwork and Leadership"
}, {
"name": "Collaborative Practice"
}, {
"name": "Health Systems and Careers"
}, {
"name": "Health Advocacy"
}, {
"name": "Aboriginal Health"
}, {
"name": "Diversity and Inequality"
}, {
"name": "Health Promotion"
}, {
"name": "Scientific Knowledge"
}, {
"name": "Patient Assessment and Clinical Reasoning"
}, {
"name": "Patient Management"
}, {
"name": "Patient Perspective"
}, {
"name": "Clinical Communication"
}, {
"name": "Quality Care"
}, {
"name": "Life-Long Learning"
}, {
"name": "Mentoring Relationships"
}, {
"name": "Patient Education"
}, {
"name": "Teaching and Learning"
}, {
"name": "Assessment and Evaluation"
}, {
"name": "Research and Biostatistics"
}, {
"name": "Evidence-Based Practice"
}, {
"name": "Information Literacy",
"children": [{
"name": "Year 1"
}, {
"name": "Year 2"
}, {
"name": "Year 3"
}, {
"name": "Year 4"
}]
}, {
"name": "Medical Ethics and Law"
}]
}]
}
应如何更改查询以显示与第一个JSON中相同的所有关系，并向每个链中添加四个"Year X"子级的集合?
How should the query be changed to show all the relationships as in the first JSON, and add the set of four 'Year X' children to each strand?
必需的JSON结果，直到Year Year子代为止(忽略Year x的子代
有关原始查询，请参见小提琴
See fiddle for original query
SQL:
用于JSON原始版本的有效PHP/MySQL是:
The working PHP/MySQL for the original version of the JSON is:
$condition = $_POST['condition'];
$query = "SELECT core_condition AS name, NULL AS parent
FROM condition_theme_lookup
UNION ALL
SELECT theme_name AS name, condition_theme_lookup.core_condition AS parent
FROM theme, condition_theme_lookup
UNION ALL
SELECT strand.strand_name AS name, theme.theme_name AS parent
FROM strand
JOIN theme ON theme.theme_pk = strand.theme_fk";
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
$data[$row->name] = $row;
}
foreach ($data as $row) {
if ($row->name == 'Condition') {
$row->name = $condition;
}
if ($row->parent === null) {
$roots[]= $row;
} else {
$data[$row->parent]->children[] = $row;
}
unset($row->parent);
}
$json = json_encode($roots);
推荐答案
正如我在其他答案中所写的:该名称在所有表中都应该是唯一的".这是一个基于您的
As I wrote in my other answer: "The name should be unique along all tables". That was an assumption based on sample data from your previous question. But it's not the case for the strand table. If a name occurs multiple times in the SQL result set, previous rows with same name will be overwritten here:
$data[$row->name] = $row;
因为$row->name具有相同的值.因此，您需要使用一列作为唯一标识符，并将该列用作$data数组的索引.您不能使用name，因为它在strand表中不是唯一的.而且您不能使用主键，因为它们在所有表中都不唯一.但是您可以结合使用表名(或唯一的表别名)和主键，例如
because $row->name has the same value. So you need a column as a unique identifier and use that column as index for the $data array. You can not use name because it's not unique in the strand table. And you can not use the primary keys, because they are not unique along all tables. But you can use a combination of table name (or a unique table alias) and primary key like
CONCAT('condition:', condition_theme_lookup_pk) AS global_id
...
CONCAT('theme:', theme_pk) AS global_id
....
CONCAT('strand:', strand_pk) AS global_id
parent列应具有相同的模式
CONCAT('theme:', theme_fk) AS parent_global_id
下一个问题是-如何按主题每年对绞线进行分组?嵌套逻辑不遵循模式parentTable <- childTable <- grandChildTable.那将是condition <- theme <- year <- strand.而是在一个表中有两个级别(年份和链名称).您需要使用DISTINCT查询从strand表中提取"年份，就像它们存储在单独的表中一样.唯一标识符应该是主题PK和年份的组合.各个股应在父列中引用这些标识符.最终查询将是
The next problem is - How to group the strands by year per theme? The nesting logic doesn't follow the pattern parentTable <- childTable <- grandChildTable. That would be condition <- theme <- year <- strand. Instead two levels (year and strand name) are in one table. You need to "extract" the years from the strand table with a DISTINCT query as if they were stored in a separate table. The unique identifier should be a combination of theme PK and year. The individual strands should reference those identifiers in the parent column. The final query would be like
SELECT CONCAT('condition:', condition_theme_lookup_pk) AS global_id,
core_condition AS name,
NULL AS parent_global_id
FROM condition_theme_lookup
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
theme_name AS name,
CONCAT('condition:', condition_theme_lookup_pk) AS parent_global_id
FROM theme CROSS JOIN condition_theme_lookup
UNION ALL
SELECT DISTINCT
CONCAT('theme:', theme_fk, ',year:', strand.year) AS global_id,
strand.year AS name,
CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT CONCAT('strand:', strand_pk) AS global_id,
strand.strand_name AS name,
CONCAT('theme:', theme_fk, ',year:', strand.year) AS parent_global_id
FROM strand
结果看起来像
global_id | name | parent_global_id
--------------------|------------------------------|---------------------
condition:1 | Condition | null
theme:1 | Professional | condition:1
theme:2 | Leader | condition:1
...
theme:1,year:Year 1 | Year 1 | theme:1
theme:2,year:Year 1 | Year 1 | theme:2
...
theme:1,year:Year 2 | Year 2 | theme:1
theme:2,year:Year 2 | Year 2 | theme:2
...
strand:1 | Professional Behavours | theme:1,year:Year 1
strand:2 | Self-Care and Self-Awareness | theme:1,year:Year 1
strand:3 | Teamwork and Leadership | theme:2,year:Year 1
strand:4 | Collaborative Practice | theme:2,year:Year 1
...
strand:27 | Teamwork and Leadership | theme:2,year:Year 2
您看到-团队合作和领导力"出现两次.但是这两行具有不同的global_id和不同的parent_global_id.您还可以看到parent_global_id如何明确引用父行的global_id.
You see - "Teamwork and Leadership" appears twice. But the two rows have different global_id and different parent_global_id. You can also see how parent_global_id unambiguously references the global_id of a parent row.
结果基本上是一个由不同表中的数据组成的邻接表.这些模式很容易转换成PHP中的嵌套结构. PHP代码几乎不需要更改即可调整为新列:
The result is basically an adjacency list composed from data in different tables. These schema is quite simple to transform into a nested structure in PHP. The PHP code needs little changes to be adjusted to the new columns:
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
$data[$row->global_id] = $row;
}
$roots = [];
foreach ($data as $row) {
if ($row->name == 'Condition') {
$row->name = $condition;
}
if ($row->parent_global_id === null) {
$roots[]= $row;
} else {
$data[$row->parent_global_id]->children[] = $row;
}
unset($row->parent_global_id);
unset($row->global_id);
}
$json = json_encode($roots);
注意:
- 结果将与您的链接中的结果不同.但是我不知道在没有数据的任何相关信息的情况下，一条钢绞线行(例如"Professional Behavours")如何成为其他钢绞线行的父级.
- 我用显式的
CROSS JOIN替换了您的逗号联接，这使意图更加清晰.这里的假设是condition_theme_lookup表中只有一行.否则，您将需要一个JOIN条件，这对于给定的架构是不可能的. - 您在评论中写道:最终的JSON中还将有几个子级别".所有级别都必须遵循相同的嵌套逻辑，或者至少是可转换的(例如年份).如果您有更多惊喜，则该解决方案可能不合适.在某个时候，我会考虑为每个级别执行一个查询，并构建自下而上"的层次结构(从叶子到根).
- The result will not be the same as in your link. But I don't know how a strand row (like "Professional Behavours") can be parent of other strand rows, without any according information in the data.
- I replaced your comma join with an explicit
CROSS JOIN, which makes the intention clearer. The assumption here is that there is only one row in thecondition_theme_lookuptable. Otherwise you will need a JOIN condition, which is not possible with the given schema. - You wrote in the comments: "There will be several more child levels in the final JSON". All levels must follow the same nesting logic, or at least be convertible (like the case with the years). If you have more surprises, the solution might be not appropriate. At some point I would consider to execute one query per level and build the hierarchical structure "bottom-up" (from leafs to root).
结合使用JSON_OBJECT()函数，JSON_ARRAYAGG()聚合函数和公用表表达式(CTE)，我们现在可以通过一个查询获得具有多个嵌套级别的嵌套JSON结果:
Using the combination of JSON_OBJECT() function, JSON_ARRAYAGG() aggregate function and common table expressions (CTE), we are now able to get a nested JSON result with multiple nesting levels with a single query:
with years as (
select
theme_fk,
year,
json_arrayagg(json_object('name', strand_name)) as children
from strand
group by theme_fk, year
), themes as (
select
t.theme_pk,
t.theme_name as name,
json_arrayagg(json_object('name', year, 'children', children)) as children
from theme t
left join years y on y.theme_fk = t.theme_pk
group by t.theme_pk
)
select json_object(
'name', c.core_condition,
'children', json_arrayagg(json_object('name', t.name, 'children', t.children))
) as json
from condition_theme_lookup c
cross join themes t
group by c.condition_theme_lookup_pk
每个嵌套级别都包装在其自己的CTE中，从而提高了可读性.每个级别都可以有自己的嵌套逻辑.由于结果是逐步构建的，因此添加更多级别应该没什么大不了的.
Every nesting level is wrapped in its own CTE, which improves the readability. And every level can have its own nesting logic. Since the result is build step by step, it shouldn't be a big deal to add more levels.
要交换UNION查询中的股数和年份，在最后两个子查询中只需要很少的更改:
To swap the levels of strands and years in the UNION query, only little changes are required in the last two subqueries:
...
SELECT DISTINCT
CONCAT('theme:', theme_fk, ',strand:', strand_name) AS global_id,
strand_name AS name,
CONCAT('theme:', theme_fk) AS parent_global_id
FROM strand
UNION ALL
SELECT CONCAT('strand_year:', strand_pk) AS global_id,
strand.year AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strand
如果您需要以特定方式对节点的子级进行排序，但对于级别进行不同的排序，则建议为每个子查询添加两列(num_sort和str_sort).例如，如果您希望主题按其PK排序-添加
If you need the children of a node to be sorted in a specific way but differently for the levels, I would suggest to add two columns (num_sort and str_sort) to every subquery. For example if you want themes to be sorted by their PK - Add
theme_pk as num_sort, '' as str_sort
如果应按名称对链进行排序-添加
If strand should be sorted by name - add
0 as num_sort, strand_name as str_sort
如果应该按自然值对年份进行排序("10年级">"2年级")
If years should be sorted by value but in a natural way ("Year 10" > "Year 2")
cast(replace(year, 'Year ', '') as signed) as num_sort, '' as str_sort
然后将ORDER BY num_sort, str_sort附加到查询中.
然后，您需要从PHP对象中删除这些列(属性)
You will then need to remove those columns (attributes) from the PHP objects
unset($row->parent_global_id);
unset($row->global_id);
unset($row->num_sort);
unset($row->str_sort);
这篇关于MySQL-如何修改父项/子项选择查询以将更多子项添加到现有数组/JSON?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
