jquery - PostgreSQL 9.3 -> 来自嵌套集的 JSON -> jQuery ListView

Question

我有这个嵌套集:

id;parent;name;lft;rgt
----------------------
1;0;"Food";2;21
3;1;"Appetizer";3;8
8;3;"Nachos & salsa";4;5
9;3;"Kentucky chicken wings";6;7
4;1;"Soup";9;14
10;4;"Broth";10;11
11;4;"Tomato soup";12;13
5;1;"Pizza";15;20
12;5;"Americana";16;17
13;5;"Margherita";18;19
2;0;"Beverages";22;27
6;2;"Wines";23;24
7;2;"Soft drinks";25;26

我想要一个代表完整树的 JSON 输出。
我想从 JSON 构建一个这样的嵌套列表:
http://demos.jquerymobile.com/1.2.1/docs/lists/lists-nested.html#&ui-page=2-4

感谢您的帮助!

Answer 1

要做的第一件事是以可以检索树的方式查询行。为此，我们可以简单地使用递归查询。假设您的表名为 food，以下查询是对其进行递归查询的一个很好的示例:

WITH RECURSIVE t AS (
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
    FROM food f
    WHERE f.parent = 0
    UNION ALL
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
    FROM food f JOIN t ON f.parent = t.id
)
SELECT repeat('|__', level)||t.name AS tree, level, path
FROM t
ORDER BY path;

返回结果如下:

             tree             | level |                   path                    
------------------------------+-------+-------------------------------------------
 Beverages                    |     0 | {Beverages}
 |__Soft drinks               |     1 | {Beverages,"Soft drinks"}
 |__Wines                     |     1 | {Beverages,Wines}
 Food                         |     0 | {Food}
 |__Appetizer                 |     1 | {Food,Appetizer}
 |__|__Kentucky chicken wings |     2 | {Food,Appetizer,"Kentucky chicken wings"}
 |__|__Nachos & salsa         |     2 | {Food,Appetizer,"Nachos & salsa"}
 |__Pizza                     |     1 | {Food,Pizza}
 |__|__Americana              |     2 | {Food,Pizza,Americana}
 |__|__Margherita             |     2 | {Food,Pizza,Margherita}
 |__Soup                      |     1 | {Food,Soup}
 |__|__Broth                  |     2 | {Food,Soup,Broth}
 |__|__Tomato soup            |     2 | {Food,Soup,"Tomato soup"}
(13 rows)

基本上，path 数组会为我们提供一个元素，以一种为我们提供树的方式对行进行排序(如果需要，您可以使用 name 以外的其他列)，以及 level (基本上路径长度 - 1)为我们提供了元素所在的级别。有了这两个信息，我们可以对 window function lead 使用一些技巧。 (使用窗口 ORDER BY path)在每一行查看下一行的 level 并创建我们的 json(检查查询中的注释) :

WITH RECURSIVE t AS (
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, array[f.name] AS path, 0 AS level
    FROM food f
    WHERE f.parent = 0
    UNION ALL
    SELECT f.id, f.name, f.parent, f.lft, f.rgt, t.path || f.name, level+1
    FROM food f JOIN t ON f.parent = t.id
)
SELECT (E'[\n'||string_agg(json, E'\n')||E'\n]')::json FROM (
SELECT
    /* Add some simple indentation (why not?) */
    repeat('    ', level)
    || '{"name":'||to_json(name)|| ', "items":['
    ||
    /* The expr bellow will return the level of next row, or -1 if it is last */
    CASE coalesce(lead(level) OVER(ORDER BY path), -1)
        /* Next row opens a new level, so let's add the items array */
        WHEN level+1 THEN ''
        /* WHEN level+1 THEN ', "items":[' */
        /* We are on the same level, so just close the current element */
        WHEN level THEN ']},'
        /* Last row, close the current element and all other levels still opened (no indentation here, sorry) */
        WHEN -1 THEN ']}' || repeat(']}', level)
        /* ELSE, the next one belongs to another parent, just close me and my parent */
        ELSE /* closes me: */ ']}' /* closes my parent: */ || E'\n'||repeat('    ', level-1)||']},'
    END AS json
FROM t
) s1;

这将为我们提供以下 json:

[
{"name":"Beverages", "items":[
    {"name":"Soft drinks", "items":[]},
    {"name":"Wines", "items":[]}
]},
{"name":"Food", "items":[
    {"name":"Appetizer", "items":[
        {"name":"Kentucky chicken wings", "items":[]},
        {"name":"Nachos & salsa", "items":[]}
    ]},
    {"name":"Pizza", "items":[
        {"name":"Americana", "items":[]},
        {"name":"Margherita", "items":[]}
    ]},
    {"name":"Soup", "items":[
        {"name":"Broth", "items":[]},
        {"name":"Tomato soup", "items":[]}]}]}
]

这是一个有点技巧的查询，我希望评论能有所帮助(也希望它对任何测试用例都是正确的)。