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"`
}

Comment From: YuZongYangHi

Rewrite intermediate table steps:

db.SetupJoinTable(&MenuRole{}, "Menus", &MenuRoleBindingMenu{})
db.SetupJoinTable(&Menu{}, "Roles", &MenuRoleBindingMenu{})

Comment From: YuZongYangHi

The query expression is as follows

var result []MenuRole
err := db.Joins("Menus", db.Where(&Menu{Id: 1})).Find(&result).Error

DEUG generates sql as follows:

SELECT
  `rbac_menu_role`.`id`,
  `rbac_menu_role`.`name`,
  `rbac_menu_role`.`description`,
  `rbac_menu_role`.`create_time`,
  `rbac_menu_role`.`update_time`,
  `Menus`.`id` AS `Menus__id`,
  `Menus`.`name` AS `Menus__name`,
  `Menus`.`key` AS `Menus__key`,
  `Menus`.`parent_id` AS `Menus__parent_id`,
  `Menus`.`description` AS `Menus__description`,
  `Menus`.`create_time` AS `Menus__create_time`,
  `Menus`.`update_time` AS `Menus__update_time`
FROM
  `rbac_menu_role`
  LEFT JOIN `rbac_menu` `Menus` ON `rbac_menu_role`.`id` = `Menus`.`role_id`
  AND `rbac_menu_role`.`menu_id` = `Menus`.`id`
  AND `Menus`.`id` = 1

The error message is as follows:

Error 1054 (42S22): Unknown column 'Menus.role_id' in 'on clause'

Comment From: YuZongYangHi

@jinzhu

Comment From: YuZongYangHi

@black-06

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: black-06

See doc Joins Perloading

NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to

So you may need to join manually

Comment From: YuZongYangHi

See doc Joins Perloading

NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to

So you may need to join manually

For my current situation, can the problem be solved only by using native left sql in Joins?

Comment From: black-06

See doc Joins Perloading

NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to

So you may need to join manually

For my current situation, can the problem be solved only by using native left sql in Joins?

I am not sure. Cc @a631807682

Comment From: YuZongYangHi

@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.

Comment From: github-actions[bot]

Comment From: a631807682

See doc Joins Perloading

NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to

So you may need to join manually

For my current situation, can the problem be solved only by using native left sql in Joins?

I am not sure. Cc @a631807682

NOTE Join Preload works with one-to-one relation, e.g: has one, belongs to

~~This sentence is ambiguous, we just won't integrate multiple (seemingly identical) records into one record, we should still allow users to use join in many-to-many situations.~~

~~The error stems from the many-to-many relationship not being declared explicitly, so it should be treated specially.~~ ~~https://github.com/go-gorm/gorm/blob/master/callbacks/query.go#L132~~ ~~https://github.com/go-gorm/gorm/blob/master/callbacks/query.go#L149~~

~~@black-06 I didn't investigate more, and probably also consider how the scan is received, would you like to create a PR for it? Or I will follow this issues in a few days.~~

Comment From: a631807682

Sorry, the above comment is problematic, in fact, we cannot use many-to-many relationship in join because of the inability to aggregate multiple records into one. It's not that queries can't be generated, it's that they can't receive. refer to https://github.com/go-gorm/gorm/issues/5280#issuecomment-1514225741

@YuZongYangHi You said beego can do it, can you provide a link? Documentation or example links are fine

Comment From: YuZongYangHi

Sorry, the above comment is problematic, in fact, we cannot use many-to-many relationship in join because of the inability to aggregate multiple records into one. It's not that queries can't be generated, it's that they can't receive. refer to #5280 (comment)

@YuZongYangHi You said beego can do it, can you provide a link? Documentation or example links are fine

beego orm many2many example

type RoleBinding struct {
    ID      int64      `orm:"pk;auto; column(id)" json:"id"`
    Groups  []*Group   `orm:"rel(m2m);rel_table(ks_rbac_role_binding_groups)" json:"groups"`

type Group struct {
    Id          int64     `orm:"pk;auto; column(id)" json:"id"`
    Name        string    `json:"name"`

# center table ks_rbac_role_binding_groups
ks_rbac_role_binding_id 
ks_group_id 

# query 
var rb RoleBinding
err := Orm().QueryTable(TableNameRoleBinding).
Filter("groups__ks_group_id", group.Id). # cross-table query
One(&rb)

@a631807682

Comment From: YuZongYangHi

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

Comment From: a631807682

@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

Comment From: a631807682

Sorry, the above comment is problematic, in fact, we cannot use many-to-many relationship in join because of the inability to aggregate multiple records into one. It's not that queries can't be generated, it's that they can't receive. refer to #5280 (comment) @YuZongYangHi You said beego can do it, can you provide a link? Documentation or example links are fine

beego orm many2many example

`` type RoleBinding struct { ID int64orm:"pk;auto; column(id)" json:"id"Groups []*Grouporm:"rel(m2m);rel_table(ks_rbac_role_binding_groups)" json:"groups"`

type Group struct { Id int64 orm:"pk;auto; column(id)" json:"id" Name string json:"name"

center table ks_rbac_role_binding_groups

ks_rbac_role_binding_id ks_group_id

query

var rb RoleBinding err := Orm().QueryTable(TableNameRoleBinding). Filter("groups__ks_group_id", group.Id). # cross-table query One(&rb) ```

@a631807682

I tried this code, it returns

table `role_binding` already exists, skip
table `group` already exists, skip
table `ks_rbac_role_binding_groups` already exists, skip
panic: unknown field/column name `groups__ks_group_id`

Am I missing something? Or can you provide a link to an executable code repository?

Comment From: YuZongYangHi

Sorry, the above comment is problematic, in fact, we cannot use many-to-many relationship in join because of the inability to aggregate multiple records into one. It's not that queries can't be generated, it's that they can't receive. refer to #5280 (comment) @YuZongYangHi You said beego can do it, can you provide a link? Documentation or example links are fine

beego orm many2many example `` type RoleBinding struct { ID int64orm:"pk;auto; column(id)" json:"id"Groups []*Grouporm:"rel(m2m);rel_table(ks_rbac_role_binding_groups)" json:"groups"`

type Group struct { Id int64 orm:"pk;auto; column(id)" json:"id" Name string json:"name"

center table ks_rbac_role_binding_groups

ks_rbac_role_binding_id ks_group_id

query

var rb RoleBinding err := Orm().QueryTable(TableNameRoleBinding). Filter("groups__ks_group_id", group.Id). # cross-table query One(&rb) ```

@a631807682

I tried this code, it returns

table `role_binding` already exists, skip table `group` already exists, skip table `ks_rbac_role_binding_groups` already exists, skip panic: unknown field/column name `groups__ks_group_id`

Am I missing something? Or can you provide a link to an executable code repository?

Wait a mininute

Comment From: YuZongYangHi

@a631807682 see: my blog example

Comment From: YuZongYangHi

@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

This preload method only means that the data of other tables is filtered, and the main table still cannot be filtered, which does not meet our usual needs for inner join....

Comment From: a631807682

@a631807682 see: my blog example

Your example means that you only need to match certain conditions through the intermediate table, instead of querying the data of RoleBinding.Users or RoleBinding.Groups through join while querying RoleBinding?

Comment From: YuZongYangHi

@a631807682 see: my blog example

Your example means that you only need to match certain conditions through the intermediate table, instead of querying the data of RoleBinding.Users or RoleBinding.Groups through join while querying RoleBinding?

@a631807682 That's how it's related

var result []models.RoleBinding
_, err := models.Orm().QueryTable("role_binding").
    Filter("Users__user_id", 1).
    Filter("Groups__group_id", 1).
    All(&result)

if err == nil {
    for _, r := range result {
    models.Orm().LoadRelated(&r, "Groups")
    models.Orm().LoadRelated(&r, "Users")
    }
}

Comment From: YuZongYangHi

@a631807682 see: my blog example

Your example means that you only need to match certain conditions through the intermediate table, instead of querying the data of RoleBinding.Users or RoleBinding.Groups through join while querying RoleBinding?

@a631807682 That's how it's related, The query of beego is a table query, and the bottom layer is join, but the preload of gorm should not meet this situation.

``` var result []models.RoleBinding _, err := models.Orm().QueryTable("role_binding"). Filter("Users__user_id", 1). Filter("Groups__group_id", 1). All(&result)

if err == nil { for _, r := range result { models.Orm().LoadRelated(&r, "Groups") models.Orm().LoadRelated(&r, "Users") } } ```

Comment From: a631807682

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.

Comment From: YuZongYangHi

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.

can you give an example of gorm based on my above example, thank you very much

Comment From: github-actions[bot]

Comment From: a631807682

I don't have enough time at the moment, I will add an example (or a detailed description of the implementation process) to the comments later, maybe next week, you can also refer to https://github.com/go-gorm/gorm/blob/master/association.go#L558 .

Comment From: github-actions[bot]

Comment From: YuZongYangHi

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

looking forward to your reply

Comment From: ankit16-19

@YuZongYangHi did you find any way to implement this in gorm?

@a631807682 can you please give some example.