如何使用PostgreSQL呈现与XML的一对多关系 [英] How to render one to many relationships to XML with PostgreSQL

问题描述

我的架构中有几个多对多的关系.例如，一个包有许多任务组，而任务组又有许多任务.所有表通过多对多表链接在一起，例如拥有包的主键和任务组的主键. (我知道这并不是严格要求的，因为XML是一对多的，但是我想不出更好的结构.)

I have several many to many relationships in my schema. For example, a package has many taskgroups, taskgroups in turn have many tasks. All tables are linked together via many to many tables, holding for instance the primary key of package and the primary key of taskgroup. (I know this is not strictly needed, since XML is one to many, but I couldn't think of a better structure).

能否以反映一对多结构的XML形式获取查询结果?因此，结果应该是这样的:

Is it possible to get the query result as XML, reflecting the one-to-many structure? So, the result should be like this:

<package id=1>
  <taskgroup id=1>
    <task id=1>
    <task id=2>
  </taskgroup>
  <taskgroup id=2>
    <task id=3>
  </taskgroup>
</package>

通过使用XMLELEMENT()和XMLATTRIBUTE()函数来获取所有任务，我设法获得了想要的一部分.像这样:

I have managed to get part of what I want by using the XMLELEMENT() and XMLATTRIBUTE() functions to get all the tasks. Like so:

    SELECT XMLELEMENT(name task,
      XMLATTRIBUTES(p.name as packageName),
        XMLELEMENT(name description, t.description),
        XMLELEMENT(name tutorial,
          XMLELEMENT(name someTaskChild1, t.data1)),
        XMLELEMENT(name objectives,
          XMLELEMENT(name someTaskChild2, t.data2)),
    ) 
    FROM packages p
    INNER JOIN package_taskgroup pt ON p.id = pt.package_id
    INNER JOIN taskgroups tg on pt.taskgroup_id = tg.id
    INNER JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
    INNER JOIN tasks t on tt.task_id = t.id
    WHERE p.id = somePackageId AND tg.id = someTaskGroupId

问题是如何将这些任务分为其父表元素?

The question is how to group these tasks into their parent table elements?

推荐答案

尝试:

SELECT p.id as pack_id,
       XMLELEMENT(name taskgroup,
                  XMLATTRIBUTES(tg.id as id),
                  XMLAGG(XMLELEMENT(name task,
                         XMLATTRIBUTES(t.id as id)) as xml_task_group
FROM packages p
JOIN package_taskgroup pt ON p.id = pt.package_id
JOIN taskgroups tg on pt.taskgroup_id = tg.id
JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
JOIN tasks t on tt.task_id = t.id
WHERE p.id = somePackageId 
GROUP BY p.id, tg.id

这将为您提供指定的p.id的所有任务组.

This will give you all the task groups for the p.id you specified.

然后:

SELECT XMLELEMENT(name package, 
                  XMLATTRIBUTES(pack_id as id),
                  XMLAGG(xml_task_group))
FROM (previous SELECT here)

这将为您提供指定的结构.

This will give you the structure you specified.

详细信息: XMLAGG ，加入的选择将如下所示:

Joined select will look like this:

SELECT XMLELEMENT(name package, 
                  XMLATTRIBUTES(pack_id as id),
                  XMLAGG(xml_task_group))
FROM (SELECT p.id as pack_id,
             XMLELEMENT(name taskgroup,
                        XMLATTRIBUTES(tg.id as id),
                        XMLAGG(XMLELEMENT(name task,
                                          XMLATTRIBUTES(t.id as id)
                       ))) as xml_task_group
      FROM packages p
      JOIN package_taskgroup pt ON p.id = pt.package_id
      JOIN taskgroups tg on pt.taskgroup_id = tg.id
      JOIN taskgroup_task tt on tg.id = tt.taskgroup_id
      JOIN tasks t on tt.task_id = t.id
      WHERE p.id = somePackageId 
      GROUP BY p.id, tg.id) t
GROUP BY pack_id

我只是将第一个选择复制到了第二个的FROM子句中.

I simply copied the first select into FROM clause of the second.

这篇关于如何使用PostgreSQL呈现与XML的一对多关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！