如何将listagg与querydsl结合使用? [英] How to use listagg with querydsl?

问题描述

我有一个查询，如下所示；

I have a query as shown below;

SELECT 
   .
   .
   LISTAGG(DISTINCT CC.POPULATION, ', ') WITHIN GROUP (ORDER BY CC.POPULATION ASC),
   .
   .
FROM COUNTRY C
     JOIN CITY CC ON C.ID = CC.COUNTRY_ID
--WHERE 
GROUP BY C.ID;

我应该使用querydsl来实现自定义过滤和排序操作，但是我收到找不到LISTAGG的模式".错误

I should be implement with querydsl for custom filtering and sorting operations but I got "No pattern found for LISTAGG" error

JPAQuery<Tuple> jpaQuery = jpaQueryFactory.select(         
            SQLExpressions.listagg(QCity.city.POPULATION, ",")
            .withinGroup()
            .orderBy(QCity.city.POPULATION.asc())
    )
            .from(QCountry.country)
            .join(QCity.city).on(QCountry.country.id.eq(QCity.city.countryId))
            //.where(custom filtering)
            .groupBy(QCountry.country.id);

jpaQuery.fetch();

我尝试添加这样的自定义模板，但无法成功.另外我的querydsl-sql版本是4.2.1

I try to add custom template like this but I couldn't succeed. Also my querydsl-sql version is 4.2.1

StringTemplate customPopulationTemplate = Expressions.stringTemplate(
            "(LISTAGG(DISTINCT {0},',') WITHIN GROUP (ORDER BY {0} ASC))", QCity.city.population);

推荐答案

Window函数未包含在JPQL规范中，因此在任何JPA实现中均不可用.您可以使用自定义功能自行注册这些功能.

Window functions are not included in the JPQL specification and as such not available in any JPA implementation. You could register these functions yourself using custom functions.

但是，此后，这些功能仍无法在QueryDSL中访问.您正在从SQLExpressions窃取以获得窗口表达式.这些方法存在于SQLExpressions中的原因是:它们仅与querydsl-sql一起使用，而不与querydsl-jpa一起使用(同样，因为JPA本身不支持窗口函数).因此，在注册自定义函数后，您仍然必须扩展JPQLTemplates以包括自定义窗口函数的模板.

However, after this, these functions still won't be accessible in QueryDSL. You're stealing from the SQLExpressions here to obtain a window expression. These methods live in SQLExpressions for a reason: they only work with querydsl-sql and not with querydsl-jpa (again, because JPA itself does not support window functions). So after registering your custom function, you will still have to extend JPQLTemplates to include the template for your custom window function.

您将这样做:

public class MyTemplates extends JPQLTemplates {

    public MyTemplates() {
        add(SQLOps.ROWNUMBER, "ROW_NUMBER({0})");
    }

}

然后按如下所示使用它:

And then use it as follows:

new JPAQuery(entityManager, new MyTemplates()).from(entity).select(rowNumber())

---

或者，您可以查看 blaze-persistence-querydsl 扩展名，它对JPQL的窗口功能(以及许多其他功能)具有开箱即用的支持.例如:

---

Alternatively you could look into the blaze-persistence-querydsl extension, which has out of the box support for window functions (and many other features) for JPQL. For example:

QCat cat = QCat.cat;

BlazeJPAQuery<Tuple> query = new BlazeJPAQuery<Tuple>(entityManager, criteriaBuilderFactory).from(cat)
    .select(cat.name, JPQLNextExpressions.rowNumber(), JPQLNextExpressions.lastValue(cat.name).over().partitionBy(cat.id));

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

这篇关于如何将listagg与querydsl结合使用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！