Gorm add multiple slices in inserting in a many to many

user_11636383 0
Viewed 1360

I'm new using go and gorm. I'm trying to insert many values in one SQL query.

I wrote this query to add multiple conversations to a user:

relationUserConversation := make([][]uint, len(users))
    
for i, v := range users {
    relationUserConversation[i] = []uint{conversation.ID, v}
}
    
result = r.db.Debug().Exec(
            "INSERT INTO `user_has_conversations` (`user_has_conversations`.`conversation_id`, `user_has_conversations`.`user_id`) VALUES ?",
            relationUserConversation, // If i do this it works relationUserConversation[0], relationUserConversation[1]
           // The issue is because the query has this value "VALUES ((35,1),(35,2))", but should be to work (35,1),(35,2)
        )

I also tried to add it directly with the conversation that would be what I would like to do, but I'm having issue trying to add the relation with the many to many because instead of creating the relation between the user and the conversation it tries to add the user.

My conversation model:

type Conversation struct {
    ID       uint    `gorm:"primarykey"`
    Users    []*User `gorm:"many2many:user_has_conversations;"`
    Messages []ConversationMessage
}

Would be great if i could create a new conversation with the related users in one query instead of creating first the conversation and after the relation to the users.

go-gorm go sql
1 Answers

Below is a minimum working example using the Gorm Appends method (see documentation here) to create a many to many association between two (or more) models. Hopefully you can adapt this to your use case.

package main

import (
    "fmt"
    "gorm.io/driver/sqlite"
    "gorm.io/gorm"
)

type User struct {
    gorm.Model
    Name     string
    Conversations []Conversation `gorm:"many2many:user_conversations;"`
}

type Conversation struct {
    gorm.Model
    Name string
    Users []*User `gorm:"many2many:user_conversations;"`
}

func main() {

    db, err := gorm.Open(sqlite.Open("many2many.db"), &gorm.Config{})
    if err != nil {
        panic("failed to connect database")
    }

    // Migrate the schema
    err = db.AutoMigrate(&User{}, &Conversation{})
    if err != nil {
        fmt.Print(err)
    }

    userOne := User{
        Name: "User One",
    }
    userTwo := User{
        Name: "User Two",
    }
    // Create users
    db.Create(&userOne)
    db.Create(&userTwo)

    conversation := Conversation{
        Name: "Conversation One",
    }
    // Create conversation
    db.Create(&conversation)


    // Append users
    err = db.Model(&conversation).Association("Users").Append([]User{userOne, userTwo})

    if err != nil {
        fmt.Print(err)
    }


    for _, convUser := range conversation.Users {
        fmt.Println("Hello I am in the conversation: " + convUser.Name)
    }

    // Clean up database
    db.Delete(&userOne)
    db.Delete(&userTwo)
    db.Delete(&conversation)
}

Number of queries

If you enable Debug() on Gorm:

err = db.Debug().Model(&conversation).Association("Users").Append([]User{userOne, userTwo})

It shows this:

[0.144ms] [rows:2] INSERT INTO `user_conversations`
 (`conversation_id`,`user_id`) VALUES (8,15),(8,16) ON CONFLICT DO NOTHING

The Values part is correct (what you were trying to do manually) and achieved using the ORM.

user_46475590
Related