Transform output/ simplified relationship

Hi! This question may be a bit dumb and has other solutions, like just creating a function to achieve what a i want, but i wanted to see if it was achievable with the Drizzle, I'm trying to implement RBAC, but im trying to store all of this in my on database and replicate the RBAC to my provider, basically I want to be able to query the user and get all of its roles and its permissions, like something like this:

{
  "id": 1,
  "supertokensId": "1f7043f8-7e98-48d7-a55e-9379071f3181",
  "username": "tacomaster8",
  "name": "test",
  "paternal_last_name": "mctest",
  "maternal_last_name": "test",
  ... more user details
  "userRoles": [
    {
      "name": "test role 1",
      "resource": "cabin",
      "rolePermissions": [
        {
          "id": 2,
          "name": "test1"
        }
      ]
    },
    {
      "name": "test roles 2",
      "rolePermissions": [
        {
          "id": 1,
          "name": "TEst"
        }
      ],
      "resource": "dev"
    }
  ]
}

{
  "id": 1,
  "supertokensId": "1f7043f8-7e98-48d7-a55e-9379071f3181",
  "username": "tacomaster8",
  "name": "test",
  "paternal_last_name": "mctest",
  "maternal_last_name": "test",
  ... more user details
  "userRoles": [
    {
      "name": "test role 1",
      "resource": "cabin",
      "rolePermissions": [
        {
          "id": 2,
          "name": "test1"
        }
      ]
    },
    {
      "name": "test roles 2",
      "rolePermissions": [
        {
          "id": 1,
          "name": "TEst"
        }
      ],
      "resource": "dev"
    }
  ]
}

{
  "id": 1,
  "supertokensId": "1f7043f8-7e98-48d7-a55e-9379071f3181",
  "username": "tacomaster8",
  "name": "test",
  "paternal_last_name": "mctest",
  "maternal_last_name": "test",
  ... more user details
  "userRoles": [
    {
      "name": "test role 1",
      "resource": "cabin",
      "rolePermissions": [
        {
          "id": 2,
          "name": "test1"
        }
      ]
    },
    {
      "name": "test roles 2",
      "rolePermissions": [
        {
          "id": 1,
          "name": "TEst"
        }
      ],
      "resource": "dev"
    }
  ]
}

{
  "id": 1,
  "supertokensId": "1f7043f8-7e98-48d7-a55e-9379071f3181",
  "username": "tacomaster8",
  "name": "test",
  "paternal_last_name": "mctest",
  "maternal_last_name": "test",
  ... more user details
  "userRoles": [
    {
      "name": "test role 1",
      "resource": "cabin",
      "rolePermissions": [
        {
          "id": 2,
          "name": "test1"
        }
      ]
    },
    {
      "name": "test roles 2",
      "rolePermissions": [
        {
          "id": 1,
          "name": "TEst"
        }
      ],
      "resource": "dev"
    }
  ]
}

I'll attach screenshot for my current response and definitions, am i doing somthing wring with the definitions? Maybe im not getting the schema definition part to achieve what i want.

Monopolo11OP•1/21/24, 4:10 AM

Maybe a select statement, but how can I turn the relation into an array?

Monopolo11OP•1/21/24, 5:31 AM

Or something like the example in the second case https://playground.prisma.io/examples/reading/relations/nested-reads

Prisma Playground | Learn the Prisma ORM in your browser

The Playground is an interactive learning environment for Prisma. Learn how to send database queries and explore migrations workflows with the Prisma ORM.

Angelelz•1/23/24, 2:14 AM

I didn't see your relations, assuming they are correct, you can use the relational query builder:

db.query.users.findfirst({
  where: eq(users.id, 1),
  with: {
    userRoles: {
      with: {
        role: { with: rolePermissions }
      }
    }
  }
})

db.query.users.findfirst({
  where: eq(users.id, 1),
  with: {
    userRoles: {
      with: {
        role: { with: rolePermissions }
      }
    }
  }
})

Monopolo11OP•1/23/24, 6:10 PM

I wanted to simplify the object, as the array that it returns is too complex and brings extra information