Multi value list foreign key sql gorm

Question

type Users struct {
    ID           int64      
    Email        string         
    Permissions  string
}

type UserPermissions  struct {
    ID          int64
    Description json.RawMessage
}

The user json should be like this:

{
    "status": 200,
    "data": {
        "id": 1,
        "email": "[email protected]",
        "permisions": [{
            "id":"1",
            "description":"Create permission"
         },
         {
            "id":"3",
            "description":"Edit permission"
         }]
    }
}

I have the following string in the Permissions of my User:

';1;3;5;7;' every number is the id related to the UserPermissions struct/table

How can I match the string with the user permission table using gorm?. I'm using mysql

Answer 1

An answer to your question (if you are using PostgreSQL as your DB).

If you want to query for the Permissions that a given User record has, given its ID

perms := []Permission{}
err := db.Joins("JOIN users u ON id::string IN STRING_TO_ARRAY(TRIM(';' FROM u.permissions), ';')").
    Where("u.id = ?", userID).
    Find(&perms).
    Error

if err != nil {
    log.Fatal(err) // or something like that
}

As you can see, we are doing a funky join with functions that clean and split the permission field into individual string IDs. This is clunky, brittle and super slow and convoluted, and stems from the faulty relational design that you started with.

EDIT: Nevermind, for MySQL the equivalent solution is very much more complicated, so I suggest you instead use the advice below.

A better way

If you have control over the database design, the better way to do this would be by not storing an array of IDs as a string, never a good thing in database design, but instead using a foreign key on the many side of the HasMany relationship*.

This would look like this as go structs:

type User struct {
    ID           int64             `json:"id"`     
    Email        string            `json:"email"`
    Permissions  []UserPermission  `json:"permissions"`
}

type UserPermissions struct {
    ID          int64            `json:"id"`
    UserID      int64            `json:"-"`
    Description json.RawMessage  `json:"description"`
}

// now you can use gorm to query the permissions that are related to a user
user := User{}
err := db.Preload("Permissions").First(&user, userID).Error
if err != nil {
    log.Fatal(err)
}
// now you can access user.Permissions[i].Description for example.
// or marshal to json
out, err := json.Marshal(user)
if err != nil {
    log.Fatal(err)
}
fmt.Println(string(out))

*: I am assuming that the relationship between User and UserPermission is One-to-many. This may very well not be the case, and a Many-to-many relationship actually makes sense (One user can have many permissions, one permission can belong to many users). If this is the case, the concepts are the same and you should be able to modify this solution following the Gorm guide on many to many relationships.