I'm using SQLBoiler as my golang ORM.
I'm trying to query a user and all of his roles, to do that I have the following tables:
ROLES:
role_id desc name
1 basic basic
USERS:
user_id password user_name created_at updated_at updated_by ...
1 ... ... ... ... ...
USERS_ROLES:
id user_id role_id
1 1 1
foreign keys: user_id, role_id
I'm trying to query (without using sql query) a user and all of his roles in the following way:
func getUserRoles(user *models.USERS) (models.USERROLESSlice, error) {
if roles, err := user.UserUSERROLESS().All(context.Background(), DB); err != nil {
infra.LogError("failed to query user roles", err)
return nil, err
} else {
return roles, nil
}
}
which gives me models.USERROLESSlice instead of slice of models.ROLES that contain my joining table.
or to just use:
if usr, err := models.USERSS(qm.Where(query, req.Email, req.Password),qm.Load("ROLES")).One(context.Background(), DB); err != nil && err.Error() == "sql: no rows in result set" {
infra.LogInfo(fmt.Sprintf("no result for %s", req.Email))
return nil, nil
}
as I would have done if I had any other object-relationship but non of those give me the result I expect.
any one know what is the easy & elegant way to do it?
Edit: For now I just used qm.SQL to get forward
if roles, err := models.ROLESS(qm.SQL("SELECT roles.* FROM user_roles INNER JOIN roles ON user_roles.role_id = roles.role_id WHERE user_roles.user_id=@uid", user.UserID)).All(context.Background(), DB); err != nil {
infra.LogError("failed to query user roles", err)
return nil, err
}
but again I'm looking for the elegant way, you can find some examples in the following link: https://github.com/volatiletech/sqlboiler