为什么在CriteriaQuery上左联接不筛选结果？ [英] Why left join on CriteriaQuery doesn't filter results?

问题描述

我有两个实体，分别是 User 和 Roles 个，每个实体都具有有效的布尔参数和 ManyToMany 关系。

I have two entities, User and Roles each one with active boolean parameter and ManyToMany relationship.

此布尔参数用于逻辑删除。

为什么当我在下面运行此查询时，结果包括不活动的角色？

Why does the result include inactive Roles when I run this query below?

 CriteriaBuilder builder = em.getCriteriaBuilder();
 CriteriaQuery<User> query = builder.createQuery(User.class);

 //Select
 Root<User> user = query.from(User.class);

 //Join
 SetJoin<User, Role> role = user.joinSet("roles", JoinType.LEFT);
 Predicate rolesActivePredicate = builder.isTrue(role.get("active"));
 role.on(rolesActivePredicate);

 query.multiselect(user).distinct(true);

 //Where
 Predicate usernamePredicate = builder.equal(user.get("username"), username);
 Predicate activePredicate = builder.isTrue(user.get("active"));
 query.where(usernamePredicate, activePredicate);

 TypedQuery<UnikUser> typedQuery = em.createQuery(query);
 return Optional.of(typedQuery.getSingleResult());

推荐答案

1. 请考虑以下内容<与具有角色 s的 @OneToMany 关系的code>个用户。

1. Consider the following Users with @OneToMany relationship to Roles.

 User-1 : Active-Role-1, Active-Role-2, In-Active-Role-3
 User-2 : In-Active-Role-3
 User-3 : Active-Role-1, Active-Role-2

1. JPA首先找到满足您条件的 User 条记录（至少有一个处于活动状态角色）

1. JPA first finds User records that satisfy your condition (have at-least one an active role)

 User-1
 User-3

1. 这是您的条件终止的位置。一旦确定要获取的记录ID，JPA就会完全获取这些 User 记录。 User-1 和 User-3 及其所有关联角色（包括不活动的角色）。它可能会执行 join 或另一个 select ，但是无论何时，它将获取其所有关联字段

1. This is where the role of your where conditions end. Once it has been decided which record id to fetch, JPA will fetch those User records in full. User-1 and User-3 with all their associated roles (including inactive). It might do join or another select but whenever it does, it will fetch all of its associated fields

总而言之，一旦决定获取实体，它将无法执行任何操作过滤其关联字段。在这种情况下，您希望在 user.getRoles（）中有一个经过过滤的对象，但不能。

In summary, Once it has decided to fetch an entity, it cannot do any filtering on its associated fields. In this case, you expected a filtered objects in user.getRoles() but it cannot.

如果JPA允许这样做，则它不能进行脏检查，级联或可重复读取。所以它不允许它

If JPA had allowed that, it cannot do dirty checking, cascading or repeatable read. So it does not allow it

这篇关于为什么在CriteriaQuery上左联接不筛选结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！