MYSQL复杂联盟 [英] MYSQL complex Union
问题描述
到目前为止，我可以使用以下MySQL查询:
I have the following MySQL query working so far:
SELECT CONCAT('program:', program_pk) AS global_id,
program_name AS name,
NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
theme_name AS name,
CONCAT('program:', program_pk) AS parent_global_id
FROM theme CROSS JOIN program
UNION ALL
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
UNION ALL
SELECT CONCAT('strand_year:', strand_pk, ',unit:', unit_pk) AS global_id,
CONCAT(unit.unit_code, ' ', unit.unit_name) AS name,
CONCAT('strand_year:', strand_pk) AS parent_global_id
FROM strand LEFT JOIN unit ON strand.year = unit.year
表格程序
+------------+--------------+
| program_pk | program_name |
+------------+--------------+
表格主题
+----------+------------+
| theme_pk | theme_name |
+----------+------------+
表链
+-----------+-------------+----------+------+
| strand_pk | strand_name | theme_fk | year |
+-----------+-------------+----------+------+
表格单位
+---------+-----------+-----------+--------+------+----------+
| unit_pk | unit_code | unit_name | points | year | theme_fk |
+---------+-----------+-----------+--------+------+----------+
关系是:
程序->主题->链->年->单位
program -> theme -> strand -> year -> unit
我现在需要向表中添加表learning_event
I now need to add to the query the table learning_event
表learning_event
Table learning_event
+-------------------+---------------------+---------+-----------+----------------+
| learning_event_pk | learning_event_name | unit_fk | strand_fk | core_condition |
+-------------------+---------------------+---------+-----------+----------------+
将来自父unit的学习事件分支给:
to branch learning events from the parent unit to give:
程序->主题->链->年->单位->学习活动
program -> theme -> strand -> year -> unit -> learning event
请注意，对于给定的链和单位，仅应显示与链相关的学习事件.
Note that only learning events related to the strand should show for a given strand and unit.
我已经试过了，但是真的不确定如何将其与学习活动与单元和链相关联.
I have played around with this, but really unsure of how to get this to work with relating learning events to unit and strand.
更新
在JSON格式下，现有查询的内容如下:
In JSON format, what I have with the existing query is like:
{
"name": "MD",
"children": [{
"name": "Professional",
"children": [{
"name": "Professional Behavours",
"children": [{
"name": "Year 1",
"children": [{
"name": "IMED4443 Integrated Medical Sciences 1"
}, {
"name": "IMED4444 Integrated Medical Sciences 2"
}]
}
我正在寻找的新输出如下:
The new output I'm looking for is like:
"name": "MD",
"children": [{
"name": "Professional",
"children": [{
"name": "Professional Behavours",
"children": [{
"name": "Year 1",
"children": [{
"name": "IMED4443 Integrated Medical Sciences 1"
}, {
"name": "IMED4444 Integrated Medical Sciences 2",
"children": [{
"name": "Lecture - CVS"
}, {
"name": "Lecture - Type 1 Diabetes"
}...
并且学习事件仅应显示与单位和链之间的关系.
and the learning events should only show where this is a relationship with the unit AND the strand.
仅供参考，使用以下方式处理关系:
FYI, the relationships are handled with:
$result = $connection->query($query);
$data = array();
while ($row = $result->fetch_object()) {
$data[$row->global_id] = $row;
}
$roots = array();
foreach ($data as $row) {
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);
新更新
此查询由Jonathan Willcock提供并由我修改，它很接近，它显示了所有主题和子线的年，以及第一个主题"Professional"的单元和学习事件，但未显示单位其他主题.
This query supplied by Jonathan Willcock and modified by me, is close, it shows the Years for all themes and Strands, as well as Units and Learning Events for the the first Theme, 'Professional', but the units are not showing for any other Themes.
SELECT CONCAT('program:', program_pk) AS global_id,
program_name AS name,
NULL AS parent_global_id
FROM program
UNION ALL
SELECT CONCAT('theme:', theme_pk) AS global_id,
theme_name AS name,
CONCAT('program:', program_fk) AS parent_global_id
FROM theme
UNION ALL
SELECT
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('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS global_id,
strandyear_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name) AS parent_global_id
FROM strandyear sy
INNER JOIN strand s ON s.strand_pk = sy.strand_fk
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS global_id,
unit_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name) AS parent_global_id
FROM unit u
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk
UNION ALL
SELECT
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name, ',learning_event:', learning_event_name) AS global_id,
learning_event_name AS name,
CONCAT('theme:', theme_fk, ',strand:', strand_name, ',strandyear:', strandyear_name, ',unit:', unit_name) AS parent_global_id
FROM learning_event le
INNER JOIN unit u ON u.unit_pk = le.unit_fk
INNER JOIN strandyear sy ON u.strandyear_fk = sy.strandyear_pk
INNER JOIN strand s ON s.strand_pk = sy.strand_fk
请注意，parent_global_id必须与前面的global_id相同.
Note that the parent_global_id needs to be the same as the preceding global_id.
最后更新
上面的查询工作正常！问题是单位表.更新 db-fiddle
The above query is working fine! The issue was the unit table. Update db-fiddle
推荐答案
如果所需的层次结构是程序->主题->链->年->单位-> learning_event，则应调整表结构以反映此情况.特别是主题应该在主题和程序之间具有外键关系，并且您需要一年的额外级别.在主题和程序之间使用外键避免了交叉连接的需要.交叉连接有一种会咬你的习惯，通常应该避免.
If your desired hierarchy is program -> theme -> strand -> year -> unit -> learning_event, then you should adjust your table structure to reflect this. In particular theme should have a foreign key relationship between theme and program, and you need an extra level for year. Having a foreign key between theme and program avoids the need for a cross join. Cross joins have a habit of biting you and are generally to be avoided.
如果您查看此 db小提琴，您会发现我有进行了这些更改.我曾称年级为stryearyear以避免使用保留字，但其意图应明确.现在，这些联接变为内部联接(而不是左联接)，以从树中的更高级别中获取描述值，并且最低级别(learning_events)自动仅包含与strand，year和unit匹配的值，除了其他原因外通过简单的权宜之计，即每个级别都有一个上层级别的外键，结构本身就可以保证它的安全.
If you look at this db fiddle you will see that I have made these changes. I have called the year level strandyear to avoid using a reserved word, but the intention should be clear. Now the joins become inner joins (instead of left joins) to pick up the description values from higher levels in the tree, and the bottom level (learning_events) automatically only contains values that match strand, year, and unit, for no other reason than that the structure itself guarantees it, through the simple expedient that each level has a foreign key to the level above.
请注意，外键有效地链接了链接.例如，您不需要在learning_event和strand之间使用特定的外键，因为链中的中间键可以保证这种关系.
Note that the foreign keys effectively chain link. You do not need, for example, a specific foreign key between learning_event and strand, because the intervening keys in the chain guarantee the relationship.
这篇关于MYSQL复杂联盟的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
