How to use listagg with querydsl?

Question

I have a query as shown below;

SELECT 
   .
   .
   LISTAGG(DISTINCT CC.POPULATION, ', ') WITHIN GROUP (ORDER BY CC.POPULATION ASC),
   .
   .
FROM COUNTRY C
     JOIN CITY CC ON C.ID = CC.COUNTRY_ID
--WHERE 
GROUP BY C.ID;

I should be implement with querydsl for custom filtering and sorting operations but I got "No pattern found for LISTAGG" error

JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(         
            SQLExpressions.listagg(QCity.city.POPULATION, ",")
            .withinGroup()
            .orderBy(QCity.city.POPULATION.asc())
    )
            .from(QCountry.country)
            .join(QCity.city).on(QCountry.country.id.eq(QCity.city.countryId))
            //.where(custom filtering)
            .groupBy(QCountry.country.id);

jpaQuery.fetch();

I try to add custom template like this but I couldn't succeed. Also my querydsl-sql version is 4.2.1

StringTemplate customPopulationTemplate = Expressions.stringTemplate(
            "(LISTAGG(DISTINCT {0},',') WITHIN GROUP (ORDER BY {0} ASC))", QCity.city.population);

Answer 1

Since Hibernate 5.2.18, you can use the MetadataBuilderContributor utility to customize the MetadataBuilder even if you are bootstrapping via JPA.

The MetadataBuilderContributor interface can be implemented like this:

public class SqlFunctionsMetadataBuilderContributor
    implements MetadataBuilderContributor {
     
 @Override
 public void contribute(MetadataBuilder metadataBuilder) {
    metadataBuilder.applySqlFunction(
        "group_concat",
        new StandardSQLFunction(
            "group_concat",
            StandardBasicTypes.STRING
        )
    );
 }
}

And, we can provide the SqlFunctionsMetadataBuilderContributor via the hibernate.metadata_builder_contributor configuration property:

<property>
    name="hibernate.metadata_builder_contributor"
    value="com.vladmihalcea.book.hpjp.hibernate.query.function.SqlFunctionsMetadataBuilderContributor"
</property>

Reference: https://vladmihalcea.com/hibernate-sql-function-jpql-criteria-api-query/