Gorm polymorphic association in gorm (belongs to)

Describe the feature

I would like to request support for belongs to polymorphic association in GORM. Currently, GORM provides support for many common association types, such as many-to-many, but lacks built-in support for belongs to polymorphic association.

Belongs to polymorphic association is a commonly used pattern where a model can belong to multiple other models, with the association determined by both the ID and the type of the associated model. This pattern allows for more flexibility and reusability in the database schema.

Motivation

type User struct {
    gorm.Model
    Name     string
    RoleID   uint
    RoleType string
    Role     interface{} `gorm:"-"`
}

type Role struct {
    gorm.Model
    Name string
}

type Admin struct {
    Role
    Level string
}

type Member struct {
    Role
    Rank string
}

i want to be able to preload Member or Admin into Role on User Table.

Using Raw Queries ?

I manually tried using raw queries to join the tables but it gets tricky and complex when you have a more complex Model with nested Polymorphic Model and Gorm doesn't allow you to Scan data Into an Interface

What am i missing ?

Related Issues

https://github.com/go-gorm/gorm/issues/3877

Comment From: TheDhejavu

Another slightly related issue

Example

type Content struct {
    gorm.Model
    Criteria []Criteria  `gorm:"foreignKey:ContentID;"`
}

type Criteria struct {
    gorm.Model
    ContentID int
    CriteriaInfoType     string
    CriteriaInfoID   uint
    CriteriaInfo     interface{} `gorm:"-"`
}

type CriteriaA struct {
    gorm.Model
    Name string
    Criteria  *Criteria       `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaA"`
}

type CriteriaB struct {
    gorm.Model
    Name string
    Criteria  *Criteria       `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaB"`
}

The Content struct has a slice of Criteria, and I can preload the Criteria data using GORM's Preload function. However, the challenge lies in parsing CriteriaA and CriteriaB into the CriteriaInfo field. Currently, I need to manually loop through the data and retrieve the necessary information causing N+1 problem. I'm wondering if there's a way to utilize a single query with GORM to preload the CriteriaInfo as well.

### I don't want to do this

var content Content
db.Preload("Criteria.Criteria").First(&content)

for i := range content.Criteria {
    criteria := content.Criteria[i]
    switch criteria.CriteriaInfoType {
    case "CriteriaA":
        var criteriaA CriteriaA
        db.Model(&criteriaA).Where("id = ?", criteria.CriteriaInfoID).First(&criteriaA)
        criteria.CriteriaInfo = criteriaA
    case "CriteriaB":
        var criteriaB CriteriaB
        db.Model(&criteriaB).Where("id = ?", criteria.CriteriaInfoID).First(&criteriaB)
        criteria.CriteriaInfo = criteriaB
    }
}

This will become slow if we need to perform more complex queries.

maybe this ?

var content Content
db.Preload("Criteria").Preload("Criteria.CriteriaInfo").First(&content)

i love gorm but it's becoming extremely hard to use when dealing with complex queries, What am i missing ?

Comment From: TheDhejavu

Cc @jinzhu

Comment From: TheDhejavu

Took a look at this once again.

Idea ?

type Content struct {
    gorm.Model
    Criteria []Criteria  `gorm:"foreignKey:ContentID;"`
}

type Criteria struct {
    gorm.Model
    ContentID int
    CriteriaInfoType     string
    CriteriaInfoID   uint
    CriteriaA     CriteriaA `gorm:"joins:criteria_info_id:id, polymorpic"`
    CriteriaB    CriteriaB `gorm:"joins:criteria_info_id:id, polymorpic"`
}

type CriteriaA struct {
    gorm.Model
    Name string
    Criteria  *Criteria       `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaA"`
}

type CriteriaB struct {
    gorm.Model
    Name string
    Criteria  *Criteria       `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaB"`
}

Query ?

var content Content
db.Preload("Criteria").Preload("Criteria.CriteriaA").Preload("Criteria.CriteriaB").First(&content)

@jinzhu @a631807682

Is there something i am missing in the doc. Is this currently achievable ?

Comment From: a631807682

This seems to be implemented (nested preload), needs to investigate. cc @black-06

Comment From: a631807682

@TheDhejavu I think this works fine, is that what you want?

type Content struct {
    gorm.Model
    Criteria []Criteria `gorm:"foreignKey:ContentID;"`
}

type Criteria struct {
    gorm.Model
    ContentID        int
    CriteriaInfoType string
    CriteriaInfoID   uint
    CriteriaA        CriteriaA `gorm:"foreignKey:ID"`
    CriteriaB        CriteriaB `gorm:"foreignKey:ID"`
}

type CriteriaA struct {
    gorm.Model
    Name     string
    Criteria *Criteria `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaA"`
}

type CriteriaB struct {
    gorm.Model
    Name     string
    Criteria *Criteria `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaB"`
}

func TestGORM(t *testing.T) {
    DB.Migrator().DropTable(&Content{}, &Criteria{}, &CriteriaA{}, &CriteriaB{})
    err := DB.Migrator().AutoMigrate(&Content{}, &Criteria{}, &CriteriaA{}, &CriteriaB{})
    if err != nil {
        t.Fatal(err)
    }

    DB.Create(&Content{
        Criteria: []Criteria{{CriteriaA: CriteriaA{Name: "A"}, CriteriaB: CriteriaB{Name: "B"}}},
    })

    var content Content
    DB.Preload("Criteria").Preload("Criteria.CriteriaA").Preload("Criteria.CriteriaB").First(&content)
}

Comment From: TheDhejavu

@a631807682 no. the goal is to join Criteria WHERE criteria.criteria_info_id =. criteria_a.id and Preload the data into CriteriaA OR WHERE criteria.criteria_info_id =. criteria_b.id and Preload it into CriteriaB

Comment From: a631807682

This is what you want, right? If not, please provide a reproduction link, indicate what result you want, and what is the current result.

type Content struct {
    gorm.Model
    Criteria []Criteria `gorm:"foreignKey:ContentID;"`
}

type Criteria struct {
    gorm.Model
    ContentID        int
    CriteriaInfoType string
    CriteriaInfoID   uint
    CriteriaA        CriteriaA `gorm:"foreignKey:ID;references:CriteriaInfoID"`
    CriteriaB        CriteriaB `gorm:"foreignKey:ID;references:CriteriaInfoID"`
}

type CriteriaA struct {
    gorm.Model
    Name     string
    Criteria *Criteria `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaA"`
}

type CriteriaB struct {
    gorm.Model
    Name     string
    Criteria *Criteria `gorm:"polymorphic:CriteriaInfo;polymorphicValue:CriteriaB"`
}

func TestGORM(t *testing.T) {
    DB.Migrator().DropTable(&Content{}, &Criteria{}, &CriteriaA{}, &CriteriaB{})
    err := DB.Migrator().AutoMigrate(&Content{}, &Criteria{}, &CriteriaA{}, &CriteriaB{})
    if err != nil {
        t.Fatal(err)
    }

    DB.Create(&CriteriaA{Name: "A_1"}) // CriteriaInfoID == 1
    DB.Create(&CriteriaB{Name: "B_1"}) // CriteriaInfoID == 1
    DB.Create(&CriteriaB{Name: "B_2"}) // CriteriaInfoID == 2

    DB.Create(&Content{
        Criteria: []Criteria{{CriteriaInfoID: 1}, {CriteriaInfoID: 2}},
    })

    var content Content
    DB.Preload("Criteria").Preload("Criteria.CriteriaA").Preload("Criteria.CriteriaB").First(&content)
    if content.Criteria[0].CriteriaA.Name != "A_1" {
        t.Fatal("Item 1 CriteriaA not found")
    }

    if content.Criteria[0].CriteriaB.Name != "B_1" {
        t.Fatal("Item 1 CriteriaB not found")
    }

    if content.Criteria[1].CriteriaB.Name != "B_2" {
        t.Fatal("Item 2 CriteriaB  not found")
    }
}

Comment From: TheDhejavu

Finally. Thank you man @a631807682 , this works!

Comment From: TheDhejavu

Hi @a631807682 , Preload function seems to generate a separate query for nested data thereby making it hard to perform even more complex queries that includes a fuzzy search on some columns.

E.G:

SELECT
    "contents".*,
    "criteria"."id" AS "criteria__id",
    "criteria"."content_id" AS "criteria__content_id",
    "criteria"."criteria_info_type" AS "criteria__criteria_info_type",
    "criteria"."criteria_info_id" AS "criteria__criteria_info_id",
    "criteria"."criteria_a_id" AS "criteria__criteria_a_id",
    "criteria"."criteria_b_id" AS "criteria__criteria_b_id",
    "criteria__criteria_a"."id" AS "criteria__criteria_a__id",
    "criteria__criteria_a"."name" AS "criteria__criteria_a__name",
    "criteria__criteria_a"."criteria_id" AS "criteria__criteria_a__criteria_id",
    "criteria__criteria_b"."id" AS "criteria__criteria_b__id",
    "criteria__criteria_b"."name" AS "criteria__criteria_b__name",
    "criteria__criteria_b"."criteria_id" AS "criteria__criteria_b__criteria_id"
FROM
    "contents"
LEFT JOIN
    "criteria" ON ("criteria"."content_id" = "contents"."id")
LEFT JOIN
    "criteria_a" AS "criteria__criteria_a" ON ("criteria__criteria_a"."id" = "criteria"."criteria_info_id" AND "criteria"."criteria_info_type" = 'CriteriaA')
LEFT JOIN
    "criteria_b" AS "criteria__criteria_b" ON ("criteria__criteria_b"."id" = "criteria"."criteria_info_id" AND "criteria"."criteria_info_type" = 'CriteriaB')
WHERE
    "criteria__criteria_a"."name" ILIKE '%A_1%'

i was hoping to see something like this?

Comment From: a631807682

use nested joins, their usage is the same

Comment From: TheDhejavu

use nested joins, their usage is the same

Thanks. Using joins requires lot of manual approach when scanning the data into its respective struct and that's what I am trying to prevent.

The above example is basic , the real usecase is larger than this.

Any other approach in Gorm that executes a single query ?

Comment From: TheDhejavu

Progress: i found this https://github.com/go-gorm/gorm/blob/master/tests/joins_test.go#L329

1:1 relationship seems to work but doesn't work for 1:N and the relationship between content and criteria here is 1:N

Comment From: a631807682

join does not support many-to-many relationships refer to https://github.com/go-gorm/gorm/issues/5280#issuecomment-1514225741, you can use them separately.

Comment From: TheDhejavu

Thanks @a631807682

DB.Preload("Criteria.CriteriaA").
 Preload("Criteria.CriteriaB").
 Joins("LEFT JOIN criteria ON criteria.content_id = contents.id").
 Joins("LEFT JOIN criteria_a AS criteria__criteria_a ON criteria__criteria_a.id = criteria.criteria_info_id AND criteria.criteria_info_type = 'CriteriaA'").
 Joins("LEFT JOIN criteria_b AS criteria__criteria_b ON criteria__criteria_b.id = criteria.criteria_info_id AND criteria.criteria_info_type = 'CriteriaB'").
 Where("criteria__criteria_a.name ILIKE ?", "%A_1%").
Find(&results)

I was able to combine Preload (preloading the 1:N criterias) and join to handle fuzzysearch.

Comment From: Fuzzyma

So there is no way, to only have "CriteriaInfo" without a or b and let it dynamically be filled with either Model A or Model B? Isnt the whole point of polymorphism to allow exactly that?

I need to access the parent of the relationship from the child model. So I would like to go from Criteria->Criteria{a,b} based on the criteria type.