xorm构建复杂sql
2022/5/25 2:20:29
本文主要是介绍xorm构建复杂sql,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
==背景==
找了很久,xorm正经的构建复杂sql的方式,记录一下
==SQL样例==
SELECT t1.id, t1.rules_unique, t1.device_id, t1.point_id, t1.point_judge, t1.point_value, t1.alarm_time, t1.alarm_value, t1.create_time, t1.update_time FROM `pulse_alarm_data` AS `t1` INNER JOIN ( SELECT rules_unique, MAX(id) AS max_id FROM pulse_alarm_data WHERE DATE_FORMAT(alarm_time, '%Y-%m-%d') >= "2022-05-24" AND DATE_FORMAT(alarm_time, '%Y-%m-%d') <= "2022-05-24" GROUP BY rules_unique ) pulse_alarm_data ON pulse_alarm_data.max_id = t1.id ORDER BY `t1`.`alarm_time` DESC LIMIT 10
==构建思路==
xorm builder
==代码样例==
package daos import ( "github.com/go-xorm/xorm" "log" "pulse-service/src/business/models" "pulse-service/src/business/request" "pulse-service/src/component" "pulse-service/src/utils" "xorm.io/builder" ) type AlarmDataDao struct { *component.Orm } const fieldAlarmData = ` t1.id, t1.rules_unique, t1.device_id, t1.point_id, t1.point_judge, t1.point_value, t1.alarm_time, t1.alarm_value, t1.create_time, t1.update_time ` func (add *AlarmDataDao) SelectAlarmDataByRulesByPage(param *request.GetAlarmData) ([]models.AlarmData, int64, error) { pageNum := param.PageNum pageSize := param.PageSize start := (pageNum - 1) * pageSize session := add.Engine.Select(fieldAlarmData) defer session.Close() makeAlarmQuerySql(session, param) session.Limit(pageSize, start) list := make([]models.AlarmData, 0) total, err := session.Desc("t1.alarm_time").FindAndCount(&list) if err != nil { log.Println(err.Error()) return nil, total, err } return list, total, nil } func makeAlarmQuerySql(session *xorm.Session, param *request.GetAlarmData) { cond := builder.NewCond() if param != nil && utils.IsNotEmpty(param.RulesUnique) { cond = cond.And(builder.Eq{"rules_unique": param.RulesUnique}) } if param != nil && utils.IsNotEmpty(param.DeviceId) { cond = cond.And(builder.Eq{"device_id": param.DeviceId}) } if param != nil && utils.IsNotEmpty(param.FromTime) { cond = cond.And(builder.Gte{"DATE_FORMAT(alarm_time, '%Y-%m-%d')": param.FromTime}) } if param != nil && utils.IsNotEmpty(param.ToTime) { cond = cond.And(builder.Lte{"DATE_FORMAT(alarm_time, '%Y-%m-%d')": param.ToTime}) } sub := builder.Select("rules_unique", "MAX(id) AS max_id") sub.From("pulse_alarm_data") sub.Where(cond) sub.GroupBy("rules_unique") session.Table("pulse_alarm_data").Alias("t1") session.Join("INNER", sub, "pulse_alarm_data.max_id = t1.id") }
--End--
这篇关于xorm构建复杂sql的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-12-21《鸿蒙HarmonyOS应用开发从入门到精通(第2版)》简介
- 2024-12-21后台管理系统开发教程:新手入门全指南
- 2024-12-21后台开发教程:新手入门及实战指南
- 2024-12-21后台综合解决方案教程:新手入门指南
- 2024-12-21接口模块封装教程:新手必备指南
- 2024-12-21请求动作封装教程:新手必看指南
- 2024-12-21RBAC的权限教程:从入门到实践
- 2024-12-21登录鉴权实战:新手入门教程
- 2024-12-21动态权限实战入门指南
- 2024-12-21功能权限实战:新手入门指南