[Python] Fintech OS query Payload

Create a payload for this

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 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;


Here is the query I'm trying to achieve.

This is what I have build:

{
"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"
}
]
}
}


The relations are correct the problem is that the inner relation like pol joining on mxp is not working the inner relations are not able to join the column refered in from is always considering to be from the base entity.

Continue reading...