JPQL, как НЕ выбрать что-то

У меня довольно простой SQL, который мне нужно выполнить.

У меня есть таблица ProcessUser, Role и a ProcessUserRole. Прямой вперед много-ко-многим

Я хочу выделить все ProcessUser, которые также имеют роль администратора.

Однако мой JPQL терпит неудачу, потому что у моего пользователя также есть роль сотрудника, поэтому он извлекается в списке.

Вот JPQL:

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

Сгенерированный SQL:

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'
            )
        )

Ответы

Ответ 1

Правильный синтаксис JPQL с использованием подзапроса:

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

Ответ 2

Будет ли это работать для вас?

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'
)

Ответ 3

В основном ваш запрос возвращает список пользователей/ролей, так как у вашего пользователя две роли, которые он дважды возвращается, вы отфильтровываете одну строку, исключая роль "sysadmin". Похоже, что вы хотите сделать, это исключить всех пользователей, которые играют роль "sysadmin", независимо от того, имеют ли они другие роли. Вам нужно будет добавить что-то к вам, например. (Я по вашему запросу не описываю ваше описание)

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

           )

Ответ 4

Запустите вложенный запрос. Сначала выберите всех пользователей с ролью sysadmin. Затем выберите дополнение этого или всех пользователей, которые не находятся в этом результате.

Ответ 5

JPQL:

TypedQuery<ProcessUser> query = em.createQuery("" +  
   " SELECT p FROM ProcessUser p " +
   " WHERE p.roles.name <> ?1", ProcessUser.class);
query.setParameter(1, "sysadmin");
return query.getResultList;