JPQL, How to NOT select something

Question

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

You say you want to include a Role of ADMIN, but your sample shows ROLE NAME NOT IN SysAdmin. Can you explain? — Raj More, Oct 16 '09 at 15:14
@Shervin - you need to be careful with your tags; majority of answers below are incorrect because you've tagged your question as "SQL" (now changed to JPQL) — ChssPly76, Oct 16 '09 at 16:01
No I don't want to include admin (sysadmin). The ProcessUser is a user, and a sysadmin, thus my select does not get me what I want. I want the user to be ommited if has sysadmin role. — Shervin Asgari, Oct 18 '09 at 14:27

ChssPly76 · Accepted Answer · 2009-10-18T16:42:40.890

18

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

edited Oct 18 '09 at 16:42

answered Oct 16 '09 at 16:05

ChssPly76

99,456
24
206
195

Thanks, this almost worked, however correct is WHERE role.name =='sysadmin' because you are already saying NOT IN – Shervin Asgari Oct 18 '09 at 16:22
I meant so say role.name = 'sysadmin' – Shervin Asgari Oct 18 '09 at 16:23

score 1 · Answer 2 · answered Oct 16 '09 at 15:13

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

answered Oct 16 '09 at 15:13

Raj More

47,048
33
131
198

This is straight SQL, not JPQL. – ChssPly76 Oct 16 '09 at 15:16

score 1 · Answer 3 · answered Oct 16 '09 at 15:21

1

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'

           )

answered Oct 16 '09 at 15:21

Gratzy

9,164
4
30
45

1

Again, this is SQL, not JPQL. – ChssPly76 Oct 16 '09 at 15:56
@ChssPly76 well the hope is posting the sql will point him in the correct direction to fix his JPQL especially since no one was offering a JPQL solution. In fact you didn't post one until 50 minutes after you pointed out other peoples responses were not JPQL – Gratzy Oct 16 '09 at 16:16
Said "hope" should probably have been mentioned in your answer then. Note that I didn't downvote you; I'm just pointing out that this is not really a valid solution to OP's question. As far as "50 minutes after" go, I've waited to see if you or anyone else in this thread would actually update their answers after to contain JPSQL after my comments so I can upvote them and I've only posted my answer after that didn't happen. – ChssPly76 Oct 16 '09 at 16:53
@ChssPly76 he listed the generated sql and tagged the question as sql, how do you know that he wasn't also looking for help understanding the sql that was generated? Maybe you should not have removed the sql tag. – Gratzy Oct 16 '09 at 17:00
Thank you Gratzy. I was actually looking for jpql, however sql is also fine because I can translate that to jpql. Thanks. – Shervin Asgari Oct 18 '09 at 14:29

score 0 · Answer 4 · answered Feb 24 '13 at 16:34

0

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;

answered Feb 24 '13 at 16:34

Johan

74,508
24
191
319

score 0 · Answer 5 · answered Oct 16 '09 at 15:15

0

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.

answered Oct 16 '09 at 15:15

Matt Boehm

1,894
1
18
21

JPQL, How to NOT select something

5 Answers5

Linked