How to perform a many-to-many query with SQLBoiler

2.2k Views Asked by At

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

0

There are 0 best solutions below