Go gorm 原生SQL查询 命名参数方式 笔记

gorm docs https://gorm.io/docs/sql_builder.html

type MerchBill struct {
	ID        uint           `gorm:"primarykey"` // 主键ID
	CreatedAt time.Time      // 创建时间
	UpdatedAt time.Time      // 更新时间
	DeletedAt gorm.DeletedAt `gorm:"index" json:"-"` // 删除时间
	Applyno  string   `json:"applyno" form:"applyno" gorm:"column:applyno;comment:订单号;type:varchar(255);"`
}
func getList(info req.Mer) (err error, list interface{}, total int64) { 
	db := *gorm.DB.Model(&MerchBill{})
	var MerchBills []MerchBill
	var MerchBillsCount []MerchBill

	sqlHeader := "SELECT bill.*,mer.mobile,mer.merchname  "
	sqlHeaderCount := "SELECT COUNT(*) AS countnum "
	sql := " FROM billtable AS bill left join merch_user as mer on mer.id = bill.merchid WHERE 1 "

	var sqlParam struct {
		Applyno       string
		Status        *int
		Applytype     *int
		Applyrealname string
		Merchname     string
		Limit         int
		Offset        int
	}
	if info.Applyno != "" {
		sql += " AND `bill`.applyno = @Applyno "
		sqlParam.Applyno = info.Applyno
	}
	if info.Status != nil {
		sql += " AND `bill`.status = @Status "
		sqlParam.Status = info.Status
	}
	if info.Applytype != nil {
		sql += " AND `bill`.applytype = @Applytype "
		sqlParam.Applytype = info.Applytype
	}
	if info.Applyrealname != "" {
		sql += " AND `bill`.applyrealname = @Applyrealname "
		sqlParam.Applyrealname = info.Applyrealname
	}
	if info.Merchname != "" {
		sql += " AND `mer`.merchname. LIKE @Merchname "
		sqlParam.Merchname = info.Merchname
	}

	sqlFooter := " ORDER BY id DESC LIMIT @Offset,@Limit"
	sqlParam.Limit = info.PageSize
	sqlParam.Offset = info.PageSize * (info.Page - 1)
	err = db.Raw(sqlHeader+sql+sqlFooter, sqlParam).Scan(&MerchBills).Error
	err = db.Raw(sqlHeaderCount+sql, sqlParam).Scan(&MerchBillsCount).Error
	total = MerchBillsCount[0].Countnum
	return err, MerchBills, total
}