选择在联接表中没有特定记录的记录 [英] Select records which doesn't have specific records in joined table

问题描述

我正在尝试从一个表中选择记录，该表在连接的多对多表中没有具有特定值的记录.

我将在示例表中进行解释:

documentation:
id_documentation
irrelevant_data

user:
id_user
irrelevant_data


documentation_user:
id_documentation
id_user
role

我想要实现的是选择没有用户特定角色的每个文档.有什么想法吗?

主要问题是我正在使用Java的CriteriaBuilder创建查询，因此不可能使用子查询.

解决方案

尝试类似的方法(未经测试的代码):

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

另请参见关于SO的有用答案. /p>

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

解决方案

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

See also this useful answer on SO.

这篇关于选择在联接表中没有特定记录的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！