[QUESTION] - Looking for some help for many to many in gorm v2

[sujit-baniya]

Your Question

Hi all, 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

Please suggest any opinion

The document you expected this should be explained

Expected answer

I wanted to link Operations with feature based on primary key other than "ID"

[tzq0301]

I think that gorm will consider id as a special field while concat 'feature' and 'feature_id' to 'feature_feature_id' as a non-special field.

I found some code at line 228 of schema/relationship.go joinFieldName := strings.Title(schema.Name) + ownField.Name that may be related to this bug.

I don't know if this is a violation of "convention", but I don't think GORM should have this problem (instead of letting users follow GORM and use id).

Looking forward to someone's help!

package main

import (
	"github.com/stretchr/testify/assert"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"testing"
)

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

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

func TestGORM(t *testing.T) {
	a := assert.New(t)

	db, err := gorm.Open(sqlite.Open(":memory:"), &gorm.Config{})
	a.NoError(err)

	db = db.Exec(`
CREATE TABLE features (
	feature_id INTEGER PRIMARY KEY,
	name VARCHAR(20),
	slug VARCHAR(100)
);

CREATE TABLE operations (
	operation_id INTEGER PRIMARY KEY,
	name VARCHAR(20),
	slug VARCHAR(100)
);

CREATE TABLE feature_operations(
    feature_id INTEGER,
    operation_id INTEGER,
    FOREIGN KEY (feature_id) REFERENCES features(feature_id),
    FOREIGN KEY (operation_id) REFERENCES operations(operation_id)
);

INSERT INTO features(feature_id, name, slug) 
VALUES (0, "feature_name0", "feature_slug0"),
       (1, "feature_name1", "feature_slug1");

INSERT INTO operations(operation_id, name, slug) 
VALUES (0, "operation_name0", "operation_slug0"),
       (1, "operation_name1", "operation_slug1"),
       (2, "operation_name2", "operation_slug2");

INSERT INTO feature_operations(feature_id, operation_id) 
VALUES (0, 0),
       (0, 1),
       (1, 1),
       (1, 2);
`)

	var features []Feature
	a.NoError(db.Debug().Model(&Feature{}).Preload("Operations").Find(&features).Error)
	t.Log(features)
}

[sujit-baniya]

@tzq0301 Exactly the place of issue. Temporarily, I'm using the following changes to work.

https://github.com/sujit-baniya/gorm/commit/d9c8b15855a757f05a9e812fef2bca35b10a99c7

[sujit-baniya]

Also, users should not be forced to use id or ID as primary key

[github-actions[bot]]

This issue has been automatically marked as stale because it has been open 360 days with no activity. Remove stale label or comment or this will be closed in 180 days