Polyphormic has-many association with separate association table

[resokou]

Your Question

I have something like this:

type MovieAmateur struct {
    ID  int   `gorm:"primary_key";autoIncrement:true;`
    Tag []Tag `gorm:"polymorphic:Movie;polymorphicValue:amatuer"`
}

type MovieProfessional struct {
    ID  int   `gorm:"primary_key";autoIncrement:true;`
    Tag []Tag `gorm:"polymorphic:Movie;polymorphicValue:professional"`
}

type Tag struct {
    ID        int    `gorm:"primary_key";autoIncrement:true;`
    MovieID   int
    MovieType string
}

This however adds two columns, movie_id and movie_type in the Tag table. So in practice it treats the entity Tag like the association table itself.

Expected answer

The table Tag in my case has a lot of extra fields, I need the association to be represented in an external table with foreign keys from Movie and Tag, e.g.

// Intermediate "bridge" table that couples together Movie* and Tag
type MovieTag struct {
    Tag       Tag `gorm:"primary_key";autoIncrement:false;`
    MovieID   int `gorm:"primary_key"`
    MovieType int `gorm:"primary_key"`
}

Is it possible with Gorm?

[resokou]

I ended up linking the associations to a new table MovieTag and then manually construct the query:

package main

import (
    "log"
    "os"
    "time"
    "fmt"

    "github.com/glebarez/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/logger"
)

type MovieAmateur struct {
    ID   int        `gorm:"primary_key";autoIncrement:true;`
    Name string
    Tags []MovieTag `gorm:"polymorphic:Movie;polymorphicValue:1000"`
}

type MovieProfessional struct {
    ID   int        `gorm:"primary_key";autoIncrement:true;`
    Name string
    Tags []MovieTag `gorm:"polymorphic:Movie;polymorphicValue:2000"`
}

type Tag struct {
    ID   int    `gorm:"primary_key";autoIncrement:true;`
    Name string
}

type MovieTag struct {
    TagID     int    `gorm:"primary_key";autoIncrement:false;`
    Tag       Tag
    MovieID   int    `gorm:"primary_key"`
    MovieType int    `gorm:"primary_key"`
}

func main() {
    dbLogger := logger.New(
        log.New(os.Stdout, "\r\n", log.LstdFlags),
        logger.Config{  // debug queries
            SlowThreshold:             time.Second,
            LogLevel:                  logger.Info,
            IgnoreRecordNotFoundError: true,
            Colorful:                  false,
        },
    )
    db, _ := gorm.Open(sqlite.Open("file::memory:?_fk=on"), &gorm.Config{Logger: dbLogger,})
    db.AutoMigrate(&Tag{}, &MovieAmateur{}, &MovieProfessional{}, &MovieTag{},)
    tag1 := Tag{Name: "Comic"}
    tag2 := Tag{Name: "Sci-Fi"}
    db.Create(&tag1)
    db.Create(&tag2)

    db.Create(&MovieAmateur{Name: "MovieA", Tags: []MovieTag{{Tag: tag1}, {Tag: tag2}},})
    db.Create(&MovieProfessional{Name: "MovieP1", Tags: []MovieTag{{Tag: tag1}, {Tag: tag2}},})
    db.Create(&MovieProfessional{Name: "MovieP2", Tags: []MovieTag{{Tag: tag2}},})

    tags := []Tag{}
    db.Table("tags").Select("*").Joins(
            "JOIN movie_tags ON tag_id==tags.id AND movie_id=1 AND movie_type=2000").Find(&tags)
    for i, tag := range tags {
        fmt.Printf("TAG %2d: %+v\n", i, tag)
    }
}

The correct combination of Movie type + Movie ID could be passed to an intermediate function (scopes?) via an Enum or by figuring it out using the reflection package I guess. But I don't know if there is an easier way to do it with GORM.

[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

[saeidee]

Why you are not having the Name field inside MovieTag? here is a similar example which is having extra fields in the polymorphic table. https://gorm.io/docs/has_many.html#Polymorphism-Association