Chiu
  • 231

go xorm 复杂的查询

项目中好多个表都有需要使用多字段的查询,想依此封装函数供各个模型调用,感觉好难实现啊。

请大家帮我看看我定义的这种请求的数据结构有没有问题

不知道大家在go中使用什么orm,还是直接使用原生。我用的是xorm

给个思路呗~~~

数据库模型go

type TestTable struct {
    int Id
    string Name
    int Created
    int Version
}

请求查询的数据json

{
    "limit":10,
    "offset":10,
    "asc":["id","name"],
    "desc":["created","updated"],
    "and":{
        "id":{// `id` > 33
            "cond":"GT",
            "values":[33]
        },
        "name":{// `name` LIKE '%test%'
            "cond":"LIKE",
            "values":["test"]
        },
        "created":{// `created` BETWEEN 1570000000 AND 1580000000
            "cond":"BETWEEN",
            "values":[1570000000,1580000000]
        },
        "version":{// `version` IN (1,2,3,4,5) 下面or条件同理
            "cond":"IN",
            "values":[1,2,3,4,5]
        }
    },
    "or":{
        "id":{
            "cond":"LTE",
            "values":[32]
        },
        "name":{
            "cond":"LIKE",
            "values":["demo"]
        },
        "created":{
            "cond":"BETWEEN",
            "values":[1570000000,1589999999]
        },
        "version":{
            "cond":"IN",
            "values":[6,7,8,9,10]
        }
    }
}

想要生成的查询语句mysql

SELECT * FROM `test_table` WHERE
(`id` > 33 AND `name` LIKE '%test%' AND `created` BETWEEN 1570000000 AND 1580000000 AND `version` IN (1,2,3,4,5))
OR
(`id` <= 32 AND `name` LIKE '%demo%' AND `created` BETWEEN 1570000000 AND 1589999999 AND `version` IN (6,7,8,9,10))
ORDER BY `id`,`name` ASC , `created`,`updated` DESC LIMIT 10,10;
阅读 332
评论 更新于 2019-11-11
    1 个回答
    评论 赞赏 2019-11-11
      撰写回答

      登录后参与交流、获取后续更新提醒