Fintech OS查询Payload内层关联异常：from字段始终引用基础实体

问题背景 #

需要将以下SQL转换为Fintech OS的查询Payload：

SELECT acc.id, pol.id, add.id, fp.no
FROM account acc
LEFT JOIN address add ON acc.ad_id = add.ad_id
LEFT JOIN mpol pol ON pol.ac_id = acc.id
LEFT JOIN mxpol mxp ON pol.mid = mxp.mxid
LEFT JOIN polF fp ON fp.fpid = mxp.fid
WHERE
(
(acc.sdate >= '2025-01-01' AND acc.sdate < '2025-02-01')
OR (acc.mdate >= '2025-01-01' AND acc.mdate < '2025-02-01')
)
AND fp.vuln = TRUE;

自行构建的Payload存在内层关联失效问题：pol与mxp、mxp与fp的关联中，from字段始终被识别为来自基础实体account，导致关联逻辑错误：

{
    "entity": {
        "alias": "acc",
        "name": "account",
        "attributeList": [
            { "name": "id", "alias": "acc_id" }
        ],
        "join": [
            {
                "entity": {
                    "alias": "add",
                    "name": "address",
                    "attributeList": [
                        { "name": "id", "alias": "add_id" }
                    ]
                },
                "fromTo": [
                    { "from": "ad_id", "to": "ad_id" }
                ],
                "type": "left"
            },
            {
                "entity": {
                    "alias": "pol",
                    "name": "mpol ",
                    "attributeList": [
                        { "name": "id", "alias": "pol_id" }
                    ]
                },
                "fromTo": [
                    { "from": "id", "to": "ac_id" }
                ],
                "type": "left"
            },
            {
                "entity": {
                    "alias": "mxp",
                    "name": "mxpol ",
                    "attributeList": []
                },
                "fromTo": [
                    { "from": "mid", "to": "mxid" }
                ],
                "type": "left"
            },
            {
                "entity": {
                    "alias": "fp",
                    "name": "polF ",
                    "attributeList": [
                        { "name": "no", "alias": "fp_no" }
                    ]
                },
                "fromTo": [
                    { "from": "fpid", "to": "fid" }
                ],
                "type": "left"
            }
        ]
    },
    "where": {
        "type": "and",
        "expressionList": [
            {
                "type": "or",
                "expressionList": [
                    {
                        "type": "and",
                        "conditionList": [
                            { "type": "gte", "first": "acc.sdate", "second": "2025-01-01" },
                            { "type": "lt", "first": "acc.sdate", "second": "2025-02-01" }
                        ]
                    },
                    {
                        "type": "and",
                        "conditionList": [
                            { "type": "gte", "first": "acc.mdate", "second": "2025-01-01" },
                            { "type": "lt", "first": "acc.mdate", "second": "2025-02-01" }
                        ]
                    }
                ]
            },
            {
                "type": "equals",
                "first": "fp.vuln",
                "second": "true"
            }
        ]
    }
}

问题原因 #

Fintech OS的查询Payload采用层级化关联结构，所有关联不能平级放在根实体的join数组中。内层关联（如mxpol关联mpol、polF关联mxpol）需要挂载到对应的父实体的join属性下，否则系统会默认从根实体account中匹配from字段，导致关联逻辑错误。此外，原Payload中部分实体名称存在多余空格（如mpol ），会导致实体匹配失败。

修正后的Payload #

{
    "entity": {
        "alias": "acc",
        "name": "account",
        "attributeList": [
            { "name": "id", "alias": "acc_id" }
        ],
        "join": [
            {
                "entity": {
                    "alias": "add",
                    "name": "address",
                    "attributeList": [
                        { "name": "id", "alias": "add_id" }
                    ]
                },
                "fromTo": [
                    { "from": "ad_id", "to": "ad_id" }
                ],
                "type": "left"
            },
            {
                "entity": {
                    "alias": "pol",
                    "name": "mpol",
                    "attributeList": [
                        { "name": "id", "alias": "pol_id" }
                    ],
                    "join": [
                        {
                            "entity": {
                                "alias": "mxp",
                                "name": "mxpol",
                                "attributeList": [],
                                "join": [
                                    {
                                        "entity": {
                                            "alias": "fp",
                                            "name": "polF",
                                            "attributeList": [
                                                { "name": "no", "alias": "fp_no" }
                                            ]
                                        },
                                        "fromTo": [
                                            { "from": "fid", "to": "fpid" }
                                        ],
                                        "type": "left"
                                    }
                                ]
                            },
                            "fromTo": [
                                { "from": "mid", "to": "mxid" }
                            ],
                            "type": "left"
                        }
                    ]
                },
                "fromTo": [
                    { "from": "id", "to": "ac_id" }
                ],
                "type": "left"
            }
        ]
    },
    "where": {
        "type": "and",
        "expressionList": [
            {
                "type": "or",
                "expressionList": [
                    {
                        "type": "and",
                        "conditionList": [
                            { "type": "gte", "first": "acc.sdate", "second": "2025-01-01" },
                            { "type": "lt", "first": "acc.sdate", "second": "2025-02-01" }
                        ]
                    },
                    {
                        "type": "and",
                        "conditionList": [
                            { "type": "gte", "first": "acc.mdate", "second": "2025-01-01" },
                            { "type": "lt", "first": "acc.mdate", "second": "2025-02-01" }
                        ]
                    }
                ]
            },
            {
                "type": "equals",
                "first": "fp.vuln",
                "second": true
            }
        ]
    }
}

关键修正点 #

• 层级化嵌套关联：将mxpol作为mpol的子join，polF作为mxpol的子join，确保每个from字段对应父级实体的属性，而非根实体。
• 修正实体名称空格：移除mpol、mxpol、polF名称后的多余空格，保证实体正确匹配。
• 调整布尔值类型：将"true"改为布尔类型true，符合Fintech OS的参数类型要求。
• 修正关联字段顺序：原SQL中fp.fpid = mxp.fid对应Payload中from: "fid", to: "fpid"，确保关联逻辑与SQL一致。

内容的提问来源于stack exchange，提问作者weeb recomendations