Querydsl: GROUP_CONCAT does NOT work in QueryDSL

[beamofsoul]

Hi there,

I am struggling to figure out how to use 'GROUP_CONCAT' in QueryDSL.

final QDataSourceSmallEliminate eliminate = QDataSourceSmallEliminate.dataSourceSmallEliminate;
        final QLattitudeVisitor lattitude = LattitudeVisitorServiceImpl.$;
//      Expressions.stringOperation(SQLOps.GROUP_CONCAT, lattitude.tagName)

        return QuerydslUtils.newQuery(entityManager).select(Projections.constructor(EliminateLattitude.class, 
                eliminate.id, 
                JPAExpressions.selectDistinct( SQLExpressions.groupConcat(lattitude.tagName)).from(lattitude).where(lattitude.urlCrc.eq(eliminate.urlCrc))))
            .from(eliminate)
            .fetch();

Exception:

org.springframework.dao.InvalidDataAccessApiUsageException: No pattern found for GROUP_CONCAT; nested exception is java.lang.IllegalArgumentException: No pattern found for GROUP_CONCAT

...

Caused by: java.lang.IllegalArgumentException: No pattern found for GROUP_CONCAT
    at com.querydsl.core.support.SerializerBase.visitOperation(SerializerBase.java:280)
    at com.querydsl.jpa.JPQLSerializer.visitOperation(JPQLSerializer.java:437)
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:231)
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:31)
    at com.querydsl.core.types.OperationImpl.accept(OperationImpl.java:83)
    at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92)
    at com.querydsl.jpa.JPQLSerializer.serialize(JPQLSerializer.java:203)
    at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:358)
    at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:39)
    at com.querydsl.core.types.SubQueryExpressionImpl.accept(SubQueryExpressionImpl.java:57)
    at com.querydsl.core.support.FetchableSubQueryBase.accept(FetchableSubQueryBase.java:150)
    at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92)
    at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:119)
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:225)
    at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:31)
    at com.querydsl.core.types.ConstructorExpression.accept(ConstructorExpression.java:112)
    at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92)
    at com.querydsl.jpa.JPQLSerializer.serialize(JPQLSerializer.java:203)
    at com.querydsl.jpa.JPAQueryBase.serialize(JPAQueryBase.java:60)
    at com.querydsl.jpa.JPAQueryBase.serialize(JPAQueryBase.java:50)
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:98)
    at com.querydsl.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:94)
    at com.querydsl.jpa.impl.AbstractJPAQuery.fetch(AbstractJPAQuery.java:201)
    at com.moraydata.general.secondary.repository.impl.DataSourceSmallEliminateRepositoryImpl.findObject(DataSourceSmallEliminateRepositoryImpl.java:98)
    at com.moraydata.general.secondary.repository.impl.DataSourceSmallEliminateRepositoryImpl$$FastClassBySpringCGLIB$$b42d875d.invoke(<generated>)
    at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)

QueryDSL 4.2.1 & JDK1.8 & Spring Boot2.0 & MySQL 5.7.

I read through the latest reference doc still cannot find the answer, so what should I do to make it work ?

Please help.
Thanks in advance.
@Shredder121

[Shredder121]

You can't always mix JPAExpressions and SQLExpressions.

[beamofsoul]

You can't always mix JPAExpressions and SQLExpressions.

Yes, mate, you are right. I have no clue to fix this, just try something might useful.
So how can I deal with it if I need to GROUP_CONCAT when using JPAExpressions ?

[giraffe-tree]

@beamofsoul

if you use spring boot data jpa and querydsl-jpa , You can follow these steps

1. create a custom DB dialect and register function

//  org.hibernate.dialect.MySQL5Dialect
public class CustomMysqlDialect extends MySQL5Dialect {
    public CustomMysqlDialect() {
        super();
        // register custom/inner function here
        this.registerFunction("group_concat", new SQLFunctionTemplate(StandardBasicTypes.STRING, "group_concat(?1)"));
    }
}

1. config JpaVendorAdapter

@Configuration
public class JpaConfiguration {
    @Bean
    public JpaVendorAdapter jpaVendorAdapter() {
        AbstractJpaVendorAdapter adapter = new HibernateJpaVendorAdapter();
        adapter.setShowSql(true);
        adapter.setDatabase(Database.MYSQL);
       // package to CustomMysqlDialect
        adapter.setDatabasePlatform("com.xxx.xxx.config.CustomMysqlDialect");
        adapter.setGenerateDdl(false);
        return adapter;
    }
}

1. use query-dsl

// before do this , autowird JPAQueryFactory at first 
        QReportDoctorTag qReportDoctorTag = QReportDoctorTag.reportDoctorTag;
        SimpleTemplate<String> simpleTemplate = Expressions.simpleTemplate(String.class, "group_concat({0})", qReportDoctorTag.tag);
        JPAQuery<Tuple> tupleJPAQuery = queryFactory.select(qReportDoctorTag.reportId, simpleTemplate)
                .from(qReportDoctorTag)
                .groupBy(qReportDoctorTag.reportId);

        List<Tuple> fetch = tupleJPAQuery.fetch();

[jwgmeligmeyling]

For SQL use SQLExpressions.groupConcat. For JPA this is not supported, because GROUP_CONCAT is not a function defined for JPQL. None of the ORM vendors ship it by default, although the function can be registered manually in some cases (see the example in the comment above).

Duplicate of #2020.

[tanqiyuan]

@giraffe-tree Hello, I want use "GROUP_CONCAT({0} ORDER BY {1})" , How register the function? Please.

[jwgmeligmeyling]

In JPA this is unsupported. You'll have to ask your ORM vendor to implement it first (for example Hibernate).

You could consider using the blaze-persistence-querydsl extension to fill the gap: https://persistence.blazebit.com/documentation/1.5/core/manual/en_US/index.html#querydsl-integration

Which has the following methods:

• JPQLNextExpressions.groupConcat(Expression<?> expression, String separator, OrderSpecifier<?>... orderSpecifiers)
• JPQLNextExpressions.groupConcat(Expression<?> expression, Expression<String> separator, OrderSpecifier<?>... orderSpecifiers)
• JPQLNextExpressions.groupConcat(boolean distinct, Expression<?> expression, String separator, OrderSpecifier<?>... orderSpecifiers)
• JPQLNextExpressions.groupConcat(boolean distinct, Expression<?> expression, Expression<String> separator, OrderSpecifier<?>... orderSpecifiers)