避免循环-递归m2m关系自引用

Question

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

Answer 1

can't say best practice, my suggestion is put the validation logic at application layer.

1. a helper function in JS to filter the option in multi-select like this
2. a validation logic in API
3. a validation logic at repository layer.
4. track the roles when apply in case there is a circle.