Prisma 2 efficient ManyToMany query for users with roles

Question

I have several tables, User ---> UserRoles <--- Roles in which I want to query all the users and get all the users roles in the query also. I can achieve this with plain sql with the following:

SELECT u.user_id, u.first_name, r.description ...
FROM users AS u
INNER JOIN user_roles AS ur ON ur.user_id = u.user_id
LEFT JOIN roles AS r ON ur.role_id = r.role_id;

and I can currently achieve it with prisma2 in the following way:

const main = async () => {
    const users = await prisma.users.findMany();

    for (let index = 0; index < users.length; index++) {
        const user = users[index];

        const roles = await prisma.user_roles.findMany({
            where: {
                user_id: user.user_id,
            },
            select: {
                roles: true,
            },
        });

        user.roles = [];
        roles.forEach((obj) => {
            user.roles.push(obj.roles);
        });
    }

    return users;
};

But as you can see that would be insane to requery every user for the roles this way, how can it be done in one query to the db?

The desired result from prisma2 is the same as what the current prisma2 query above returns:

"users": [
      {
        "user_id": "71096566-ef63-4865-96c3-136910420492",
        "first_name": "Jack",
        "roles": [
          {
            "description": "manager"
          },
          {
            "description": "admin"
          }
        ]
      },
      {
        "user_id": "c588da66-0e3a-42d5-abc9-9ea04c4687ef",
        "first_name": "Jill",
        "roles": [
          {
            "description": "superboss"
          }
        ]
      }
    ]

Prisma 2 efficient ManyToMany query for users with roles

0 Answers0