Using 'relational' queries with GORM/GO

Question

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

Emin Laletovic · Accepted Answer · 2022-08-01 18:16:29Z

1

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

edited Aug 1, 2022 at 18:16

answered Jul 29, 2022 at 20:16

Emin Laletovic

4,3541 gold badge17 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

qknight Over a year ago

thank you so much for your answer. Everything makes sense what you write. The only thing which did not work yet is the third task, I get: [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)

Emin Laletovic Over a year ago

I've updated that code by using a subquery instead of a Joins function. The other approach would be to just write an update SQL query and execute it with an Exec function.

qknight Over a year ago

I get ---3----------------------------------- 2022/08/02 09:19:19 /app/main.go:82 ERROR: missing FROM-clause entry for table "ccu" (SQLSTATE 42P01) [0.444ms] [rows:0] UPDATE "credit_cards" SET "number"='23422342',"updated_at"='2022-08-02 09:19:19.464' 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) from your new query.

qknight Over a year ago

I've updated the question and linked this repo now: github.com/qknight/gorm-examples

Collectives™ on Stack Overflow

Using 'relational' queries with GORM/GO

The question

The code

console output

Solution

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

The question

The code

console output

Solution

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related