refactor(manager-api-jpa): rework `getAllUsersWithPermission` & `getAllUsersWithRole` to work on Postgres
The queries were written in a weird way that worked on H2 but not on Postgres.
@@ -2356,9 +2356,10 @@ public class JpaStorage extends AbstractJpaStorage implements IStorage, IStorage
@Override
public List<UserBean> getAllUsersWithPermission(PermissionType permission, String orgName) throws StorageException {
return getJdbi().withHandle(h -> h.createQuery(
- "SELECT DISTINCT u.* FROM USERS u, MEMBERSHIPS m, PERMISSIONS p "
- + "INNER JOIN PERMISSIONS ON p.ROLE_ID = m.ROLE_ID "
- + "INNER JOIN MEMBERSHIPS ON m.USER_ID = u.USERNAME "
+ "SELECT DISTINCT u.* "
+ + "FROM USERS u "
+ + " INNER JOIN MEMBERSHIPS m ON m.USER_ID = u.USERNAME "
+ + " INNER JOIN PERMISSIONS p ON p.ROLE_ID = m.ROLE_ID "
+ " INNER JOIN USERS ON u.USERNAME = m.USER_ID "
+ "WHERE p.PERMISSIONS = :permissionType "
+ " AND m.ORG_ID = :orgName")
@@ -2375,7 +2376,9 @@ public class JpaStorage extends AbstractJpaStorage implements IStorage, IStorage
@Override
public List<UserBean> getAllUsersWithRole(String roleName, String orgName) throws StorageException {
return getJdbi().withHandle(h -> h.createQuery(
- "SELECT DISTINCT u.* FROM USERS u, MEMBERSHIPS m "
+ "SELECT DISTINCT u.* "
+ + "FROM USERS u "
+ + " INNER JOIN MEMBERSHIPS m ON m.USER_ID = u.USERNAME "
+ " INNER JOIN USERS ON u.USERNAME = m.USER_ID "
+ "WHERE m.ORG_ID = :orgName "
+ " AND m.ROLE_ID = :roleName")
