gomany-to-manygo-gorm

many to many in gorm v2 error on foreign key


I'm finding it difficult to define many to many relationship using Gorm in following cases

features(feature_id, name, slug)
operations(operation_id, name, slug)
feature_operations(feature_id, operation_id)

type Feature struct {
    FeatureID  int64       `gorm:"primaryKey;column:feature_id" json:"feature_id"`
    Name       string      `validate:"required" json:"name"`
    Slug       string      `json:"slug"`
    Status     string      `json:"status"`
    Operations []Operation `gorm:"many2many:feature_operations;foreignKey:feature_id"`
    appModels.BaseModel
}

When using feature_id, I get error

column feature_operations.feature_feature_id does not exist

When using id, I get error

invalid foreign key: id


Solution

  • Looks like you are not using the convention that gorm suggests where you name your primary key columns just id

    so in your case your foreignKey should be the name of the field and you also need to use References to specify column that you want to reference. See the example here: https://gorm.io/docs/many_to_many.html#Override-Foreign-Key

    What you need is this:

    type Feature struct {
        FeatureID  int64 `gorm:"primaryKey;column:feature_id"`
        Name       string
        Slug       string
        Operations []Operation `gorm:"many2many:feature_operations;foreignKey:FeatureID;References:OperationID"`
    }
    
    type Operation struct {
        OperationID int64 `gorm:"primaryKey;column:operation_id"`
        Name        string
        Slug        string
    }
    

    After this the join table will be FEATURE_OPERATIONS with two columns FEATURE_FEATURE_ID AND OPERATION_OPERATION_ID

    If you dont like the redundant column names then you need to use the two additional attributes joinForeignKey and joinReferences to choose your own names for the columns like so:

    gorm:"many2many:feature_operations;foreignKey:FeatureID;joinForeignKey:FeatureID;References:OperationID;joinReferences:OperationID"

    All this extra work is needed because your primary keys are FEATURE_ID and OPERATION_ID instead of just ID If you can rename the column to follow the convention, you will notice life is much easier with gorm