Gorm Bulk & Nested firstOrCreate With Association

Your Question

Hi there! I'm working on optimizing a worker that is responsible for importing records into the DB from an API call. Let me outline some of the context:

For the sake of simplicity, let's say the main struct I'm working with is called Interaction. An Interaction has a few relations - the important ones are "belongs-to" (each linked by a FK stored on the interactions table).

Our current implementation of this importer is quite expensive. For each relation on Interaction, we are firstOrCreate-ing that record, and once we've processed all of the relations we then attempt to firstOrCreate the Interaction. All of these firstOrCreate operations are based on the unique indexes of a given table (e.g. email, address, etc.).

I've tried playing around with the onConflict clauses (using both DoNothing and trying to update columns), but so far have been unsuccessful. Any duplicates inserted cause the entire bulk creation to error out. Is there a way to do what I need, or will my approach need to change? I've included a (hopefully) thorough demo below.

The document you expected this should be explained

My current implementation looks something like:

import "gorm.io/gorm"

type InteractionBeforeImport struct {
    xOtherKey string
    yOtherKey string
    zOtherKey string
}

type Interaction struct {
    x   X
    xId uint
    y   Y
    yId uint
    z   Z
    zId uint
}

type X struct {
    id       uint
    otherKey string
}

type Y struct {
    id       uint
    otherKey string
}

type Z struct {
    id       uint
    otherKey string
}

func importInteractions(input []*InteractionBeforeImport) {
    var db *gorm.DB
    for _, interaction := range input {

        var existing *Interaction
        db.Where("x_other_key = ?", interaction.xOtherKey).Where("y_other_key = ?", interaction.yOtherKey).Where("z_other_key = ?", interaction.zOtherKey).First(existing)
        if existing != nil {
            continue
        }

        var x *X
        db.Where("other_key = ?", interaction.xOtherKey).First(x)
        if x == nil {
            x = &X{otherKey: interaction.xOtherKey}
            db.Create(x)
        }

        var y *Y
        db.Where("other_key = ?", interaction.yOtherKey).First(y)
        if y == nil {
            y := &Y{otherKey: interaction.yOtherKey}
            db.Create(y)
        }

        var z *Z
        db.Where("other_key = ?", interaction.zOtherKey).First(z)
        if z == nil {
            z := &Z{otherKey: interaction.zOtherKey}
            db.Create(z)
        }

        db.Create(&Interaction{
            x: *x,
            y: *y,
            z: *z,
        })
    }
}

Expected answer

I would love to be able to do something like so, where if a conflict is found, the nested association just uses whatever value already exists:

import (
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type InteractionBeforeImport struct {
    xOtherKey string
    yOtherKey string
    zOtherKey string
}

type Interaction struct {
    x   X
    xId uint
    y   Y
    yId uint
    z   Z
    zId uint
}

type X struct {
    id       uint
    otherKey string
}

type Y struct {
    id       uint
    otherKey string
}

type Z struct {
    id       uint
    otherKey string
}

func importInteractions(input []*InteractionBeforeImport) {
    var db *gorm.DB
    var interactions []*Interaction
    for _, interaction := range input {
        interactions = append(interactions, &Interaction{
            x: X{otherKey: interaction.xOtherKey},
            y: Y{otherKey: interaction.yOtherKey},
            z: Z{otherKey: interaction.zOtherKey},
        })

    }

    db.Clauses(clause.OnConflict{
        FirstOrCreate: true,
    })

    db.Create(interactions)
}