How to get "select 1" data

Question

I'm trying to trying to determine existence of a value in the database, so I choose to use Select 1 From table, but how can I get the data? I want to check two people are buddies, and here are my models

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

this is the gorm sql I've tried

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

but I didn't get anything in exist.Exist

passing table in raw query not required; Also capture the error and what query was printed in console? — Shubham Srivastava, Dec 17 '20 at 04:51
console printed " [1.994ms] [rows:-] SELECT 1 as exist FROM buddies WHERE user_id = 1000039 and buddy_id = 1000079 " — ducklu, Dec 17 '20 at 05:12
maybe you just have too many pointers to the `exist` variable. Either use `exist := Result{}` and `.Scan(&exist)` or `exist := &Result{}` and `.Scan(exist)`. — Emin Laletovic, Dec 17 '20 at 08:22
@ducklu The issue is you are passing &exists to scan as a pointer to pointer exists is already a pointer and you can directly call .Scan(exist) and always check for error — Shubham Srivastava, Dec 17 '20 at 08:28
sorry, I copy the wrong code, I fix it and try it again, exist still get nothing — ducklu, Dec 17 '20 at 08:51
how does it work if `Exist` field is of `bool` type, instead of `uint32`? — Emin Laletovic, Dec 17 '20 at 08:55
@EminLaletovic it still get nothing(I mean, I give it initial value true, the logic should give me false, but it's still true) — ducklu, Dec 17 '20 at 09:05

janmbaco · Accepted Answer · 2020-12-19T09:23:01.390

0

Here you have a function that does what you ask and in the most optimal way

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

you can check that it worked

edited Dec 19 '20 at 09:23

answered Dec 17 '20 at 06:12

janmbaco

340
4
11

I know I can use this to check two people are buddies, but this use to ```SELECT *```, I heard determine existence of a value maybe select 1 will be more effient than select * – ducklu Dec 17 '20 at 06:31
you can use a sql statement as I mentioned – janmbaco Dec 17 '20 at 06:46
I mean select 1, maybe this question can show my meaning: https://stackoverflow.com/questions/7171041/what-does-it-mean-by-select-1-from-table, and the second code you write also can't change the exists(it's a bit of like the code I write), because count(1) create a new column that the model struct not exist – ducklu Dec 17 '20 at 08:13
If you want to verify that two people are friends, you can do either of the two options, or you are asking the wrong question. – janmbaco Dec 18 '20 at 06:15
I know how to write sql to judge two people are friends, but when you create a new column that the model struct not exist by gorm, problem occurred. Just like the second code that you write, the exists won't get the right answer – ducklu Dec 18 '20 at 06:55
I have built the script and I have seen that it works perfectly, I do not understand what is the problem that you pose – janmbaco Dec 19 '20 at 11:27
I finally know what happened, Because I use gorm.io/gorm v1.20.2, it won't get the right answer, it fix in the future version – ducklu Dec 22 '20 at 04:07

score 0 · Answer 2 · answered Dec 21 '20 at 20:18

    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
    }

score 0 · Answer 3 · answered Aug 22 '22 at 23:23

0

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
}

answered Aug 22 '22 at 23:23

codegeek

1

How to get "select 1" data

3 Answers3