The question
The documentation on GORM is a little sparse and we don't get some of the concepts working - the 'has many'. https://gorm.io/docs/has_many.html
I've added my conceptual issues as FIXME in the code.
If anyone knows how to solve these, I'd like to add this to the GORM documentation on later.
The code
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")
}
console output
---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
Solution
There is still one issue with the problem 3 and we've added a problem 7 as well which was not in the original list.
We also are using CASCADE now as it seems to make modifying the data easier:
`gorm:"ForeignKey:UserID;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
Here is the source:
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;constraint:OnUpdate:CASCADE,OnDelete:CASCADE;"`
}
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"})
db.Create(&CreditCardUser{Name: "mrGone", CreditCards: []CreditCard{{Number: "77777777777", Bank: "BICrupt"}}})
//////////// 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{}
db.Joins("INNER JOIN credit_card_users ccu ON ccu.id = credit_cards.user_id").Where("ccu.name = ? AND credit_cards.bank = ?", "mrFlux", "FinFisher").Find(&finFisherCreditCards)
fmt.Println("mrFlux's FinFisher card(s) are (request 1): ", finFisherCreditCards)
// alternatively using preload for the same result
mrFluxUser := CreditCardUser{}
db.Preload("CreditCards", "bank = ?", "FinFisher").First(&mrFluxUser, "name =?", "mrFlux")
fmt.Println("mrFlux's FinFisher card(s) are (request 2): ", mrFluxUser.CreditCards)
//////////// 3 - update wrong creditcard number of the sirTuxedo's Bankxter card number from 2342 to 23422342 ////////////
fmt.Println("---3-----------------------------------")
// FIXME does not work
op := db.Model(&CreditCard{}).Joins("INNER JOIN credit_card_users ccu ON ccu.id = credit_cards.user_id").Where("ccu.name = ? AND credit_cards.bank = ?", "sirTuxedo", "Bankxter").Update("number", "23422342")
// FIXME
// [0.365ms] [rows:0] UPDATE "credit_cards" SET "number"='23422342',"updated_at"='2022-08-01 13:40:52.819' WHERE (ccu.name = 'sirTuxedo' AND credit_cards.bank = 'Bankxter') AND "credit_cards"."deleted_at" IS NULL
// Couldn't update credit card number: ERROR: missing FROM-clause entry for table "ccu" (SQLSTATE 42P01)
if op.Error != nil {
fmt.Println("Couldn't update credit card number: ", op.Error)
}
//////////// 4 - list all user(s) with a credit card from 'FinFisher' Bank ////////////
fmt.Println("---4-----------------------------------")
extractUserNamesFromUsers := func(users *[]CreditCardUser) string {
s := ""
for i := 0; i < len(*users); i {
if i > 0 {
s = ", "
}
s = (*users)[i].Name
}
return s
}
users := []CreditCardUser{}
db.Joins("INNER JOIN credit_cards cc ON cc.user_id = credit_card_users.id").Where("cc.bank = ?", "FinFisher").Find(&users)
fmt.Println(" all user(s) with a credit card from 'FinFisher' Bank: ", extractUserNamesFromUsers(&users))
//////////// 5 - drop all fraudy creditcards from related uses where the card number is 999999999, no matter the bank name ////////////
fmt.Println("---5-----------------------------------")
// basically delete sirTuxedo and missFraudinger
db.Where("number = ?", "999999999").Unscoped().Delete(&CreditCard{})
//////////// 6 - add a creditcard to happyUser ////////////
fmt.Println("---6-----------------------------------")
happyUser := CreditCardUser{}
db.Model(&CreditCardUser{}).Where("name=?", "happyUser").First(&happyUser)
happyUser.CreditCards = []CreditCard{{Number: "666666666666", Bank: "happyBank"}}
db.Session(&gorm.Session{FullSaveAssociations: true}).Updates(&happyUser)
creditCardsOfHappyUser := CreditCardUser{}
db.Preload("CreditCards").Where("name=?", "happyUser").First(&creditCardsOfHappyUser)
fmt.Println("The credit cards of HappyUser are: ", creditCardsOfHappyUser.CreditCards)
//////////// 7 - append another entry in the the creditcard(s) of happyUser ////////////
fmt.Println("---7-----------------------------------")
happyUser2 := CreditCardUser{}
db.Transaction(func(tx *gorm.DB) error {
tx.Model(&CreditCardUser{}).Where("name=?", "happyUser").First(&happyUser2)
happyUser2.CreditCards = append(happyUser2.CreditCards, CreditCard{Number: "666666666666", Bank: "happyhappyBank"})
tx.Session(&gorm.Session{FullSaveAssociations: true}).Updates(&happyUser2)
// return nil will commit the whole transaction
return nil
})
creditCardsOfHappyUser2 := CreditCardUser{}
db.Preload("CreditCards").Where("name=?", "happyUser").First(&creditCardsOfHappyUser2)
fmt.Println("The credit cards of HappyUser are: ", creditCardsOfHappyUser2.CreditCards)
//////////// 8 - delete user with associated creditcard(s) ////////////
fmt.Println("---8-----------------------------------")
db.Unscoped().Delete(&CreditCardUser{}, "name = ?", "mrGone")
fmt.Println("/About to create a relational object")
}
CodePudding user response：
go-gorm documentation provides just enough examples to show how something works. Personally, I do agree that there could be more examples, but at the same time, there are a lot of combinations to cover in examples.
One important thing to remember is always to try to construct a SQL query by using go-gorm functions. Try to work out how a raw SQL query should do the job, then convert it into go-gorm functions.
second task
You can either load just the cards with a Joins function
finFisherCreditCards := []CreditCard{}
db.Joins("INNER JOIN credit_card_users ccu ON ccu.id = credit_cards.user_id").Where("ccu.name = ?", "mrFlux").Find(&finFisherCreditCards)
or load the user with just the FinFisher credit card records
user := CreditCardUser{}
db.Preload("CreditCards", "bank = ?", "FinFisher").First(&user, "name =?", "mrFlux")
third task
Use Joins and Where functions to get the object you want, then you can update just one field.
db.Model(&CreditCard{}).Joins("INNER JOIN credit_card_users ccu ON ccu.id = credit_cards.user_id").Where("ccu.name = ? AND credit_cards.bank = ?", "sirTuxedo", "Bankxter").Update("number", "23422342")
fourth task
If you just want to find users, use Joins, Where, and Find functions. You can use Preload here if you want all of their credit cards with them.
users := []CreditCardUsers{}
db.Joins("INNER JOIN credit_cards cc ON cc.user_id = credit_card_users.id").Where("cc.bank = ?", "FinFisher").Find(&users)
fifth task
Even though there is a relationship, I presume that here you don't care about users, but just want to delete the fraudulent cards. This is then just a conditional delete operation (Where and Delete functions).
db.Where("number = ?", "9999999999").Delete(&CreditCard{})