表自连接属性冲突 #125

jinspire · 2020-03-10T02:45:35Z

环境信息

系统: mac os
JDK: 1.8.0_17
数据库: MySQL 5.7
APIJSON: 3.3.0

问题描述
1.有一张表如下：

CREATE TABLE identity_certificate(idbigint(20) unsigned NOT NULL AUTO_INCREMENT,mobilevarchar(32) NOT NULL DEFAULT '' COMMENT '手机号',namevarchar(32) NOT NULL DEFAULT '' COMMENT '姓名',idcardvarchar(32) NOT NULL DEFAULT '' COMMENT '身份证号',idcard_positive_urlvarchar(500) NOT NULL DEFAULT '' COMMENT '身份证正面图片',idcard_negative_urlvarchar(500) NOT NULL DEFAULT '' COMMENT '身份证反面图片',status int(4) NOT NULL DEFAULT '0' COMMENT '认证状态(0-待审核;1-审核通过;2-已拒绝;3-二级审核中;)', PRIMARY KEY (id) USING BTREE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='实名认证表';

2.现在需要查询表中的最大值，sql形式如下：
select r1.* from identity_certificate r1,(select max(id) as id from identity_certificate
group by idcard) r2 where r1.id=r2.id

3.使用apijson请求
{ "[]": { 'query': 2, "page": 0, "count": 0, "identity_certificate": { "@schema": "auth", "@column": "max(id):abcid", "@group": 'idcard', }, "identity_certificate": { "@schema": "auth", "id@": "/identity_certificate/abcid", }, }, "total@": "/[]/total" }

错误信息
请求变成了，第一个属性被覆盖，导致请求变成了无结果的请求。
{"[]":{"query":2,"page":0,"count":0,"identity_certificate":{"@schema":"auth","id@":"/identity_certificate/abcid"}},"total@":"/[]/total"}

结果：
{code: 200, msg: "success"}

有类似于sql改名机制解决这个问题么？如何解决？

jinspire · 2020-04-08T08:14:36Z

你说的
"@column": "count(DISTINCT userId):total"
//替代 query:2 查 total，因为 max(id) 函数导致查总数报错，这是一个刚发现的 bug，
这个具体是啥意思？有问题的写法是怎样的？

TommyLemon · 2020-04-19T10:41:47Z

@jinspire

{
    "[]": {
        "query": 2, //这样会报错
        "page": 0,
        "count": 0,
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    }
}

返回

{
    "[]": {
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    },
    "ok": false,
    "code": 406,
    "msg": "HEAD请求: 预编译模式下 @column:value 中 value里面用 , 分割的每一项 column:alias 中 column 必须是1个单词！如果有alias，则alias也必须为1个单词！并且不要有多余的空格！",
    "sql:generate|cache|execute|maxExecute": "1|0|0|200",
    "depth:count|max": "3|5",
    "time:start|duration|end": "1587292937756|2|1587292937758"
}

需要改写 AbstractSQLConfig.getColumnString 737 行

return SQL.count(column != null && column.size() == 1 ? getKey(Pair.parseEntry(column.get(0), true).getKey()) : "*");

当 column.size() == 1 时，可能里面包含一个 SQL 函数，需要改成

if (column != null && column.size() == 1) {
  String key = getKey(Pair.parseEntry(column.get(0), true).getKey());
  if (key != null && key.indexOf("(") < 0 && key.indexOf(")") < 0) {
    return SQL.count(key);
  }
}

return SQL.count("*");

https://github.com/APIJSON/APIJSON/blob/master/APIJSON-Java-Server/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java

我暂时没时间，你可以改完后发个 Pull Request 贡献代码，感谢

TommyLemon · 2020-04-19T11:02:32Z

不小心删了我之前的回答：

{
    "Comment:total": {
        "@column": "count(DISTINCT userId):total"  //替代 query:2 查 total，因为 max(id) 函数导致查总数报错，这是一个刚发现的 bug
    },
    "[]": {
        "page": 0,
        "count": 0,
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    }
}

返回

{
    "Comment:total": {
        "total": 27
    },
    "[]": [
        {
            "Comment:max": {
                "abcid": 1532057419100
            },
            "Comment": {
                "id": 1532057419100,
                "toId": 0,
                "userId": 38710,
                "momentId": 1531969818357,
                "date": "2018-07-20 11:30:19.0",
                "content": "可以加上标题哦"
            }
        },
        {
            "Comment:max": {
                "abcid": 1557754765785
            },
            "Comment": {
                "id": 1557754765785,
                "toId": 0,
                "userId": 70793,
                "momentId": 1557754680146,
                "date": "2019-05-13 21:39:25.0",
                "content": "链接发下"
            }
        }
    ],
    "ok": true,
    "code": 200,
    "msg": "success",
    "sql:generate|cache|execute|maxExecute": "5|1|4|200",
    "depth:count|max": "3|5",
    "time:start|duration|end": "1587294180415|24|1587294180439"
}

jasoncool520 · 2020-07-03T07:08:58Z

@jinspire

{
    "[]": {
        "query": 2, //这样会报错
        "page": 0,
        "count": 0,
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    }
}

返回

{
    "[]": {
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    },
    "ok": false,
    "code": 406,
    "msg": "HEAD请求: 预编译模式下 @column:value 中 value里面用 , 分割的每一项 column:alias 中 column 必须是1个单词！如果有alias，则alias也必须为1个单词！并且不要有多余的空格！",
    "sql:generate|cache|execute|maxExecute": "1|0|0|200",
    "depth:count|max": "3|5",
    "time:start|duration|end": "1587292937756|2|1587292937758"
}

需要改写 AbstractSQLConfig.getColumnString 737 行

return SQL.count(column != null && column.size() == 1 ? getKey(Pair.parseEntry(column.get(0), true).getKey()) : "*");

当 column.size() == 1 时，可能里面包含一个 SQL 函数，需要改成

if (column != null && column.size() == 1) {
  String key = getKey(Pair.parseEntry(column.get(0), true).getKey());
  if (key != null && key.indexOf("(") < 0 && key.indexOf(")") < 0) {
    return SQL.count(key);
  }
}

return SQL.count("*");

https://github.com/APIJSON/APIJSON/blob/master/APIJSON-Java-Server/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java

我暂时没时间，你可以改完后发个 Pull Request 贡献代码，感谢

这个的修改方式应该就是直接 return SQL.count("*") 吧，然后去掉上边 origin 的 isName验证，既然HEAD是查询总记录数，返回max(id),也没有意义，像其他非聚合函数(比如一些转换函数cast等)的话，返回原函数也不对，所以这里直接返回SQL.count("*")就ok吧

TommyLemon · 2020-07-11T05:44:28Z

@jinspire
{
    "[]": {
        "query": 2, //这样会报错
        "page": 0,
        "count": 0,
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    }
}
返回
{
    "[]": {
        "Comment:max": {
            "@column": "max(id):abcid",
            "@group": "userId"
        },
        "Comment": {
            "id@": "/Comment:max/abcid"
        }
    },
    "ok": false,
    "code": 406,
    "msg": "HEAD请求: 预编译模式下 @column:value 中 value里面用 , 分割的每一项 column:alias 中 column 必须是1个单词！如果有alias，则alias也必须为1个单词！并且不要有多余的空格！",
    "sql:generate|cache|execute|maxExecute": "1|0|0|200",
    "depth:count|max": "3|5",
    "time:start|duration|end": "1587292937756|2|1587292937758"
}
需要改写 AbstractSQLConfig.getColumnString 737 行
return SQL.count(column != null && column.size() == 1 ? getKey(Pair.parseEntry(column.get(0), true).getKey()) : "*");
当 column.size() == 1 时，可能里面包含一个 SQL 函数，需要改成
if (column != null && column.size() == 1) {
  String key = getKey(Pair.parseEntry(column.get(0), true).getKey());
  if (key != null && key.indexOf("(") < 0 && key.indexOf(")") < 0) {
    return SQL.count(key);
  }
}

return SQL.count("*");
https://github.com/APIJSON/APIJSON/blob/master/APIJSON-Java-Server/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java
我暂时没时间，你可以改完后发个 Pull Request 贡献代码，感谢
这个的修改方式应该就是直接 return SQL.count("") 吧，然后去掉上边 origin 的 isName验证，既然HEAD是查询总记录数，返回max(id),也没有意义，像其他非聚合函数(比如一些转换函数cast等)的话，返回原函数也不对，所以这里直接返回SQL.count("")就ok吧

对

TommyLemon added the bug label Mar 13, 2020

APIJSON / APIJSON

表自连接属性冲突 #125

表自连接属性冲突 #125

jinspire commented Mar 10, 2020 •

edited

jinspire commented Apr 8, 2020

TommyLemon commented Apr 19, 2020 •

edited

TommyLemon commented Apr 19, 2020 •

edited

jasoncool520 commented Jul 3, 2020 •

edited

TommyLemon commented Jul 11, 2020

APIJSON / APIJSON

Join GitHub today

表自连接属性冲突 #125

表自连接属性冲突 #125

Comments

jinspire commented Mar 10, 2020 • edited

jinspire commented Apr 8, 2020

TommyLemon commented Apr 19, 2020 • edited

TommyLemon commented Apr 19, 2020 • edited

jasoncool520 commented Jul 3, 2020 • edited

TommyLemon commented Jul 11, 2020

Essential cookies

Always active

Analytics cookies

jinspire commented Mar 10, 2020 •

edited

TommyLemon commented Apr 19, 2020 •

edited

TommyLemon commented Apr 19, 2020 •

edited

jasoncool520 commented Jul 3, 2020 •

edited