Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Count in QueryDSL grouping transformer

Is it possible to get the count of aggregating results when using groupby processing in QueryDSL? I have the following query:

query.from(catalog)
.innerJoin(qe).on(catalog.id.eq(qe.itemId))
.innerJoin(enterprise).on(enterprise.id.eq(qe.enterpriseId))

.leftJoin(catalogPerson).on(catalogPerson.catalogId.eq(catalog.id))

.where(catalog.deletionDate.isNull(), qe.enterpriseId.eq(org) )

.orderBy(catalog.creationDate.desc())

.limit(limit)
.offset(offset)

.transform(groupBy(catalog.id).as(Projections.constructor(Catalog.class,
                                catalog.id,
                                catalog.name,
                                catalog.code,
                                // //GET THE NUMBER OF CATALOG PERSONS'
                                )));

and I want to get the count of persons belonging to the certain catalog.

Thanks

like image 959
Nedo Avatar asked Oct 02 '13 12:10

Nedo


1 Answers

If you don't need any of the child records then express the query without group by transformation like this (only sketching)

query.from(catalog)
.innerJoin(qe).on(catalog.id.eq(qe.itemId))
.innerJoin(enterprise).on(enterprise.id.eq(qe.enterpriseId))
.leftJoin(catalogPerson).on(catalogPerson.catalogId.eq(catalog.id))
.where(catalog.deletionDate.isNull(), qe.enterpriseId.eq(org))
.orderBy(catalog.creationDate.desc())
.limit(limit)
.offset(offset)
.groupBy(catalog.id)
.list(Projections.constructor(Catalog.class,
                            catalog.id,
                            catalog.name,
                            catalog.code,
                            catalogPerson.count()));

The group by transformation is useful if you need to aggregate individual result rows.

like image 164
Timo Westkämper Avatar answered Sep 30 '22 11:09

Timo Westkämper