Gorm Many2Many fails to create a join table record for an existing associated record

https://github.com/go-gorm/playground/pull/604

Description

Having problems with many2many, I went back to the basics and slightly modified the playground to fix the documentation first example. And it's not a success. The example being

type User struct {
  gorm.Model
  Languages []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
  gorm.Model
  Name string
}

In a few words, if Name is not annotated to be unique, multiple identical Language records are created. If Name is annotated, then gorm when asked for an association of an existing language will try to create a join table entry those language_id = 0. This triggers a constraint violation.

More details are here: https://stackoverflow.com/questions/76358210/how-to-make-the-many2many-gorm-documentation-example-work

Comment From: a631807682

I think this is as expected, it does violate the constraint when you use unique Index.

Comment From: diligiant

@a631807682 IMHO it doesn't, let me expand here: - first gorm tries to INSERT a Language record with an ON CONFLICT/ON DUPLICATE that doesn't duplicate it (the db wouldn't let it anyway) nor returns an error. - then, and this is where things go awry, gorm creates the join table record, but it doesn't set the language_id to the id of the existing Language but to zero.

My guess is that despite the INSERT asking for the id (RETURNING id [sqlite&postgres] or UPDATE id=id [mysql]) [this works fine when a Language is inserted], it doesn't get the id of the existing record.

The only plausible explanation I found is in postgres doc:

The syntax of the RETURNING list is identical to that of the output list of SELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an ON CONFLICT DO UPDATE ... WHERE clause condition was not satisfied, the row will not be returned.

As the record is not updated (this would explain the id=id for mysql but I encounter the same problem anyway), id isn't returned.

postgres log

[3.064ms] [rows:1] INSERT INTO "languages" ("created_at","updated_at","deleted_at","code","name") VALUES ('…','…',NULL,'en-us','English') ON CONFLICT DO NOTHING RETURNING "id"
[1.508ms] [rows:1] INSERT INTO "user_speaks" ("user_id","language_id") VALUES (1,1) ON CONFLICT DO NOTHING
…
[0.396ms] [rows:0] INSERT INTO "languages" ("created_at","updated_at","deleted_at","code","name") VALUES ('…','…',NULL,'en-us','English') ON CONFLICT DO NOTHING RETURNING "id"
[0.808ms] [rows:0] INSERT INTO "user_speaks" ("user_id","language_id") VALUES (2,0) ON CONFLICT DO NOTHING

Notice VALUES(2,0) on the last line.

Comment From: a631807682

Seems related to https://github.com/go-gorm/gorm/issues/6047

func TestGORM(t *testing.T) {
    tx := DB.Clauses(clause.OnConflict{DoNothing: true}).Session(&gorm.Session{})

    l1 := Language{Code: "ZH", Name: "Chinese"}
    tx.Create(&l1)

    l2 := &Language{Code: "ZH", Name: "Chinese"}
    tx.Create(&l2)

    if l1.ID != l2.ID {
        t.Errorf("l1.ID != l2.ID: %v != %v", l1.ID, l2.ID)
    }
}

Comment From: diligiant

Yes and there doesn't seem to be a "db-side solution"; this isn't authoritative but Erwin Brandstetter's answer here implies so how-to-use-returning-with-on-conflict-in-postgresql.

The playground and the gorm tests I found so far in the repo do many2many without an id; Code is the key so this "works" (ie the caller knows the value of Code).

Down the stackoverflow post, someone suggests to insert then select. I'm doing it the other way around, creating Language first with FirstOrCreate then updating User. That's not ideal but it does the job.