在 golang 中使用 gorm 不在条件中 - 动态不在 select 语句的条件中

Question

我有两个数据库 Booking 和 Room。 Booking 将 roomid 作为其字段之一。我编写了一个 select 语句，它将检索到的行保存在结果变量中，如下所述。

var result models.Booking
rows, err := utils.DB.Model(&currRequest).Where("check_in BETWEEN ? AND ? AND check_out BETWEEN ? AND ?", currRequest.CheckIn, currRequest.CheckOut, currRequest.CheckIn, currRequest.CheckOut).Select("room_id").Rows()
for rows.Next() {
            utils.DB.ScanRows(rows, &result)
            fmt.Println(result.RoomID)
        }

现在，我的 result.roomid 的 roomid 值满足预订表中的 select 语句。

我的结果变量可能有多个房间 id 值。我可以通过循环结果变量来检索 roomid 值。现在我必须检查名为 Room 的主房间数据库并获取那些不在结果结构中的房间 ID。通过使用下面的语句，我只能访问 result.roomid 中的第一个值，因此 not in 条件仅考虑 result.roomid 中的第一个值。如何对 result.roomid 中的所有值执行 not in 条件？

rows, err := utils.DB.Model(&models.Room{}).Not(result.RoomID).Select("room_id").Rows()

完整代码：

package handlers

import (
    "encoding/json"
    "fmt"
    "net/http"
    "server/models"
    "server/utils"
    "strings"
)

func AvailableRoomsHandler(w http.ResponseWriter, r *http.Request) {

    currRequest := &models.Booking{}
    err := json.NewDecoder(r.Body).Decode(currRequest)
    //check if a valid request has been sent from front end
    if err != nil {
        //fmt.Println(err)
        var resp = map[string]interface{}{"status": false, "message": "Invalid json request"}
        json.NewEncoder(w).Encode(resp)
        return
    }

    noOfRoomsOccupied := 0
    var notinrooms string

    // Use GORM API build SQL
    //check if any rooms are available which havent been booked yet in the requested check-in and check-out dates
    var result models.Booking
    rows, err := utils.DB.Model(&currRequest).Where("check_in BETWEEN ? AND ? AND check_out BETWEEN ? AND ?", currRequest.CheckIn, currRequest.CheckOut, currRequest.CheckIn, currRequest.CheckOut).Select("room_id").Rows()
    if err != nil {
        json.NewEncoder(w).Encode(err)
        fmt.Print("error occured in select statement")
        return
    } else {
        defer rows.Close()
        for rows.Next() {
            noOfRoomsOccupied = noOfRoomsOccupied + 1
            utils.DB.ScanRows(rows, &result)
            fmt.Println(result.RoomID)
            notinrooms = notinrooms + result.RoomID + ","
        }
        notinrooms = strings.TrimRight(notinrooms, ",")
        fmt.Println(notinrooms)
        //calculate the number of rooms in the database
        //rows, err := utils.DB.Model(&models.Room{}).Select("room_id").Rows()
        res := utils.DB.Find(&models.Room{})
        rowcount := res.RowsAffected
        fmt.Println(rowcount)
        if noOfRoomsOccupied == int(rowcount) {
            var resp = map[string]interface{}{"status": false, "message": "no rooms available in the specified time period"}
            json.NewEncoder(w).Encode(resp)
            return
        } else {
            noOfRooms := (currRequest.NoOfGuests + currRequest.NoOfChildren) / 2
            if (currRequest.NoOfGuests+currRequest.NoOfChildren)%2 == 1 {
                noOfRooms = noOfRooms + 1
            }
            if int(noOfRooms) < int(rowcount)-noOfRoomsOccupied {
                fmt.Println("number of rooms to book : ", noOfRooms)
                //assign rooms if available
                var roomids models.Room
                //rows, err := utils.DB.Model(&models.Room{}).Not(result.RoomID).Select("room_id").Rows()
                fmt.Println("rooms that can be booked")
                rows, err := utils.DB.Model(&models.Room{}).Not(result.RoomID).Select("room_id").Rows()
                //rows, err := utils.DB.Model(&models.Room{}).Not([]string{notinrooms}).Select("room_id").Rows()
                //map[string]interface{}{"name": []string{"jinzhu", "jinzhu 2"}}
                if err != nil {
                    json.NewEncoder(w).Encode(err)
                    fmt.Print("error occured in select statement to get room ids to assign")
                    return
                } else {
                    defer rows.Close()
                    for rows.Next() {
                        noOfRoomsOccupied = noOfRoomsOccupied + 1
                        utils.DB.ScanRows(rows, &roomids)
                        fmt.Println(roomids.RoomID)
                    }
                }
                var success = map[string]interface{}{"message": "Select statement worked well"}
                json.NewEncoder(w).Encode(success)
                return
            }
        }
    }

}

当我执行 result.roomid 时，它只给出第一个房间 id，并仅删除上面 select 语句中的那个房间 id。如何消除在房间表数据中的预订表中找到的所有房间 ID？

我尝试拆分 result.roomid 值并尝试形成一个字符串并将其放在 select 语句中，但这不起作用。我尝试循环遍历每个 result.roomid 并在语句中运行 not ，但这没有任何意义。

Answer 1

使用此代码：

var result models.Booking
rows, err := utils.DB.Model(&currRequest).Where("check_in BETWEEN ? AND ? AND check_out BETWEEN ? AND ?", currRequest.CheckIn, currRequest.CheckOut, currRequest.CheckIn, currRequest.CheckOut).Select("room_id").Rows()
if err != nil {
        json.NewEncoder(w).Encode(err)
        fmt.Print("error occured in select statement")
        return
} else {
        defer rows.Close()
        for rows.Next() {
            noOfRoomsOccupied = noOfRoomsOccupied + 1
            utils.DB.ScanRows(rows, &result)
            //rest of the code
        }
}

您只能从结果集中获取可能多行中的一行。要获取所有行并提取它们的值，您应该使用[]models.Booking。

result := []models.Booking{}
rows, err := utils.DB.Model(&currRequest).Where("check_in BETWEEN ? AND ? AND check_out BETWEEN ? AND ?", currRequest.CheckIn, currRequest.CheckOut, currRequest.CheckIn, currRequest.CheckOut).Select("room_id").Rows()
if err != nil {
        json.NewEncoder(w).Encode(err)
        fmt.Print("error occured in select statement")
        return
} else {
        defer rows.Close()
        for rows.Next() {
            var b models.Booking
            noOfRoomsOccupied = noOfRoomsOccupied + 1
            utils.DB.ScanRows(rows, &b)
            result = append(result, b)
            //rest of the code
        }
}

但是，由于您只需要 roomid ，因此可以使用 []uint 使其变得更容易（假设 roomid 的类型为 uint ）。

result := []uint{}
rows, err := utils.DB.Model(&currRequest).Where("check_in BETWEEN ? AND ? AND check_out BETWEEN ? AND ?", currRequest.CheckIn, currRequest.CheckOut, currRequest.CheckIn, currRequest.CheckOut).Select("room_id").Rows()
if err != nil {
        json.NewEncoder(w).Encode(err)
        fmt.Print("error occured in select statement")
        return
} else {
        defer rows.Close()
        for rows.Next() {
            var rid uint
            noOfRoomsOccupied = noOfRoomsOccupied + 1
            utils.DB.ScanRows(rows, &rid)
            result = append(result, rid)
            //rest of the code
        }
}

由于 result 的类型为 []uint，因此将其与 Not 函数一起使用会更容易（根据 文档）：

rows, err := utils.DB.Model(&models.Room{}).Not(result).Select("room_id").Rows()