Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

JPQL, How to NOT select something

Tags:

jpa

jpql

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 JPQL fails because my user also has role officer, so it is retrieved in the list.

Here is the JPQL:

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'
            )
        )
like image 296
Shervin Asgari Avatar asked Oct 16 '09 15:10

Shervin Asgari


2 Answers

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'
 )
like image 74
ChssPly76 Avatar answered Oct 24 '22 13:10

ChssPly76


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'
)
like image 37
Raj More Avatar answered Oct 24 '22 13:10

Raj More