JPA Criteria API 任意数量的联接/子查询

我需要使用以下实体构建相交类型查询（为了清楚起见，减少了实体）。

@Entity // and other @ stuff
public class Member {
    @Id
    private Long id;
    private String name;
    ...
}

@Entity
public class Program {
     @Id
     private Long id;
     private Long programName;
     private List<ProgramLevel> levels;
     ...
}

@Entity
public class ProgramLevel {
    @Id
    private Long id
    private String levelName;
}

一名成员可以属于一个或多个计划，并且他始终有自己的计划级别，通过以下方式连接：

public class Membership {
    @Id
    private Long id;
    private Long memberId;     // this is the member
    private Long programId;    // in which program is he
    private Long programLevel; // and on what level
    ...
}

示例：我有三个课程：数学、英语、科学。他们每个人都有一些级别，例如，数学有代数、几何，英语有文学、拼写和语法，科学有实验和理论。

此外，示例用户 Joe 将拥有数学：代数、英语：语法程序和级别。示例用户 Max 可能有英语：文学。因此，会员可以拥有多个计划，但每个计划只能有一个级别。

现在我需要计算或获取与几个程序及其中的某些级别匹配的所有成员。 示例：我希望所有用户都具备数学：代数或几何、英语：文学或语法和科学：理论。

我不太喜欢 JPA 的东西，所以我陷入了困境。

在 SQL 中，我会进行相交。我该如何使用 JPA 来做呢？

我有这样的事情：

HashMap<Long, List<ProgramLevel>> levels = new HashMap<Long, List<ProgramLevel>>();
// then I fetch the levels map.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> criteriaQuery = cb.createQuery(Long.class);
Root<Membership> root = query.from(Membership.class);

// this is the part where I'm stuck.

// I figured I could try with multiple inner joins?
for(Map.Entry<Long, List<ProgramLevel>> curentLevel: levels.entrySet()) {
    // cb.equal(root.join ??? what comes here?
    // what what what?
    // root.get("programId") = currentLevel.getKey()
    // AND root.get("programLevelId") IN currentLevel.getValue()

}
...

我怎样才能得到这个？

除了作为多个内部联接执行此操作之外，我不知道它是否可以作为 INTERSECT 执行（如果重要的话，db 是 PostgreSQL）？

另外，我不确定在该查询中的何处放置条件，以便仅通过这些条件获得不同的成员资格。

作为奖励，我必须创建一个 OR 查询。这是我必须匹配所有程序的地方，下一个需要任何程序/级别匹配才能将成员包含在那里。但我希望，一旦我弄清楚了这个，我就会自己弄清楚那个。

编辑：可能的（伪）SQL 选择如下所示：

SELECT count(membership) FROM membership m
  WHERE (m.program == :program1 and m.programLevel in :programLevels1ArrayOfLevels)
INTERSECT
SELECT count(membership) FROM membership m
  WHERE (m.program == :program2 and m.programLevel in :programLevels2ArrayOfLevels)
INTERSECT...
... /* this would go on for as many (Program, List<ProgramLevel>) pairs I have on input. Which is variable).

或者是这样的：

SELECT count(membership) FROM membership m
JOIN membership m1 ON (m1.program = :program1 AND m1.programLevel IN m1.programLevels1Aray)
JOIN membership m2 ON (m2.program = :program2 AND m2.programLevel IN m1.programLevels2Aray)
/* Continued to as many input pairs I have */

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = cb.createQuery(Tuple.class);
Root<Membership> root = query.from(Membership.class);

Collection<Long> queredPrograms = levels.keySet()
Predicate[] queryPredicates = new Predicate[queredPrograms.size()];
int i = 0;
for(Long programId : queredPrograms) {
    queryPredicates[i++] = cb.and(
        cb.equal(root.get("programId"), programId),
        root.get("programLevel").in(levels.get(programId))
    );
}
criteriaQuery.where(
    cb.and(
        cb.or(queryPredicates),
        root.get("programId").in(queredPrograms)
    )
);

criteriaQuery.groupBy(root.get("programId"));
criteriaQuery.select(cb.tuple(cb.count(cb.distinct(root.get("memberId"))), root.get("programId")));
TypedQuery<Tuple> countSelection = entityManager.createQuery(criteriaQuery);

标准 API 的 SQL 等效项将如下所示（将按每个程序 ID 返回唯一的用户计数）

SELECT 
      COUNT(DISTINCT memberId),
      programId
FROM membership
WHERE 
      programId in (:PROGRAMS_ID_LIST)
      AND (
           (programId = :PROGRAM_ID AND programLevel IN (:PROGRAM_LEVEL_LIST))
           ...
           OR (programId = :PROGRAM_ID_N AND programLevel IN (:PROGRAM_N_LEVEL_LIST))
      )
GROUP BY programId

如果您不想按每个programId进行计数，只需从group by和select子句中删除programId，并在where子句中将OR更改为AND。我希望这能帮到您。