Sort entities by many to many column aggregated-CodePudding

We have a parent entity in many to many relationships to another entity (removed irrelevant properties) and the corresponding repository for this entity. Is there a way we can sort by the child?

E.g: I send a request with sortBy 'category1' and the parent entities are sorted by the aggregated values of the child entities, aggregated by the value property, and only with the corresponding category. I have tried adding a custom Postgres dialect and using select string_agg but I cannot make the sorting work. We are using the spring sorting and pagable. Given the below parent entity linked to the child entities, send a request to get the parent with sort column 'category1' and sort order 'descending', it should try to sort the parent entities by 'aaabbb' and 'cccddd' and return 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>

CodePudding user response：

In the end, we were able to solve this by doing the following:

Extend the postgres dialect (note that you have to use it, change the following property in application.properties file 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 )"
            )
        )

    }
}

Update the query as follows:

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