java - 获取 MAX 1 Child 时避免 OneToMany 的内存计算

Question

我正在使用 Spring Data JPA 并拥有映射到各自表的实体。我需要查询结果，以便我可以根据其强度获取所有父级和每个父级一个子级。

@Entity
public class Parent {
  @Id
  Long id;

  @OneToMany(mappedBy = "parent", cascade = CascadeType.ALL)
  private List<Child> children;
}

@Entity
public class Child {
  @Id
  Long id;

  @Enumerated(EnumType.STRING)
  private Strength strength;

  @ManyToOne(fetch = FetchType.EAGER)
  @JoinColumn(name = "parent_id", nullable = false, insertable = true, updatable = false)
  private Parent parent;
} 

public Enum Strength {
  STRONG,
  NORMAL,
  WEAK
}

我有一个基本的 CRUD 存储库，如下所示:

@Repository
public interface ParentRepository extends CrudRepository<Parent, Long>{}

一些规则和假设:

1. child 属于单亲家长
2. 一个父对象可以在数据库中拥有多个子对象
3. parent 可以有 0 或 1 个有力量 = STRONG 的 child
4. parent 将有 1 个具有力量 = NORMAL 的 child
5. 父对象将有 0 个或多个强度 = WEAK 的子对象
6. 弱子永远回不来
7. 下面的 getParentAndStrongChildren 方法最多应返回 1 个子级。

我可以在 Spring 中对 Parent Repository 方法执行 findAll 查询，然后将结果映射到内存中，如下所示

public List<Parent> getParentAndStrongChildren(){
    List<Parent> parents = parentRepository
        .findAll().stream()
        .map(p -> {
            if(p.getChildren() != null && p.getChildren.size() > 1){
               Child found = p.getChildren().stream()
                            .filter(c -> c.getStrength() == Strength.STRONG)
                            .findFirst()
                            .orElseGet(()-> p.getChildren().stream()
                                             .filter(c -> c.getStrength() == Strength.NORMAL)
                                             .findFirst()   
                                             .orElse(null));
                p.setChildren(found == null ? null : new Arrays.asList(found));
            }
        }
    return parents;
}

问:有没有办法不在内存中进行过滤，而依靠 JPQL 和 @Query 注解来实现这一点？

Answer 1

这是一个典型的“top N per category”SQL 查询。我个人怀疑这可以用 JPQL 来完成，但也许其他人会提供答案。这是使用横向方法解决您的问题的标准 SQL 解决方案:

SELECT p.*
FROM parent p
LEFT JOIN LATERAL (
  SELECT c.*
  FROM child c
  WHERE p.id = c.parent_id
  AND c.strength != 'WEAK'
  ORDER BY CASE c.strength WHEN 'STRONG' THEN 1 WHEN 'NORMAL' THEN 2 END
  FETCH FIRST ROW ONLY
) c ON 1 = 1

或者，使用窗口函数(也是标准 SQL):

SELECT p.*
FROM parent p
LEFT JOIN (
  SELECT c.*, row_number() OVER (
    PARTITION BY c.parent_id 
    ORDER BY CASE c.strength WHEN 'STRONG' THEN 1 WHEN 'NORMAL' THEN 2 END
  ) AS rk
  FROM child c
  WHERE c.strength != 'WEAK'
) c ON p.id = c.parent_id AND c.rk = 1