Group equivalent in queryDSL

Question

What is the equivalent expression for a group concat SQL query in queryDSL? I am using queryDSL with spring JPA and it is being updated to the latest version and I could not find such a thing while reading documents.

Thank.

Answer 1

I found the answer myself. This can be done using the conversion function in queryDSL.

From http://www.querydsl.com/static/querydsl/3.4.3/reference/html/ch03s02.html Section 3.2.4

Map<Integer, List<Comment>> results = query.from(post, comment)
    .where(comment.post.id.eq(post.id))
    .transform(groupBy(post.id).as(list(comment)));

So, for example, if you want to group by 2 columns, the following code will work:

Map<List<?>, List<Comment>> results = query.from(post, comment)
    .where(comment.post.id.eq(post.id))
    .transform(groupBy(post.id, post.hooplah).as(list(comment)));

which creates the map List_of_groupByFieldValues ​​-> List_of_results.

- , concat queryDSL?

Answer 2

. .

https://github.com/querydsl/querydsl/commit/23e2471b4735ad50f5ac33ad539474d113bb5b07

, :

    @Test
    @ExcludeIn(DERBY)
    public void groupConcat() {
        assertEquals(
                ImmutableList.of("Mike,Mary", "Joe,Peter,Steve,Jim", "Jennifer,Helen,Daisy,Barbara"),
                query().select(SQLExpressions.groupConcat(employee.firstname))
                        .from(employee).orderBy(groupConcatOrder())
                        .groupBy(employee.superiorId).fetch());
    }

    @Test
    @ExcludeIn(DERBY)
    public void groupConcat2() {
        assertEquals(
                ImmutableList.of("Mike-Mary", "Joe-Peter-Steve-Jim", "Jennifer-Helen-Daisy-Barbara"),
                query().select(SQLExpressions.groupConcat(employee.firstname, "-"))
                        .from(employee).orderBy(groupConcatOrder())
                        .groupBy(employee.superiorId).fetch());
    }

    private OrderSpecifier<?>[] groupConcatOrder() {
        if (Connections.getTarget() == Target.POSTGRESQL) {
            return new OrderSpecifier[] {employee.id.asc()};
        } else {
            return new OrderSpecifier<?>[0];
        }
    }