this is less of a question about golang or mysql, its more a general question. Hope i am still in the right place and someone could help me to wrap my head around this.
I have a struct Role which can have multiple child roles.
type Role struct{
Name string
Children []Role
}
So let's say Role A has a child Role B and Role B has a Child Role C.
In my frontend, the m2m relation is displayed as a multi-select HTML field. To avoid an infinite loop (A-B-C-A...) I want that the user can not enter one of the related Roles.
For example, Role C should not display Role A and B, because if a user would select those, an infinite loop would happen.
The database in the backend is looking like this:
roles table (main table) id, name, ...
role_roles (junction table) role_id, child_id
I created this helper method to detect the ids which should not get displayed. It's checking if the Role C is somewhere in the field child_id then it takes the role_id of this entry and is doing the same again. This works, but it is looking really unprofessional and I was wondering how this could be solved in a more elegant way - and with fewer SQL queries...
// whereIDLoop returns the ids which should get excluded
func whereIDLoop(id int) ([]int, error) {
ids := []int{}
b := builder.GlobalBuilder
rows, err := b.Select("role_roles").Columns("role_id").Where("child_id = ?", id).All()
if err != nil {
return nil, err
}
for rows.Next() {
var id int
if err := rows.Scan(&id); err != nil {
return nil,err
}
ids = append(ids, id)
id2,err := whereIDLoop(id)
if err != nil {
return nil, err
}
if id2 != nil{
ids = append(ids, id2...)
}
}
err = rows.Close()
if err != nil {
return nil, err
}
return ids, nil
}
Thanks for any help. Cheers Pat
can't say best practice, my suggestion is put the validation logic at application layer.