CODEWITHSUNDEEP
javahibernatejpaquerydsl
Djefferson Silva
Djefferson Silva

Reputation: 66

QueryDSl multilevel result aggregation

QueryDsl provides a tool for aggregating results returned from the database as follows:

http://www.querydsl.com/static/querydsl/latest/reference/html/ch03s02.html

In the item: 3.2.4. Result aggregation

But I don't know how to do a 3-level aggregation, and I didn't find any documentation of it, nor in the QueryDSL automated test class does this case appear, and it seems very strange that this is not possible, as this is a very common case.

Here's the example of what I want to do:

DTO's classes : 

class DtoLevel1 {
    Long id;
    List<DtoLevel2> level2;
}

class DtoLevel2 {
    Long id;
    List<DtoLevel3> level3;
}

class DtoLevel3 {
    Long id;
}

Query:

QDtoLevel1 dtoLevel1 = QDtoLevel1.dtoLevel1;
QDtoLevel1 dtoLevel2 = QDtoLevel1.dtoLevel1;
QDtoLevel1 dtoLevel3 = QDtoLevel1.dtoLevel1;

JPAQuery<DtoLevel1> query = new JPAQuery<DtoLevel1>();

// ... query.from, etc

query.transform(
    GroupBy.groupBy(dtoLevel1.id).as( // the first level is ok
            Projections.constructor(
                    DtoLevel1.class, dtoLevel1.id, 
                    GroupBy.list( // passing grouped items in the first level
                        Projections.constructor(
                            DtoLevel2.class, 
                            dtoLevel2.id, 
                            GroupBy.groupBy(dtoLevel2.id) // the problem is here
                                .list(
                                    Projections.constructor(DtoLevel3.class, dtoLevel3.id)
                                )
                        )
                    )
            )
    )
)

In the line where I highlighted my problem, is a conceptual code that is the way I believe it should work. Which is where I need to group the third level items belonging to the current second level, but I haven't found a way to do that, just for the first level.

Upvotes: 2

Views: 1260

Answers (1)

Luiz Eduardo Zis
Luiz Eduardo Zis

Reputation: 11

Djefferson, I faced this problem too. Seems actually this problem has no solution yet. Multiple level support is a feature to do in QueryDsl roadmap, but is foreseen since 2015..

I developed a lib to offer a approach to help this multiple level aggregation. More details in: https://github.com/zisluiz/querydsl-object-binder.

Below a example that how this lib can help a aggregation like that:

JPAQuery<Tuple> query = new JPAQuery<Tuple>(em);

query.from(_city)
        .join(_city.state, _state)
        .join(_state.country, _country);

List<Tuple> tupleResult = query.select(_city.id, _city.name,
        _state.id, _state.name,
        _country.id, _country.name).orderBy(_city.id.asc()).fetch();

List<City> cities = QueryDslBinder.to(tupleResult, City.class,
        new GroupByBinder()
            .key("id", _city.id).
            field("name", _city.name)
            .single("state", new GroupByBinder()
                    .key("id", _state.id)
                    .field("name", _state.name)
                    .single("country", new GroupByBinder()
                            .key("id", _country.id)
                            .field("name", _country.name)                   
                            .collection("states", new GroupByBinder()
                                    .key("id", _state.id)))));

It supports a variety of queries combinations in multiple levels, always using single object referente for each specified object. Supports too subquery expressions path and many complex queries that are not possible to map into results with only QueryDsl api. Helped me a lot in company project.

Upvotes: 1

Related Questions