如何获取"select 1"数据

Question

我试图确定数据库中是否存在值，所以我选择使用Select 1 From table，但如何获取数据？我想检查一下两个人是好朋友，这是我的型号

type Buddy struct {
ID      uint64 `gorm:"primarykey"`
UserID  uint32
BuddyID uint32
}

这是我尝试过的gorm-sql

// select 1 from buddies where user_id=? and buddy_id=?
type Result struct {
Exist uint32
}
exist := &Result{}
orm.DB.Debug().Table("buddies").Raw("SELECT 1 as exist FROM buddies WHERE user_id = ? and buddy_id = ?", userID, buddyID).Scan(exist)

但我什么都没得到。存在

Answer 1

这里有一个函数，它可以满足您的要求，并以最优化的方式

package main

import (
"gorm.io/driver/sqlite"
"gorm.io/gorm"
"log"
)

type Buddy struct {
ID      uint64 `gorm:"primarykey"`
UserID  uint32
BuddyID uint32
}
func main() {
db, err := gorm.Open(sqlite.Open("test.db"), &gorm.Config{})
if err != nil {
panic("failed to connect database")
}

// Migrate the schema
db.AutoMigrate(&Buddy{})

// Create
db.Create(&Buddy{UserID: 1, BuddyID: 2})
db.Create(&Buddy{UserID: 2, BuddyID: 1})
db.Create(&Buddy{UserID: 3, BuddyID: 1})

var exists bool
db.Raw("select case count(1) when 0 then false else true end FROM buddies WHERE user_id = ? and buddy_id = ?", 1, 2).Scan(&exists)
log.Print(exists)
db.Raw("select case count(1) when 0 then false else true end FROM buddies WHERE user_id = ? and buddy_id = ?", 3, 2).Scan(&exists)
log.Print(exists)
}

你可以检查一下是否工作

Answer 2

queryResult := db.Where(fmt.Sprintf("user_id = %d and buddy_id = %d", 1, 2).Find(&Buddy)
if queryResult.Error == nil {
//you can also check queryResult.RowsAffected > 0
result = queryResult.Statement.Model //get the returned data
} else {
//do something with queryResult.Error
}

Answer 3

func IsExist[T any](model T, conds ...interface{}) bool {
var val int
err :=db.Model(&model).Where(conds[0],conds[1:]...).Select("1").First(&val).Error
return nil != err
}