gorm: Many2Many Multi-table query using Join Table error: Unknown column

I have two tables doing many2many, but when I try to join the query, he always reports that the column cannot be found

type MenuRole struct {
	Id          int64     `json:"id"`
	Name        string    `gorm:"column:name;unique;not null" json:"name"`
	Menus       []*Menu   `gorm:"many2many:rbac_menu_role_binding_menu;joinForeignKey:role_id;joinReferences:menu_id" json:"menus"`
}

type Menu struct {
	Id          int64       `json:"id"`
	Name        string      `gorm:"column:name" json:"name"`
	Roles       []*MenuRole `gorm:"many2many:rbac_menu_role_binding_menu;joinForeignKey:menu_id;joinReferences:role_id" json:"roles"`
}

type MenuRoleBindingMenu struct {
	Id         int64     `json:"id"`
	RoleId     int64     `gorm:"primaryKey;column:role_id" json:"roleId"`
	MenuId     int64     `gorm:"primaryKey;column:menu_id; not null;" json:"menuId"`
	CreateTime time.Time `json:"createTime"`
	UpdateTime time.Time `json:"updateTime"`
}

About this issue

Original URL
State: closed
Created a year ago
Comments: 29 (9 by maintainers)

Most upvoted comments

https://github.com/go- gorm/gorm/blob/master/association.go#L558

looking forward to your reply

YuZongYangHi on Jun 20, 2023

gorm does not have a Filter api to handle conditions in relationships (the Where api does not handle association relationships).

The Joins api does not process many-to-many relationships, it is used to query data, the specified join table data needs to be accepted (beego Filter does not accept the specified join table data, it needs to use LoadRelated, Similar to https://gorm.io/docs/associations.html#Find-Associations).

The Preload/Association api allows you to specify query criteria, but only for the specified table, you can filter for User or Group, but you still cannot join multiple tables as filter criteria like the Filter api.

The current api is not suitable for such a scenario, and such a scenario is not suitable for the above api, the current possible approach is to use a lower dimension clause api encapsulation.

a631807682 on Jun 15, 2023

@a631807682 If I want to query data through many2many, is there a recommended query method? Is it only possible to use Joins(“inner xxx”)?

I’d look into beego’s implementation, usually the Preload api is recommended for many-to-many relationships

a631807682 on Jun 14, 2023

@a631807682 @black-06

I have gone through a lot of issues. This kind of problem has been there since 2 years ago, but I haven’t gotten an answer. If the many2many query can’t use __ to split like beego, but use the original inner, then I think it will be very complicated.

query a lot of issues, many people have the same problem as me.

YuZongYangHi on Jun 13, 2023