JPA QL, How to NOT select something

Question

Hi.

I have a pretty simple sql I need to perform.

I have a ProcessUser, Role and a ProcessUserRole table. A straight forward many-to-many

I want to select all ProcessUser's that does also have a Role of admin.

However my jpa sql fails because my user also has role officer, so it is retrieved in the list.

Here is the sql:

entityManager.createQuery("SELECT p FROM " + ProcessUser.class.getName() + " p join p.roles role WHERE role.name NOT IN ('sysadmin')").getResultList();

The generated SQL is:

select
        distinct processuse0_.id as id8_,
        processuse0_.position as position8_,
        processuse0_.username as username8_,
        processuse0_.organization_id as organiza9_8_,
        processuse0_.passwordHash as password4_8_,
        processuse0_.fromEmail as fromEmail8_,
        processuse0_.firstname as firstname8_,
        processuse0_.lastname as lastname8_,
        processuse0_.processes as processes8_
    from
        ProcessUser processuse0_ 
    inner join
        ProcessUserRoles roles1_ 
            on processuse0_.id=roles1_.userId 
    inner join
        Role role2_ 
            on roles1_.roleId=role2_.id 
    where
         (
            role2_.name not in  (
                'sysadmin'
            )
        )

Answer 1

Will this work for you?

SELECT *
FROM ProcessUser
WHERE Exists
(
    SELECT 1
    FROM 
     ProcessUserRoles
     INNER JOIN Roles
      ON Roles.RoleId = ProcessUserRoles.RoleId
    WHERE 1=1
     AND ProcessUser.ProcessUserId = ProcessUserRoles.ProcessUserId
     AND Roles.RoleDescription = 'Super User'
)

Answer 2

Run a nested query. First select all users with the role of sysadmin. Then select the complement of this, or all users that are not in this result.

Answer 3

Your query is basicly bringing back a list of user/roles since your user has two roles he comes back twice, you filter out one row by excluding the role of 'sysadmin'. What it sounds like you want to do is exclude all users who have a role of 'sysadmin' regardless of they have other roles. You would need to add something to you query like. (I'm going by your query not your description)

  where processuse0_.id not in 
  select ( userId  from 
           ProcessUserRoles
           inner join 
           Role 
           on ProcessUserRoles.roleId=Role.id 
           where role.name != 'sysadmin'

           )

Answer 4

entityManager.createQuery("SELECT p FROM " + ProcessUser.class.getName() + " p left outer join (select RoleId from Roles WHERE name  ='sysadmin') r on r.RoleId = p.RoleId where r.RoleId is null").getResultList();

Answer 5

Proper JPQL syntax using subquery:

SELECT p FROM ProcessUser p
 WHERE p.id  NOT IN (
  SELECT p2.id FROM ProcessUser p2
    JOIN p2.roles role
   WHERE role.name='sysadmin'
 )