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--