[联表查询问题] 联表查询时副表无数据

[uedkkk]

Description

您好，多有打扰，在使用apiJson的过程中遇到了一点问题，翻阅了issus也试了很多一直没有解决
问题：联表查询的时候副表的数据无法返回
apijson配置

{
    "apijson":{
        "method":"GET",
        "[]":{
            "TABLEONE":{
                "@column":"id;relation_id:relationId",
                "@datasource":"testSource"
            },
            "TABLETWO":{
                "@column":"id;qrcode_url:url",
                "@datasource":"testSource",
                "id@":"/TABLEONE/relation_id"
            },
            "count":"#{[request][pageSize]}",
            "page":"#{[request][pageNum]-1}",
            "query":"2"
        },
        "pageInfo@":"/[]/info"
    },
    "parserMethod":"GET"
}   

返回数据

{
    "message": "success",
    "code": 200,
    "data": {
        "list": [
            {
                "relationId": 173,
                "id": 1
            }
        ],
        "pageNum": 1,
        "pageSize": 1,
        "total": 49
    },
    "success": true
}

查看sql日志

SELECT `id`,`relation_id` AS `relationId` FROM `tableone` LIMIT 1

---

之后查看同事的文档进行了语法上的改进，但是依旧无法返回正常结果
改进后的apijson

{
    "apijson":{
        "method":"GET",
        "[]":{
            "join":"</TABLETWO/id@",
                "TABLEONE":{
                    "@column":"id;relation_id:relationId",
                    "@datasource":"testSource"
                },
                "TABLETWO":{
                    "@column":"id;qrcode_url:url",
                    "@datasource":"testSource",
                    "id@":"/TABLEONE/relation_id"
                },
                "count":"#{[request][pageSize]}",
                "page":"#{[request][pageNum]-1}",
                "query":"2"
        },
        "pageInfo@":"/[]/info",
    },
    "parserMethod":"GET"
}    

改进后的返回结果

{
    "message": "success",
    "code": 200,
    "data": {
        "list": [
            {
                "relationId": 173,
                "id": 1
            }
        ],
        "pageNum": 1,
        "pageSize": 1,
        "total": 49
    },
    "success": true
}

改进后的sql

SELECT `TABLEONE`.`id`,
       `TABLEONE`.`relation_id` AS `relationId`,
       `TABLETWO`.*
FROM `tableone` AS `TABLEONE`
         LEFT JOIN (SELECT `id`, `qrcode_url` AS `url` FROM `tabletwo`) AS `TABLETWO`
                   ON `TABLETWO`.`id` = `TABLEONE`.`invite_qrcode_relation_id`
LIMIT 1

---

求问为什么会查不出来副表的数据，sql是可以查出来的

[TommyLemon]

引用赋值路径错误，TABLEONE/relation_id 对应值为 null，应该是 TABLEONE/relationId。
如果用 SQL JOIN，则 SQL 中必须用数据库表中的字段 relation_id，所以 @column: "relation_id:relationId" 会导致无法兼顾，需要额外加上 relation_id，即 @column: "relation_id;relation_id:relationId"。
一般不建议把数据库原字段名在 APIJSON 解析时改成另一个，如果只是蛇形命名改为驼峰命名，可以用 format: true 且设置 JSONResponse.IS_FORMAT_UNDERLINE = true

https://github.com/Tencent/APIJSON/blob/master/Document.md#3.2

其它引用赋值问题见 常见问题 20
#36

[uedkkk]

感谢🙏