多对多多级查询：基于obj_rels表追溯各对象类型关联实体

嘿，刚好能帮你搞定这个obj_rels表的关联查询需求！

核心思路 #

这个表的关联关系没有固定的主次方向——可能是A关联B，也可能是B关联A，而且还存在多层级的链状关联（比如评论→文件→提交→实体）。所以咱们得用递归遍历+双向检查的方式，把所有可能的关联路径都走一遍，最终定位到每个对象对应的实体。

示例SQL查询 #

WITH RECURSIVE entity_relations AS (
    -- 初始节点：捞取所有直接关联到ENT的记录（双向匹配）
    SELECT
        CASE 
            WHEN type = 'ENT' THEN target_obj_id
            WHEN related_type = 'ENT' THEN obj_id
        END AS entity_id,
        CASE 
            WHEN type != 'ENT' THEN obj_id
            WHEN related_type != 'ENT' THEN target_obj_id
        END AS object_id,
        CASE 
            WHEN type != 'ENT' THEN type
            WHEN related_type != 'ENT' THEN related_type
        END AS object_type,
        1 AS depth
    FROM obj_rels
    WHERE type = 'ENT' OR related_type = 'ENT'

    UNION ALL

    -- 递归遍历：继续查找未关联到实体的对象的关联记录（双向匹配）
    SELECT
        er.entity_id,
        CASE 
            WHEN ors.type = er.object_type THEN ors.target_obj_id
            WHEN ors.related_type = er.object_type THEN ors.obj_id
        END AS new_object_id,
        CASE 
            WHEN ors.type != er.object_type THEN ors.type
            WHEN ors.related_type != er.object_type THEN ors.related_type
        END AS new_object_type,
        er.depth + 1
    FROM entity_relations er
    JOIN obj_rels ors
        ON (ors.obj_id = er.object_id AND ors.type != er.object_type)
        OR (ors.target_obj_id = er.object_id AND ors.related_type != er.object_type)
    WHERE 
        -- 避免循环遍历（防止A→B→A这种死循环）
        CASE 
            WHEN ors.type = er.object_type THEN ors.target_obj_id
            WHEN ors.related_type = er.object_type THEN ors.obj_id
        END NOT IN (SELECT object_id FROM entity_relations)
)
-- 最终整理成清晰的结果
SELECT
    object_type AS 类型标识,
    CASE object_type
        WHEN 'COM' THEN '评论'
        WHEN 'FIL' THEN '文件'
        WHEN 'SBM' THEN '提交'
        WHEN 'ENT' THEN '实体'
    END AS 对象类型,
    object_id AS 对象ID,
    entity_id AS 关联实体ID
FROM entity_relations
ORDER BY object_type, object_id;

代码分步解释 #

1. 初始节点构建：先把所有直接和ENT（实体）挂钩的记录捞出来——不管是实体作为主对象，还是被关联的对象，都提取出实体ID和对应的其他对象信息，这是咱们遍历的起点。
2. 递归遍历关联链：从起点出发，把每个未处理的对象和其他关联记录做双向匹配（检查obj_id和target_obj_id），这样不管关联方向是啥都不会漏掉。同时加了循环避免逻辑，防止出现A连B、B连A的死循环。
3. 结果整理优化：最后把英文类型标识转换成中文名称，让结果一目了然，每个对象对应的关联实体ID都清晰呈现。

补充提示 #

• 如果你的数据库不支持递归CTE（比如旧版MySQL），可以改用多次JOIN的方式处理固定层级的关联，但递归CTE更灵活，能应对任意深度的关联链。
• 可以根据实际需求添加过滤条件，比如只查询特定类型的对象，或者限制关联深度。

内容的提问来源于stack exchange，提问作者Curtis Fuller