Using 'relational' queries with GORM/GO

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:

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

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 a subquery (to select a user ID for sirTuxedo ) and Where functions to get the object you want, then you can update just one field.

db.Model(&CreditCard{}).Where("name = ? AND user_id = ?", "Bankxter", db.Model(&CreditCardUser{}).Where("name = ?", "sirTuxedo").Select("id")).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{})