条件查询，查找我不是其中的所有组

I have two entities, Group and GroupMember. Group is like it sounds a group that has name and some other properties. Members of the group are mapped with GroupMember entity, which has an entry with a User and a Group for every group the user is member of. They look like the following:

我有两个实体,Group和GroupMember。群组听起来像是一个有名字和其他属性的群体。该组的成员使用GroupMember实体进行映射,该实体具有一个条目,该条目包含用户所属的每个组的用户和组。它们看起来如下:

@Entity
@Table(name = EntityTokens.GROUP_TABLE)
public class Group
{
   @Id
   @Column(name = EntityTokens.GROUP_ID_COLUMN) 
   @GeneratedValue(strategy = GenerationType.IDENTITY)    
   private Long groupId;

   ...

   // Group members
   @OneToMany(orphanRemoval = true, fetch = FetchType.LAZY, mappedBy = "group", cascade = {CascadeType.ALL})
   private Collection<GroupMember> groupMembers;
}

@Entity
@Table(name = EntityTokens.GROUP_MEMBER_TABLE)
public class GroupMember
{
   @Id
   @Column(name = EntityTokens.GROUP_MEMBER_ID_COLUMN) 
   @GeneratedValue(strategy = GenerationType.IDENTITY)    
   private Long memberId;

   ...

   @ManyToOne(fetch = FetchType.LAZY)
   @JoinColumn(name = EntityTokens.GROUP_MEMBER_ID_COLUMN)
   private Group group;
}

I'm trying to write a criteria query that returns all groups that have some predefined properties and that the current user is not part of. My query looks like this:

我正在尝试编写一个条件查询,该查询返回具有某些预定义属性且当前用户不属于的所有组。我的查询如下所示:

CriteriaQuery<Group> q = cb.createQuery(Group.class);
Root<Group> root = q.from(Group.class);

Join<Group, GroupMember> groups = root.join(Group_.groupMembers);

q.select(root);
q.where(cb.notEqual(groups.get(GroupMember_.user), user),
    cb.equal(root.get(Group_.global), false),
    cb.equal(root.get(Group_.personal), false),
    cb.equal(root.get(Group_.privacy), GroupPrivacy.PUBLIC));
q.distinct(true);

The user here represents the current user. This query doesn't work because if there are other members that are part of the same group as me they will be included in the query result due to the join. How should the correct query look like? I'm not that familiar with the criteria query API yet.

此处的用户代表当前用户。此查询不起作用,因为如果有其他成员属于同一组,则由于连接,它们将包含在查询结果中。正确的查询应该如何?我还不熟悉条件查询API。

1 个解决方案

#1

join for a ToMany relationship is conceptually an "anyOf" operation, meaning if any of the many is true then the expression is true.

从概念上来说,加入一个ToMany关系是一个“anyOf”操作,这意味着如果其中任何一个是真的那么表达式就是真的。

What you want is an "allOf", criteria does not have this, you need to use a sub select for this in SQL.

你想要的是一个“allOf”,标准没有这个,你需要在SQL中使用一个子选择。

The JPQL would be,

JPQL会是,

Select g from Group g where not exists (select m from g.members m where m.user = :user)

The criteria would be the same, using a sub criteria query for the exists.

使用存在的子标准查询,标准将是相同的。

#1