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

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?/其它补充说明？

{
    "@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

@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

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

@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

改好后给 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

@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

我找到代码位置：

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
我修改一下这里

@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

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

@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

@wz11wz 对，是这样，另外

"join": {
  "supplier_id": "12" // 这种在最外层加条件看起来没大的必要，因为主表上的条件、排序等都已经是在 JOIN 外层拼接了，不过按以下方式也能做
}

而这种按你说的在 Join.java 内加上 where 虽然可行，但对于 HAVING, ORDER BY 也都要单独加字段，
所以不如直接复用 outerConfig 中的 where, having, order
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/Join.java#L30-L32

"join": { // 去除所有 </Table/key 这种带 / 分隔的路径 key: {} 后，剩下的就是 outerConfig 部分
   "Table": { // 副表加到 JOIN 外层的条件
     "key": value // WHERE Table.key = value
   },
   "Table2": { // 副表加到 JOIN 外层的条件
      "key2": value2 // WHERE Table.key2 = value2
    },
}

这里遍历 outer，里面有 Table: {} 格式的键值对就把每个都再解析成对应的 List outerConfigList（原来的 SQLConfig outerConfig 改成 List 适配多个副表在 JOIN 外层的条件），其它 key: value 就是主表 SQLConfig，作为 List 第 0 项。
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L6308-L6316

然后在最终 WHERE/HAVAING/GROUP BY 等所有用到 SQLConfig outerConfig 的地方都改成 for 循环遍历 List outerConfigList 处理
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L1653-L1667
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L1577-L1591

@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

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

@wz11wz 对，是这样，另外

"join": {
"supplier_id": "12" // 这种在最外层加条件看起来没必要，因为主表上的条件、排序等都已经是在 JOIN 外层拼接了，另外也太不好实现。
}
而这种按你说的在 Join.java 内加上 where 虽然可行，但对于 HAVING, ORDER BY 也都要单独加字段，所以不如直接复用 outerConfig 中的 where, having, order https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/Join.java#L30-L32

"join": {
"Table": { // 副表加到 JOIN 外层的条件
"key": value // WHERE Table.key = value
},
"Table2": { // 副表加到 JOIN 外层的条件
"key2": value2 // WHERE Table.key2 = value2
},
}

outerConfig 原本是用解析什么的？把 left join 以及right join 或者所有的连表副表的条件主sql外的条件，放这里都没有么？ @TommyLemon

@wz11wz

@wz11wz 原来的 outerConfig 就是指所有 &/Table/key 这种 INNER/OUTER/ANTI/FOREIGN JOIN 带 / 分隔的路径 key: {}，现在要把不带 / 的单独 Table: {} 里面的内容给合并进来作为 outerConfig，补充 LEFT/RIGHT JOIN 在外层的条件

"join": { // 去除所有 </Table/key 这种带 / 分隔的路径 key: {} 后，剩下的就是 outerConfig 部分
   "</Table": {}, // INNER JOIN 等非 LEFT/RIGHT JOIN 的 outerConfig
   ">/Table2/key2": {}, // INNER JOIN 等非 LEFT/RIGHT JOIN 的 outerConfig，key2 可省略，变成 ">/Table2": {}
   "Table": { // 副表加到 JOIN 外层的条件，加到 </Table 对应的 outerConfig
     "key": value // JOIN 外层 WHERE Table.key = value
   },
   "Table2": { // 副表加到 JOIN 外层的条件，加到 >/Table2/key2 对应的 outerConfig，目前可以只支持 >/Table2，不用兼容为了提升一点性能而具体指定 ON key 的 >/Table2/key2
      "key2": value2 // JOIN 外层 WHERE Table.key2 = value2,
      "@column": "key2,key3"  // JOIN 外层 SELECT Table2.key2, Table2.key3,
      "@group": "key2+"  // JOIN 外层 GROYP BY key2
      "@having": "count(key3)>0"  // JOIN 外层 HAVING count(key3)>0
      "@order": "key2+,key3-"  // JOIN 外层 ORDER BY key2 ASC, key3 DESC
    },
}

现在看起来只需要在这里合并 outerConfig 就行了，后续的处理都是复用现有的逻辑(不用把 SQLConfig outerConfig 改成 List outerConfigList)

目前 outerConfig 是把条件加到 ON 上，而不是外层 WHERE 上，所以需要以上方式

http://apijson.cn/api/?send=false&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:{%22%3C/User%22:{%22sex%22:0}},%22Comment%22:{},%22User%22:{%22name$%22:%22%25a%25%22,%22id@%22:%22%2FComment%2FuserId%22}},%22@explain%22:true}

现在的 Join.java 中 SQLConfig outerConfig 重命名成 onConfig 及对应的 get set 方法，IDE 会批量替换所有用到它的地方，
原来的逻辑就保留了，然后再加回来 SQLConfig outerConfig，然后下方的键值对放到 outerConfig 上，后面再具体 AbstractSQLConfig gainWhereString, gainGroupString, gainHavingString, gainOrderString ...使用新的 outerConfig

"join":{
   "</Table": { // onConfig ON Table.refKey = MainTable.relateKey
      "key": value //  AND key = value
    },
   ">/Table2/key2": {}, // onConfig ON Table.key2 = MainTable.relateKey2
   "Table": { // 新的 outerConfig
     "key": value // JOIN 外层 WHERE Table.key = value
   },
   "Table2": { // 新的 outerConfig
      "key2": value2 // JOIN 外层 WHERE Table.key2 = value2,
      "@column": "key2,key3"  // JOIN 外层 SELECT Table2.key2, Table2.key3,
      "@group": "key2+"  // JOIN 外层 GROYP BY key2
      "@having": "count(key3)>0"  // JOIN 外层 HAVING count(key3)>0
      "@order": "key2+,key3-"  // JOIN 外层 ORDER BY key2 ASC, key3 DESC
    }
}

https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/Join.java#L30-L32

@TommyLemon

目前 outerConfig 是把条件加到 ON 上，而不是层 WHERE 上，所以需要以上方式

http://apijson.cn/api/?send=false&type=JSON&url=http%3A%2F%2Fapijson.cn%3A8080%2Fget&json={%22[]%22:{%22join%22:{%22%3C/User%22:{%22sex%22:0}},%22Comment%22:{},%22User%22:{%22name$%22:%22%25a%25%22,%22id@%22:%22%2FComment%2FuserId%22}},%22@explain%22:true}

现在的 Join.java 中 SQLConfig outerConfig 重命名成 onConfig 及对应的 get set 方法，IDE 会批量替换所有用到它的地方，原来的逻辑就保留了，然后再加回来 SQLConfig outerConfig，然后下方的键值对放到 outerConfig 上，后面再具体 AbstractSQLConfig gainWhereString, gainGroupString, gainHavingString, gainOrderString ...使用新的 outerConfig
"join":{
   "</Table": { // onConfig ON Table.refKey = MainTable.relateKey
      "key": value //  AND key = value
    },
   ">/Table2/key2": {}, // onConfig ON Table.key2 = MainTable.relateKey2
   "Table": { // 新的 outerConfig
     "key": value // JOIN 外层 WHERE Table.key = value
   },
   "Table2": { // 新的 outerConfig
      "key2": value2 // JOIN 外层 WHERE Table.key2 = value2,
      "@column": "key2,key3"  // JOIN 外层 SELECT Table2.key2, Table2.key3,
      "@group": "key2+"  // JOIN 外层 GROYP BY key2
      "@having": "count(key3)>0"  // JOIN 外层 HAVING count(key3)>0
      "@order": "key2+,key3-"  // JOIN 外层 ORDER BY key2 ASC, key3 DESC
    }
}
https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/Join.java#L30-L32

@TommyLemon 好，我大致理解了。我看下代码。到时候改了，你看下。

好的，谢谢

LEFT/RIGHT JOIN 新增支持在外层WHERE/GROUP BY/HAVING 加条件/排序(待解决引入 bug) #824

@wz11wz

Hi, I'd like to help fix this issue. I noticed @wz11wz said "我修改一下这里" (I'll modify this) back in July 2025 but no PR has been submitted since, so I'll take over.

I've reproduced the problem on the current main (v10.0.0). Here's what I found:

PostgreSQL scenario (the original report): already fixed on main — for non-MySQL databases the join subquery falls back to SELECT *, so it no longer fails.

MySQL scenario (multi-JOIN cross-reference): still broken. Example from the issue:

SELECT count(*) AS `count` FROM `Inventory` AS `Inventory`  
   LEFT JOIN ( SELECT `code` FROM `Location_info` ) AS `Location_info` ON `Location_info`.`code` = `Inventory`.`location_code`  
   LEFT JOIN ( SELECT `id` FROM `Warehose_info` ) AS `Warehose_info` ON `Warehose_info`.`id` = `Location_info`.`warehouse_id`

Location_info's subquery only selects code, but the second ON clause references Location_info.warehouse_id, causing an Unknown column error.

The fix is in AbstractSQLConfig.parseJoin (around line 6587): when collecting the subquery columns for a JOIN table, also include the targetKey of any ON clause (from any join in the list) whose target table/alias matches the current JOIN table. I'll keep PostgreSQL's SELECT * fallback as-is.

I'll open a PR with the fix and a regression test soon.

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

Navigation Menu

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

APIJSON Version/APIJSON 版本号

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

Environment/环境信息

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

Current Behavior/问题描述

Expected Behavior/期望结果

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

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

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

Description

APIJSON Version/APIJSON 版本号

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

Environment/环境信息

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

Current Behavior/问题描述

Expected Behavior/期望结果

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

Activity

wz11wz commented on Jun 26, 2025

wz11wz commented on Jun 28, 2025

TommyLemon commented on Jun 29, 2025

wz11wz commented on Jun 29, 2025

TommyLemon commented on Jun 29, 2025

TommyLemon commented on Jun 29, 2025

wz11wz commented on Jul 1, 2025

wz11wz commented on Jul 3, 2025

TommyLemon commented on Jul 5, 2025

wz11wz commented on Jul 5, 2025

TommyLemon commented on Jul 5, 2025

TommyLemon commented on Jul 5, 2025

wz11wz commented on Jul 5, 2025

TommyLemon commented on Jul 5, 2025

youdaoSu commented on Sep 22, 2026

wz11wz commented on Sep 22, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions

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