编程并不是一个机械性的工作，而是需要有思考，有创新的工作，语法是固定的，但解决问题的思路则是依靠人的思维，这就需要我们坚持学习和更新自己的知识。今天golang学习网就整理分享《使用 GORM/GO 的“关系”查询》，文章讲解的知识点主要包括，如果你对Golang方面的知识点感兴趣，就不要错过golang学习网，在这可以对大家的知识积累有所帮助，助力开发能力的提升。

问题

gorm 的文档有点稀疏，我们没有得到一些有效的概念 - “有很多”。 https://gorm.io/docs/has_many.html

我已将我的概念问题添加为代码中的 fixme。

如果有人知道如何解决这些问题，我想稍后将其添加到 gorm 文档中。

代码

package main

import (
    "fmt"
    "gorm.io/driver/postgres"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
    "log"
)

// user has many creditcards, userid is the foreign key
type creditcarduser struct {
    gorm.model
    name        string
    creditcards []creditcard `gorm:"foreignkey:userid"`
}

type creditcard struct {
    gorm.model
    number string
    bank   string
    userid uint
}

func main() {
    //https://gorm.io/docs/connecting_to_the_database.html
    dsn := fmt.sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable",
        "localhost", 5432, "postgres", "52436c7a7d852f6aee3658e4150adf9782a5e418", "serverprovisioning")
    db, err := gorm.open(postgres.open(dsn), &gorm.config{
        //logger: logruslogger.debug(), // fixme https://github.com/onrik/gorm-logrus

        logger: logger.default.logmode(logger.info), // fixme expose this with environment variable db_log_level info/warn/error/trace
    })

    if err != nil {
        log.fatal(err)
    }

    db.migrator().droptable(&creditcarduser{}, &creditcard{})
    db.automigrate(&creditcarduser{})
    db.automigrate(&creditcard{})

    // https://github.com/harranali/gorm-relationships-examples/tree/main/has-many
    // https://gist.github.com/jtbonhomme/ff6db22b8dcac7dd9349e26bad002fb1
    fmt.println("about to create a relational object")
    // insert new record
    db.create(&creditcarduser{name: "mrflux", creditcards: []creditcard{{number: "1234567898", bank: "finfisher"}, {number: "345657881", bank: "maxedout limited"}}})
    db.create(&creditcarduser{name: "sirtuxedo", creditcards: []creditcard{{number: "999999999", bank: "finfisher"}, {number: "2342", bank: "bankxter"}}})
    db.create(&creditcarduser{name: "missfraudinger", creditcards: []creditcard{{number: "999999999", bank: "finfisher"}}})
    db.create(&creditcarduser{name: "happyuser"})

    //////////// 1 - get all credit card records of user 'mrflux' ////////////
    fmt.println("---1-----------------------------------")
    creditcardsofflux := []creditcarduser{}
    db.preload("creditcards").where("name=?", "mrflux").find(&creditcardsofflux)
    fmt.println("the credit cards of mrflux are: ", creditcardsofflux)

    //////////// 2 - get all finfisher credit card records of user 'mrflux' ////////////
    fmt.println("---2-----------------------------------")
    finfishercreditcards := []creditcard{}
    // fixme this does not work
    db.preload("creditcarduser").preload("creditcard").find(&finfishercreditcards)
    fmt.println("mrflux's finfisher card(s) are: ", finfishercreditcards)

    //////////// 3 - update wrong creditcard number of the sirtuxedo's bankxter card number from 2342 to 23422342 ////////////
    fmt.println("---3-----------------------------------")
    // fixme no clue yet

    //////////// 4 -  list all user(s) with a credit card from 'finfisher' bank ////////////
    fmt.println("---4-----------------------------------")
    // fixme no clue yet

    //////////// 5 - drop all credit card relations for all users with a fraudy 999999999 card number from any bank ////////////
    fmt.println("---5-----------------------------------")
    // fixme no clue yet


    fmt.println("/about to create a relational object")
}

控制台输出

---1-----------------------------------

2022/07/29 19:35:25 c:/users/joschie/golandprojects/awesomeproject/main.go:55
[1.000ms] [rows:2] select * from "credit_cards" where "credit_cards"."user_id" = 1 and "credit_cards"."deleted_at" is null

2022/07/29 19:35:25 c:/users/joschie/golandprojects/awesomeproject/main.go:55
[2.999ms] [rows:1] select * from "credit_card_users" where name='mrflux' and "credit_card_users"."deleted_at" is null
the credit cards of mrflux are: [{{1 2022-07-29 19:35:25.935651 +0200 cest 2022-07-29 19:35:25.935651 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} mrflux [{{1 2022-07-29 19:35:25.937363 +0200 cest 2022-07-29 19:35:25.937363
+0200 cest {0001-01-01 00:00:00 +0000 utc false}} 1234567898 finfisher 1} {{2 2022-07-29 19:35:25.937363 +0200 cest 2022-07-29 19:35:25.937363 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} 345657881 maxedout limited 1}]}]
---2-----------------------------------

2022/07/29 19:35:25 c:/users/joschie/golandprojects/awesomeproject/main.go:62 creditcard: unsupported relations for schema creditcard; creditcarduser: unsupported relations for schema creditcard
[1.000ms] [rows:5] select * from "credit_cards" where "credit_cards"."deleted_at" is null
mrflux's finfisher card(s) are: [{{1 2022-07-29 19:35:25.937363 +0200 cest 2022-07-29 19:35:25.937363 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} 1234567898 finfisher 1} {{2 2022-07-29 19:35:25.937363 +0200 cest 2022-07-29
19:35:25.937363 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} 345657881 maxedout limited 1} {{3 2022-07-29 19:35:25.942696 +0200 cest 2022-07-29 19:35:25.942696 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} 999999999 fin
fisher 2} {{4 2022-07-29 19:35:25.942696 +0200 cest 2022-07-29 19:35:25.942696 +0200 cest {0001-01-01 00:00:00 +0000 utc false}} 2342 bankxter 2} {{5 2022-07-29 19:35:25.946875 +0200 cest 2022-07-29 19:35:25.946875 +0200 cest {00
01-01-01 00:00:00 +0000 utc false}} 999999999 finfisher 3}]
---3-----------------------------------
---4-----------------------------------
---5-----------------------------------
---6-----------------------------------
/about to create a relational object

process finished with the exit code 0

解决方案

问题 3 仍然存在一个问题，我们还添加了问题 7，该问题不在原始列表中。

我们现在也在使用 cascade，因为它似乎使修改数据变得更容易：

`gorm:"ForeignKey:UserID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`

来源如下：

https://github.com/qknight/gorm-examples

正确答案

go-gorm 文档提供了足够的示例来展示某些内容是如何工作的。就我个人而言，我确实同意可以有更多示例，但与此同时，示例中需要涵盖很多组合。

要记住的一件重要事情是始终尝试使用 go-gorm 函数构建 sql 查询。尝试弄清楚原始 sql 查询应如何完成这项工作，然后将其转换为 go-gorm 函数。

第二个任务

您可以使用 joins 函数仅加载卡片

finfishercreditcards := []creditcard{}  
db.joins("inner join credit_card_users ccu on ccu.id = credit_cards.user_id").where("ccu.name = ?", "mrflux").find(&finfishercreditcards)

或仅向用户加载 finfisher 信用卡记录

user := creditcarduser{}
db.preload("creditcards", "bank = ?", "finfisher").first(&user, "name =?", "mrflux")

第三个任务

使用子查询（为sirtuxedo 选择用户id）和where函数来获取你想要的对象，然后就可以update just one field。

db.model(&creditcard{}).where("name = ? and user_id = ?", "bankxter", db.model(&creditcarduser{}).where("name = ?", "sirtuxedo").select("id")).update("number", "23422342")

第四个任务

如果您只想查找用户，请使用 joins、where 和 find 函数。如果您想要他们的所有信用卡，您可以在此处使用 preload。

users := []creditcardusers{}
db.joins("inner join credit_cards cc on cc.user_id = credit_card_users.id").where("cc.bank = ?", "finfisher").find(&users)

第五个任务

虽然有关系，但我推测你这里并不关心用户，只是想删除盗卡。这只是一个条件删除操作（where 和 delete 函数）。

db.Where("number = ?", "9999999999").Delete(&CreditCard{})

以上就是《使用 GORM/GO 的“关系”查询》的详细内容，更多关于的资料请关注golang学习网公众号！