【where条件中有函数和比较运算】

[CarsonYue]

Description

SELECT count(1)
FROM  report_contract_assessment_terms
WHERE GREATEST(contract_start_date, '2024-03-01') <= LEAST(contract_end_date, '2024-04-01');

以上sql应该如何实现？

[TommyLemon]

左侧的函数可以用 @key 映射，右侧的需要用 @raw https://github.com/Tencent/APIJSON/issues/627

{
    "Report_contract_assessment_terms": {
        "@column": "count(1)",
        "least_date<=": "LEAST(contract_end_date,'2024-04-01')", // 后端 DemoSQLConfig.RAW_MAP 配置下
        "@key": "least_date:GREATEST(contract_start_date,'2024-03-01')",
        "@raw": "least_date<="
    }
}

[TommyLemon]

如果希望不配置 RAW_MAP，需要和 key{} , @column, @having 一样自动解析 SQL 函数/表达式，调用 parseSQLExpression https://github.com/Tencent/APIJSON/blob/master/APIJSONORM/src/main/java/apijson/orm/AbstractSQLConfig.java#L2037-L2055