gorm执行原生sql

目录

• 1.gorm修改单个字段
• 2.gorm普通查询
• 3.gorm连表操作，where
• 4.gorm执行原生sql
• 5.sql分组排序后取第一个
• 6.gorm执行原生update的sql

1.gorm修改单个字段

func (o *object)Update(i *Info) bool {
	err := o.Db.Model(Info{}).Where("cid = ?",i.Cid).Update(map[string]interface{}{
		"online":i.Online,
	}).Error
	if err != nil {
		o.Log.Error(err)
		return false
	}
	return true
}

2.gorm普通查询

func (o *object) QueryInfoByUid(uid int) (user User) {
	if uid <= 0 {
		o.Log.Error(qutil.ParamError)
		return
	}
	//err := o.Db.Model(User{}).Where(User{Uid: uid}).Find(&user).Error //方式一
  err := o.Db.Model(User{}).Where("id = ?",id).Find(&user).Error  // 方式二
	if err != nil {
		o.Log.Error(err)
	}
	return
}

type User struct {
	id       int               `json:"id"`
	Name     string          `json:"name"`
}

func (User) TableName() string {
	return "users"
}

3.gorm连表操作，where

import (
	"database/sql"

	"github.com/jinzhu/gorm"

)

//实现接口
type dictionary struct {
	db   *sql.DB
}

var this *dictionary
var DB *gorm.DB

func (*dictionary) SearchTestDemo(page, pageSize int, name, age, tag string, typeStatus int) (info []user, total int) {
	if pageSize >= 20 {
		pageSize = 20
	}
	if page <= 0 {
		page = 1
	}
	db := DB.Table("t_user as tsg").Select(
		"tsg.name as name," +
			"tsn.age as age," +
			"tw.ts as ts," +
			"tw.status as status")
	if name != "" {
		db = db.Where("tsg.object_no = ?", name)
	}

	if age != "" {
		db = db.Where("tsg.ship_bill_no = ?", age)
	}

	if tag != "" {
		db = db.Where("tsg.waybill_no = ?", tag)
	}
	db = db.Where("tsg.ship_type = ?", typeStatus)
	db = db.Joins("inner join t_tag on t_tag.id = tsg.id").Joins("inner join user on tw.waybill_no=tsg.waybill_no")
	db = db.Order("ship_ts").Limit(pageSize).Offset((page - 1) * pageSize)
	err := db.Count(&total).Scan(&info).Error
	if err != nil {
		app.Log.Errorf("GetByage err:%s", err)
		return
	}

	return
}

4.gorm执行原生sql

方式一：Db.Raw

type BattAlarm struct {
		Bid string `json:"bid"`
		T   int    `json:"t"`
	}

func (o *object) QueryAlarmByBids(cid string, bids []string) (out []system.BattAlarm) {
	if len(bids) <= 0 {
		o.Log.Error(qutil.ParamError)
		return
	}
	in := "('" + strings.Join(bids, "','") + "')"
	sqlstr := fmt.Sprintf(`
	select 
extra::json->>'bid' as bid,
type as t
from alarm_log where id in  (
SELECT max(id) as bid FROM alarm_log WHERE cid = '%s' and extra::json->>'bid' in %s  GROUP BY extra::json->>'bid'
)
`, cid, in)
	err := o.Db.Raw(sqlstr).Scan(&out)
	if err != nil {
		o.Log.Error(err)
		return
	}
	return
}

方式二：Db.QueryRow

func (o object) QueryInfo(FBillNo string) *Info {
	r := new(Info)
	var qstr string
	switch {
	case len(FBillNo) > 0:
		qstr += fmt.Sprintf(" and \"FBillNo\" = '%s'", FBillNo)
	default:
		o.Log.Error("invalid param")
		return nil
	}
	sqlstr := `
	select
	coalesce("FBillNo",'') as FBillNo,
	coalesce(fshortnumber,'') as fshortnumber,
	coalesce(fname,'') as fname,
	coalesce(fchexing,'') as fchexing,
	coalesce(fys,'') as fys
	from 表名
	where 1=1
	`
	sqlstr += qstr
	err := o.DbRo.QueryRow(sqlstr).Scan(&r.FBillNo, &r.FShortNumber, &r.FName, &r.FCheXing, &r.Fys)
	if err != nil {
		o.Log.Errorf("param=%s,sql=%s,err=%v", FBillNo, sqlstr, err)
		return nil
	}
	return r
}

方式3：Db.Raw,rows.next

// 原生 SQL
rows, err := db.Raw("select name, age, email from users where name = ?", "jinzhu").Rows() // (*sql.Rows, error)
defer rows.Close()
for rows.Next() {
  ...
  rows.Scan(&name, &age, &email)
  ...
}

5.sql分组排序后取第一个

SELECT
	*
FROM
	( SELECT *, ROW_NUMBER() OVER ( PARTITION BY user_id ORDER BY updated_at DESC ) AS rn FROM user_table WHERE user_id IN ( 2,3,4 ) and status = 1 ) t 
WHERE
	t.rn = 1

6.gorm执行原生update的sql

sql:=`update xxx`
err = dbCtx.Exec(sql).Error
	if err != nil {
		a.log.WithContext(ctx).Error(err)
		return err
	}