按聚合的多对多列排序实体

Question

我们有一个与另一个实体(删除了不相关的属性)有多对多关系的父实体和该实体的相应存储库。我们能不能按孩子排序?

E。g:我发送一个带有sortBy 'category1'的请求，父实体按子实体的聚合值排序，按value属性聚合，并且只使用相应的类别。我已经尝试添加自定义Postgres方言和使用select string_agg，但我不能使排序工作。我们正在使用spring排序和可分页。给定下面的父实体链接到子实体，发送一个请求来获得父实体的排序列'category1'和排序顺序'降序'，它应该尝试按'aaabbb'和'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'
}
]
}

@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>

Answer 1

最后，我们通过以下操作解决了这个问题:

扩展postgres方言(注意您必须使用它，在应用程序中更改以下属性)。属性文件spring.jpa.properties.hibernate.dialect)

class PostgresRichDialect : PostgreSQL10Dialect() {
init {
registerFunction("string_agg", SQLFunctionTemplate(StandardBasicTypes.STRING, "string_agg(?1, ?2)"))
registerFunction(
"string_agg",
SQLFunctionTemplate(
StandardBasicTypes.STRING,
"string_agg(?1, ?2 ORDER BY ?3 )"
)
)
}
}

按如下方式更新查询:

@Query(
value = "select distinct pe as ParentEntity, lower(pe.name), lower(pe.description), string_agg(c1.value, ',', c1.value) as cValue 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.... group by pp.id)