avatar

Catalog
Go gorm 原生SQL查询 命名参数方式 Query Raw SQL with Scan , Named parameters

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

go
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
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
}
Author: Qin
Link: https://qinhaolei.com/posts/49704/
Copyright Notice: All articles in this blog are licensed under CC BY-NC-SA 4.0 unless stating additionally.
Reward
  • WechatPay
    WechatPay
  • AliPay
    AliPay
  • Bitcoin
    Bitcoin

Comment