如何关联三张数据库表？附表结构及预期输出示例

问题描述 #

我有三张数据库表：

• type表：字段为id、type、reason_id
• reason表：字段为id、reason
• permission表：字段为id、type_id，不确定是否包含reason_id字段

需要查询得到如下嵌套结构的JSON结果（已修正原示例中的JSON语法错误）：

{
    "message": "Get all permissions with type and reason",
    "data": [
        {
            "id": "1",
            "types": [
                {
                    "id": "1",
                    "type": "Sick",
                    "reasons": [
                        {
                            "id": "1",
                            "reason": "covid-19"
                        }
                    ]
                }
            ]
        }
    ]
}

解决方案 #

情况1：permission表不存在reason_id字段 #

关联逻辑：permission通过type_id关联type，type通过reason_id关联reason。

基础SQL查询（以MySQL为例）

先获取关联后的扁平数据：

SELECT
    p.id AS permission_id,
    t.id AS type_id,
    t.type,
    r.id AS reason_id,
    r.reason
FROM permission p
LEFT JOIN `type` t ON p.type_id = t.id
LEFT JOIN reason r ON t.reason_id = r.id;

后端组装嵌套结构（以Python为例）

SQL无法直接返回嵌套JSON，需通过后端代码将扁平结果组装成目标结构：

# 假设sql_query_result是SQL查询返回的扁平数据列表
sql_query_result = [
    {"permission_id": "1", "type_id": "1", "type": "Sick", "reason_id": "1", "reason": "covid-19"}
]

permission_map = {}
for row in sql_query_result:
    perm_id = row["permission_id"]
    # 初始化权限条目
    if perm_id not in permission_map:
        permission_map[perm_id] = {
            "id": perm_id,
            "types": []
        }
    perm_item = permission_map[perm_id]
    
    # 检查当前type是否已存在
    type_item = next((t for t in perm_item["types"] if t["id"] == row["type_id"]), None)
    if not type_item:
        type_item = {
            "id": row["type_id"],
            "type": row["type"],
            "reasons": []
        }
        perm_item["types"].append(type_item)
    
    # 添加reason到对应type的列表
    type_item["reasons"].append({
        "id": row["reason_id"],
        "reason": row["reason"]
    })

# 组装最终结果
final_result = {
    "message": "Get all permissions with type and reason",
    "data": list(permission_map.values())
}

情况2：permission表存在reason_id字段 #

关联逻辑：permission可直接通过自身reason_id关联reason，同时保留与type的关联，兼容两种关联场景。

基础SQL查询（以MySQL为例）

SELECT
    p.id AS permission_id,
    t.id AS type_id,
    t.type,
    r.id AS reason_id,
    r.reason
FROM permission p
LEFT JOIN `type` t ON p.type_id = t.id
LEFT JOIN reason r ON p.reason_id = r.id OR t.reason_id = r.id;

后端组装逻辑

与情况1的后端代码完全一致，只需确保SQL返回字段与代码中的键名对应即可。

可选：PostgreSQL直接生成嵌套JSON #

如果使用PostgreSQL，可利用原生JSON函数直接在SQL中生成目标结构，无需后端额外处理：

SELECT
    json_build_object(
        'message', 'Get all permissions with type and reason',
        'data', json_agg(
            json_build_object(
                'id', p.id,
                'types', (
                    SELECT json_agg(
                        json_build_object(
                            'id', t.id,
                            'type', t.type,
                            'reasons', (
                                SELECT json_agg(
                                    json_build_object('id', r.id, 'reason', r.reason)
                                ) FROM reason r WHERE r.id = t.reason_id
                            )
                        )
                    ) FROM `type` t WHERE t.id = p.type_id
                )
            )
        )
    ) AS result
FROM permission p;

内容的提问来源于stack exchange，提问作者Vireak Ron