[Bug] join 关键字 在 count 分页的时候，会报关联的子表的字段找不到

[wz11wz]

APIJSON Version/APIJSON 版本号

7.5.5

Database Type & Version/数据库类型及版本号

PostgreSQL 17.3

Environment/环境信息

- JDK/基础库: 17
- OS/系统: window11

APIAuto Screenshots/APIAuto 请求与结果完整截屏

1

Current Behavior/问题描述

{
    "@schema": "warehose",
    "[]": {
        "Inventory": {},
        "query": 2,
        "count": 10,
        "page": 0,
        "join": "</Location_info/code@,</Warehose_info/id@",
        "Location_info": {
            "code@": "/Inventory/location_code"
        },
        "Warehose_info": {
            "id@": "/Location_info/warehouse_id"
        }
    },
    "@explain": true
}

如果"query": 0 ，查询没问题，

分页count 生成的，
已生成 1 条 SQL
execute  startTime = 1750920200358
database = ; schema = warehose; sql = 
SELECT count(*) AS "count" FROM "warehose"."inventory" AS "Inventory"  
   LEFT JOIN ( SELECT "code" FROM "warehose"."location_info" )  AS "Location_info" ON "Location_info"."code" = "Inventory"."location_code"  
   LEFT JOIN ( SELECT "id" FROM "warehose"."warehose_info" )  AS "Warehose_info" ON "Warehose_info"."id" = "Location_info"."warehouse_id"  

这里限制只能是 SELECT "code" FROM "warehose"."location_info"  code了，这个count sql会运行不通过。

Expected Behavior/期望结果

有没有另外一种访问可以通过count

Any additional comments?/其它补充说明？

[wz11wz]

[wz11wz]

{
"@Schema": "zlgl",
"[]": {
"Qms_checkout_task": {},
"join": "</Qms_sample_info/id@,</Qms_sampling_task/id@",
"Qms_sample_info": {
"id@": "/Qms_checkout_task/sample_info_id"
},
"Qms_sampling_task": {
"id@": "/Qms_sample_info/task_id"
},
"supplier_id": "12"
},
"@Explain": true
} 我想要一种 ，join之后，where条件加到最外面，where在最外面可以过滤行数。SELECT "Qms_checkout_task"., "Qms_sample_info"., "Qms_sampling_task".* FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" LIMIT 9999

希望 where Qms_sampling_task.supplier_id = '123'

"Qms_sample_info"., "Qms_sampling_task". FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" where Qms_sampling_task.supplier_id = '123' LIMIT 9999

[TommyLemon]

@wz11wz

PostgreSQL 不支持 JOIN ON 引用没有 SELECT 的字段，所以需要在 SELECT count(*) 后面加上 location_code，
在 AbstractSQLConfig.gainColumnString 中遍历 joinList 里面每个 join.getOnList，把引用的主表字段带上
或者干脆加上 * 就不用遍历了，性能会差些。

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2343-L2376

[wz11wz]

这是来自QQ邮箱的假期自动回复邮件。您好，我最近正在休假中，无法亲自回复您的邮件。我将在假期结束后，尽快给您回复。

[TommyLemon]

{ "@Schema": "zlgl", "[]": { "Qms_checkout_task": {}, "join": "</Qms_sample_info/id@,</Qms_sampling_task/id@", "Qms_sample_info": { "id@": "/Qms_checkout_task/sample_info_id" }, "Qms_sampling_task": { "id@": "/Qms_sample_info/task_id" }, "supplier_id": "12" }, "@Explain": true } 我想要一种 ，join之后，where条件加到最外面，where在最外面可以过滤行数。SELECT "Qms_checkout_task"., "Qms_sample_info"., "Qms_sampling_task".* FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" LIMIT 9999

希望 where Qms_sampling_task.supplier_id = '123'

"Qms_sample_info"., "Qms_sampling_task". FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" where Qms_sampling_task.supplier_id = '123' LIMIT 9999

@wz11wz 直接放在 []:{} 会导致和 count, page, query, join, compat 等字段冲突，可以放到 join: {} 内。
目前 join: {} 内只支持

"join": {
   "&/Table": {}, // INNER JOIN Table
   "</Table2/key2": {}, // LEFT JOIN Table2 ON key2 = MainTable.key
}

这种格式，可以直接

"join": {
   "</Table": {},
   "</Table2/key2": {},
   "supplier_id": "12" // 把这个条件放到主表 WHERE 中
}

需要改 AbstractParser.onJoinParse，value 不为 Map 类型时会当成 WHERE 条件加到主表，还可以

"join": {
   "</Table": {},
   "</Table2/key2": {},
   "supplier_id": "12", // 把这个条件放到 WHERE 中 MainTable.supplier_id = '12'
   "Table": { // 副表加到 JOIN 外层的条件
     "key": value // WHERE Table.key = value
   },
   "Table2": { // 副表加到 JOIN 外层的条件
      "key2": value2 // WHERE Table.key2 = value2
    },
}

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java#L1541-L1589

[TommyLemon]

改好后给 APIJSON 提交 PR 贡献，谢谢，开源要大家一起参与贡献才会更美好~

提 PR 贡献代码的步骤可参考：
https://github.com/Tencent/APIJSON/blob/master/CONTRIBUTING.md#%E4%B8%BA%E4%BB%80%E4%B9%88%E4%B8%80%E5%AE%9A%E8%A6%81%E8%B4%A1%E7%8C%AE%E4%BB%A3%E7%A0%81

[wz11wz]

@wz11wz

PostgreSQL 不支持 JOIN ON 引用没有 SELECT 的字段，所以需要在 SELECT count(*) 后面加上 location_code， 在 AbstractSQLConfig.gainColumnString 中遍历 joinList 里面每个 join.getOnList，把引用的主表字段带上 或者干脆加上 * 就不用遍历了，性能会差些。

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2343-L2376

我找到代码位置：

apijson.orm.AbstractSQLConfig#parseJoin

joinList SqlConfig 里设置了，column。

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L6322-L6331
@TommyLemon
我修改一下这里

[wz11wz]

{ "@Schema": "zlgl", "[]": { "Qms_checkout_task": {}, "join": "</Qms_sample_info/id@,</Qms_sampling_task/id@", "Qms_sample_info": { "id@": "/Qms_checkout_task/sample_info_id" }, "Qms_sampling_task": { "id@": "/Qms_sample_info/task_id" }, "supplier_id": "12" }, "@Explain": true } 我想要一种 ，join之后，where条件加到最外面，where在最外面可以过滤行数。SELECT "Qms_checkout_task"., "Qms_sample_info"., "Qms_sampling_task".* FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" LIMIT 9999
希望 where Qms_sampling_task.supplier_id = '123'
"Qms_sample_info"., "Qms_sampling_task". FROM "zlgl"."qms_checkout_task" AS "Qms_checkout_task" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sample_info" ) AS "Qms_sample_info" ON "Qms_sample_info"."id" = "Qms_checkout_task"."sample_info_id" LEFT JOIN ( SELECT * FROM "zlgl"."qms_sampling_task" ) AS "Qms_sampling_task" ON "Qms_sampling_task"."id" = "Qms_sample_info"."task_id" where Qms_sampling_task.supplier_id = '123' LIMIT 9999

@wz11wz 直接放在 []:{} 会导致和 count, page, query, join, compat 等字段冲突，可以放到 join: {} 内。 目前 join: {} 内只支持

"join": {
"&/Table": {}, // INNER JOIN Table
"</Table2/key2": {}, // LEFT JOIN Table2 ON key2 = MainTable.key
}
这种格式，可以直接

"join": {
"</Table": {},
"</Table2/key2": {},
"supplier_id": "12" // 把这个条件放到主表 WHERE 中
}
需要改 AbstractParser.onJoinParse，value 不为 Map 类型时会当成 WHERE 条件加到主表，还可以

"join": {
"</Table": {},
"</Table2/key2": {},
"supplier_id": "12", // 把这个条件放到 WHERE 中 MainTable.supplier_id = '12'
"Table": { // 副表加到 JOIN 外层的条件
"key": value // WHERE Table.key = value
},
"Table2": { // 副表加到 JOIN 外层的条件
"key2": value2 // WHERE Table.key2 = value2
},
}
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractParser.java#L1541-L1589

@TommyLemon

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig#L3721-L3850