How to join nested tables

[sintanial]

Your Question

I have models like this:

package models

type ItemAsset struct {
	ID int32 `gorm:"primary_key;AUTO_INCREMENT;column:id;type:int;" json:"id"`
	ItemID string `gorm:"column:item_id;type:varchar;size:36;" json:"item_id"`
}

type ItemSet struct {
	ID uint32 `gorm:"primary_key;AUTO_INCREMENT;column:id;type:uint;" json:"id"`
}

type Item struct {
	ID int32 `gorm:"primary_key;AUTO_INCREMENT;column:id;type:int;" json:"id"`
	ItemID string `gorm:"column:item_id;type:varchar;size:36;" json:"item_id"`
	SetID null.Int `gorm:"column:set_id;type:uint;" json:"set_id"`
}

type PlayerItem struct {
	ID int32 `gorm:"primary_key;AUTO_INCREMENT;column:id;type:int;" json:"id"`
	PlayerID string `gorm:"column:player_id;type:varchar;size:128;" json:"player_id"`
	ItemID string `gorm:"column:item_id;type:varchar;size:36;" json:"item_id"`
	ItemLevelID null.Int `gorm:"column:item_level_id;type:uint;" json:"item_level_id"`
}

type ItemLevel struct {
	ID uint32 `gorm:"primary_key;AUTO_INCREMENT;column:id;type:uint;" json:"id"`
	ItemID string `gorm:"column:item_id;type:varchar;size:36;" json:"item_id"`
}

And then i have another structure which combine all structure with relations

package entity

type Item struct {
	models.Item
	ItemAsset *models.ItemAsset `gorm:"foreignKey:ItemID;references:item_id" json:"item_asset"`
	ItemSet   *models.ItemSet          `gorm:"foreignKey:ID;references:set_id" json:"item_set"`
}

type PlayerItem struct {
	models.PlayerItem
	Item      Item       `gorm:"foreignKey:ItemID;references:item_id" json:"item"`
	ItemLevel *models.ItemLevel `gorm:"foreignKey:ItemLevelID;references:id" json:"item_level"`
}

So my question is, how can i join nested models when i fetch entity.PlayerItem ?

Query like this is not working :(

db.
		Joins("ItemLevel").
		Joins("Item").
		Joins("Item.ItemAsset").
		Joins("Item.ItemSet").
		Find(&items).

Yes, of course i know about Preload ! But it's generate separate query, but i want to join nested structure by LEFT JOIN query !

[lisandromdc]

I also needing the same feature. A simple Joins("Item.ItemAsset") like the Preload, but using LEFT JOIN

[0xdeface]

+1. i also

[lfelguetac]

JOIN no work for me ._.

[d-baranowski]

Hate to be the "me too" guy... but me too :(

[MateuszKepczynski]

Try by adding tag gorm:"embedded":

type PlayerItem struct {
	models.PlayerItem `gorm:"embedded"`
	Item      Item       `gorm:"foreignKey:ItemID;references:item_id" json:"item"`
	ItemLevel *models.ItemLevel `gorm:"foreignKey:ItemLevelID;references:id" json:"item_level"`
}

But it doesn't work in the way you want to. It will create model.PlayerItem fields in PlayerItem structure. So now when you will try to do INSERT on PlayerItem struct it will demand model.PlayerItem fields and so on... It really need improvement.

Or you can try creating lets call it aggregator. When you are doing more than one Join on similar fields like IDs gorm.io will return two fields called ID (ItemAsset and Item) not itemAsset.ID item.ID.

So what can you do is to create this aggregator.

func gatherResult() {
  type aggregator struct {
    item Item `gorm:"embedded"`
    itemLevel ItemLevel `gorm:"embedded;embeddedPrefix:itemlevel_"`
  }
  var item []Item
  var result []aggregator
  db.Join("LEFT JOIN itemLevel on itemLevel.id = item.itemLevelID").Select([]string{"item.id","item.itemID","itemLevel.ID as itemlevel_id","itemLevel.itemID as itemlevel_item_id"}).Model(&item).Scan(&result)
}

Just adjust it to your code.

[TobiasGrether]

I believe you can use .Preload("Item.ItemAsset") if you want to load it. At least that works for me, maybe you should try that.

[CubicrootXYZ]

Having the same issue. embedded tag is not a workaround for this, you can not aggregate multiple separate tables into a single one and call it a solution, this will come with a lot of drawbacks and is not how relational databases are desired to be used.

Preload is also no viable option considering it's performance difference especially for large data sets.

Please make it possible to use nested joins, it is a very basic SQL feature that I'd expect from any ORM to support.

[4ND3R50N]

This is definatly a necessary feature for some users. We started using gorm, but recently discovered that there is no possibility to use JOIN with 1:N relations.

This is a hard drawback, hopefully there is some feedback if this is getting implemented in the future :-)

[KiddoV]

When will this feature release?

[bcbill]

why cant we just preload and join the same table?? Preload the nested data we want to get, and then use Join for conditions

[bcbill]

When will this feature release?

@KiddoV already released last week

[amirejaz75]

I also need this feature. Thank god it is released. Unable to find any documentation for it. Can someone please mention the link to it? Thanks.

[KiddoV]

What version is the latest for Gorm? Kinda confused the latest one was released 2 years ago 🤔

[a631807682]

There is no documentation at present, you can refer to unit test, I will add it to the documentation this month, if anyone is willing to help add documentation, please commented in https://github.com/go-gorm/gorm/issues/6226

The latest version is v1.25.0

[amirejaz75]

Thanks @a631807682 . I tried and it's working for nested structs but only where there is 1:1 relationship. Like

        DB.
	Joins("Manager").
	Joins("Manager.Company").
	Joins("Manager.NamedPet").
	Joins("NamedPet").
	Joins("NamedPet.Toy")

All these relationships are 1:1, i.e Manager has only one company, NamedPet etc.

But it is failing where we have 1:N relationship. i.e it will fail in the following scenario (as Manager/User has 1:N relationship with Pets):

        DB.
	Joins("Manager").
	Joins("Manager.Pets")

OR

        DB.
        Joins("User.Pets")

How we can use nested joins with 1:N relationships? Is there any way other than Preload? Though It works well with Preload, but it will cost network latency as it creates separate queries (for 1:N relationship it makes almost 4 to 5 queries).

[a631807682]

Join will not handle 1:N relationship.

1. Join from native sql will not handle 1:N relationship. When API and sql have the same syntax, we must ensure that their meanings are also similar.
2. When using Join, we cannot actually determine the ownership of the collection. Take the pseudo code below as an example. If there are two users with different ids but the same name, how do we determine which user the pet belongs to?

DB.Select("User.Name, Pets.Name").Joins("Pets").Find(&user)

[selopia909]

@a631807682

Nested Join is working for this: DB.Joins("Manager").Joins("Manager.Pet")

It works for Preload, but not working for Nested Join: DB.Joins("Manager").Joins("Manager.Pet").Joins("Manager.Pet.Toy")

It shows this error on the following line: Error 1054 (42S22): Unknown column 'Pet.toy_id' in 'on clause' LEFT JOIN toys Pet__Toy ON Pet.toy_id = Pet__Toy.id

The generated query should be like this, wrong alias: LEFT JOIN toys Pet__Toy ON Manager__Pet.toy_id = Pet__Toy.id

Manager.Pet.Toy Manager can have a Pet, and that Pet can Have a Toy. Any workaround for this?