java query dsl_java – QueryDSL – 按计数排序为别名

我正在使用queryDSL从用户那里获得一些额外的数据：

public List getUsersWithData (final SomeParam someParam) {

QUser user = QUser.user;

QRecord record = QRecord.record;

JPQLQuery = query = new JPAQuery(getEntityManager());

NumberPath cAlias = Expressions.numberPath(Long.class, "cAlias");

return query.from(user)

.leftJoin(record).on(record.someParam.eq(someParam))

.where(user.active.eq(true))

.groupBy(user)

.orderBy(cAlias.asc())

.list(user, record.countDistinct().as(cAlias));

}

尽管它按预期工作,但它在SQL中生成两个COUNT()：

SELECT

t0.ID

t0.NAME

to.ACTIVE

COUNT(DISTINCT (t1.ID))

FROM USERS t0 LEFT OUTER JOIN t1 ON (t1.SOME_PARAM_ID = ?)

WHERE t0.ACTIVE = true

GROUP BY t0.ID, to.NAME, t0.ACTIVE

ORDER BY COUNT(DISTINCT (t1.ID))

我想知道是否有可能得到这样的东西：

SELECT

t0.ID

t0.NAME

to.ACTIVE

COUNT(DISTINCT (t1.ID)) as cAlias

FROM USERS t0 LEFT OUTER JOIN t1 ON (t1.SOME_PARAM_ID = ?)

WHERE t0.ACTIVE = true

GROUP BY t0.ID, to.NAME, t0.ACTIVE

ORDER BY cAlias

我没有从文档中理解这一点,如果可能,请给我一些指示.

最佳答案

QVehicle qVehicle = QVehicle.vehicle;

NumberPath aliasQuantity = Expressions.numberPath(Long.class, "quantity");

final List quantityByTypeVO = new JPAQueryFactory(getEntityManager())

.select(Projections.constructor(QuantityByTypeVO.class, qVehicle.tipo, qVehicle.count().as(aliasQuantity)))

.from(qVehicle)

.groupBy(qVehicle.type)

.orderBy(aliasQuantity.desc())

.fetch();

select

vehicleges0_.type as col_0_0_, count(vehicleges0_.pk) as col_1_0_

from vehicle vehicleges0_

group by vehicleges0_.type

order by col_1_0_ desc;

我做了类似的事情,但在订购之前我确实算过了.查看查询和生成的选择.