Select records which doesn't have specific records in joined table

Question

Hi I'm trying to select records from one table which doesn't have records in connected many-to-many table with specific values.

I will explain on sample tables:

documentation:
id_documentation
irrelevant_data

user:
id_user
irrelevant_data


documentation_user:
id_documentation
id_user
role

What I want to achieve is to select every single documentation which doesn't have user in specific role. Any ideas?

The main problem is that I'm using java's CriteriaBuilder to create query so using subqueries is impossible (I think).

Maybe this... Is there any possibility to add more join condidions to be added in ON clause? For example: select * from documentation d left join documentation_user du ON d.id_documentation = du.id_documentation AND du.role = 2; — J33nn, Feb 24 '13 at 20:50
Yes, starting from Hibernate 3.5.0 you can. see [this fix](https://hibernate.onjira.com/browse/HHH-2308) — dan, Feb 24 '13 at 20:57
Could you please tell me how to achieve this with CriteriaBuilder? Root u = select.from(Documentation.class); Join dul = u.join("documentationUserCollection", JoinType.LEFT); — J33nn, Feb 24 '13 at 21:02

Mikko Maunu · Answer 1 · 2013-02-25T05:40:22.963

0

Main problem does not exist - Criteria API do have SubQuery. Query itself selects instances of User and uses not in construct to limit results based to subquery. Subquery selects all users that are connected to document with specific role via DocumentationUser.

edited Feb 25 '13 at 05:40

answered Feb 24 '13 at 21:08

Mikko Maunu

41,366
10
132
135

Better way is to left join documentation_user with additional on criteria and check if id_user is null: select * from documentation d left join documentation_user du on d.id_documentation = du.id_documentation and du.role = 2 where du.id_user is null; – J33nn Feb 24 '13 at 21:10
But still I don't know how to add additional condition to join in CriteriaBuilder. – J33nn Feb 24 '13 at 21:12
There is no possibility to add additional join conditions in Criteria API. – Mikko Maunu Feb 24 '13 at 21:13
Could you please tell me how? Can't find this. – J33nn Feb 24 '13 at 21:14

score 0 · Answer 2 · edited May 23 '17 at 12:22

0

You can add restrictions on your left join using: createAlias(java.lang.String, java.lang.String, int, org.hibernate.criterion.Criterion) method, see API.

Check this answer for an example on how to use the left join with a criteria.

edited May 23 '17 at 12:22

Community

1
1

answered Feb 24 '13 at 21:24

dan

13,132
3
38
49

score 0 · Answer 3 · edited May 23 '17 at 11:56

Try something like this (code not tested):

CriteriaQuery<Documentation> cq = cb.createQuery(Documentation.class);
Root<Documentation> u = cq.from(Documentation.class);
Subquery<Integer> sq = cq.subquery(Integer.class);
Root<User> su = sq.from(User.class);
sq.select(su.get("id_user"));
Join<User, DocumentationUser> du = su.join("documentationUserCollection");
sq.where(cb.equals(du.get("role"), "mySpecificRole"));
cq.where(cb.not(cb.in(u.get("id_user")).value(sq)));

Select records which doesn't have specific records in joined table

3 Answers3

Linked