Spring JPA多对多关联场景下按子实体聚合值排序实现方案

当前存在与其他实体构成多对多关联关系的父实体（已移除无关属性）及对应JPA Repository，需要实现按关联子实体属性聚合结果对父实体排序的功能。

核心需求 #

• 当接口传入sortBy参数值为category1时，仅筛选对应分类的子实体，按子实体value属性的聚合结果对父实体排序
• 已尝试方案：自定义Postgres方言、使用string_agg函数实现聚合，但结合Spring的Sort与Pageable分页机制时排序始终无法正常生效

预期排序效果示例 #

传入排序列为category1、排序方向为降序时：

• parent1下所有category1分类的子实体value聚合结果为aaabbb
• parent2下对应分类子实体value聚合结果为cccddd
• 按聚合值降序排序后返回顺序应为parent2、parent1

示例实体数据：

{
  "name": "parent1",
  "child": [
    {
      "category": "category1",
      "value": "aaa"
    },
    {
      "category": "category1",
      "value": "bbb"
    },
    {
      "category": "category2",
      "value": "ccc"
    }
  ]
}
{
  "name": "parent2",
  "child": [
    {
      "category": "category1",
      "value": "ccc"
    },
    {
      "category": "category1",
      "value": "ddd"
    },
    {
      "category": "category2",
      "value": "eee"
    }
  ]
}

现有实体与Repository定义代码：

@Entity(name = "parent")
class ParentEntity { 
    @field:ManyToMany(fetch = FetchType.EAGER)
    @field:ValidateCategories([category1, category2, category3])
    val child: MutableSet<ChildEntity>
}


@Entity(name = "child")
@Table(uniqueConstraints = [UniqueConstraint(columnNames = ["category", "value"])])
class ChildEntity {
    @field:Column(nullable = false)
    @field:Enumerated(EnumType.STRING)
    val category: Category

    @field:Column(nullable = false, columnDefinition = "citext")
    @field:Size(min = 1, max = 255)
    val value: String
}


interface ParentRepository : JpaRepository<ParentEntity, String> {
    @Query(
        value = "select distinct pe as ParentEntity, lower(pe.name), lower(pe.description) from parent pe left join pe.child c1 on c1.category = 'category1' 
    left join pe.child c2 on c2.category = 'category2' left join pe.child c3 on c3.category = 'category3' where etc....)
    fun findByProperties(
        @Param("request") request: GetRequest,
        pageable: Pageable
    ): Page<ParentEntityProjection>
}

排序失效的核心原因有两个：一是直接注册自定义函数后，Spring Data的Sort参数无法识别未显式声明的聚合派生字段；二是distinct+多次left join同一张子表的写法会产生重复行，导致聚合结果、分页计数异常。按以下步骤调整即可正常生效：

1. 修正JPQL查询结构，显式声明聚合字段别名 #

放弃多次left join同一张子表的写法，直接在select子句中用case when过滤分类做聚合，给每个聚合结果声明固定别名供Sort引用，同时单独写count查询避免分页计数错误：

@Query(
    value = """
        select 
            pe,
            lower(pe.name) as nameLower,
            lower(pe.description) as descLower,
            string_agg(case when c.category = 'category1' then c.value else null end, '' order by c.value) as category1Agg,
            string_agg(case when c.category = 'category2' then c.value else null end, '' order by c.value) as category2Agg,
            string_agg(case when c.category = 'category3' then c.value else null end, '' order by c.value) as category3Agg
        from parent pe
        left join pe.child c
        where -- 保留原有过滤逻辑
        group by pe.id
    """,
    countQuery = """
        select count(distinct pe.id) from parent pe
        left join pe.child c
        where -- 和主查询过滤逻辑保持一致
    """
)
fun findByProperties(
    @Param("request") request: GetRequest,
    pageable: Pageable
): Page<ParentEntityProjection>

注意：string_agg内要加order by c.value保证聚合时value拼接顺序固定，否则返回的聚合字符串顺序随机，排序结果不可控；用group by pe.id替代distinct去重，避免多表join产生重复父实体记录。

2. 业务层做Sort字段映射 #

不要直接把前端传入的sortBy参数透传给Pageable，在构造Pageable前将入参映射为查询中声明的聚合别名：

// 接口层接收sortBy、sortDir参数后做转换
val sort = when (sortBy) {
    "category1" -> Sort.by(Sort.Direction.fromString(sortDir), "category1Agg")
    "category2" -> Sort.by(Sort.Direction.fromString(sortDir), "category2Agg")
    "category3" -> Sort.by(Sort.Direction.fromString(sortDir), "category3Agg")
    "name" -> Sort.by(Sort.Direction.fromString(sortDir), "nameLower")
    else -> Sort.by(Sort.Direction.DESC, "createTime") // 配置默认排序规则
}
val pageable = PageRequest.of(pageNum, pageSize, sort)

3. 调整投影接口 #

如果使用接口-based的ParentEntityProjection，仅需声明业务需要返回的父实体字段、名称、描述等属性即可，聚合用的xxAgg字段不需要加入投影定义，不会影响正常序列化返回。

调整完成后，传入sortBy=category1、排序方向降序时，生成的SQL会自动在order by子句追加category1Agg desc，分页计数也会返回正确结果，最终返回顺序为parent2、parent1，符合预期。

内容的提问来源于stack exchange，提问作者George Marin