QueryDSL Window functions

Question

How can I write a query using window functions and selecting all fields in QueryDSL? In the docs there is an example like this:

query.from(employee)
.list(SQLExpressions.rowNumber()
    .over()
    .partitionBy(employee.name)
    .orderBy(employee.id));

but I need to generate a query like:

SELECT * FROM 
  (SELECT employee.name, employee.id, row_number() 
    over(partition BY employee.name
    ORDER BY employee.id)
  FROM employee) AS sub
WHERE row_number = 1

And is it possible to do it with JPAQuery?

Timo Westkämper · Accepted Answer · 2015-06-17T16:51:52.183

15

JPAQuery supports only the expressivity of JPQL, so window functions are not supported, but paging should work using

query.from(employee).orderBy(employee.id).limit(1)

In case you need to use window functions and you need employee.name and employee.id out this should work

NumberExpression<Long> rowNumber = SQLExpressions.rowNumber()
    .over()
    .partitionBy(employee.name)
    .orderBy(employee.id).as("rowNumber");

query.select(employee.name, employee.id)
    .from(SQLExpressions.select(employee.name, employee.id, rowNumber)
                        .from(employee).as(employee))
    .where(Expressions.numberPath(Long.class, "rowNumber").eq(1L))
    .fetch();

edited Jun 17 '15 at 16:51

answered Jun 14 '15 at 08:48

Timo Westkämper

21,824
5
78
111

And how to do it with SQLQuery? – matteobarbieri Jun 15 '15 at 08:44
For SQLQuery it's best to do the same, since limit/offset handling is internally mapped to the paging functionality provided by the SQL engine. – Timo Westkämper Jun 15 '15 at 09:58
1

Ok thanks. Actually in my code I need rank and not rownumber, so limit won't work. Is there a way to generate a query like the one I posted? – matteobarbieri Jun 15 '15 at 10:15
I am also looking for exactly same functionality and the example provided in QueryDSL page is not very helpful. :( – tsatiz Jun 17 '15 at 12:37
with JPAQuery I am getting error like "The method from(EntityPath>) in the type JPAQueryBase> is not applicable for the arguments (SimpleExpression)" for this implementation. Any work arrounds? – Payal Singh Jan 11 '21 at 14:53

Pavel Bely · Answer 2 · 2017-05-17T08:29:12.373

As written by @timo Window functions (rank, row_number) are not supported by JPQL (JPA 2.1 version) and hence by JPAQuery (QueryDsl Jpa 4.1.4).

You can however rewrite your query so that is does not use rank over():

select a.* from employees a
where
(
    select count(*) from employees b
    where 
       a.department = b.department and
       a.salary <= b.salary
) <= 10
order by salary DESC

This is supported by JPAQuery, it probably goes like this.

final BooleanBuilder rankFilterBuilder = 
    new BooleanBuilder(employee.department.eq(employee2.department));
rankFilterBuilder.and(employee.salary.loe(employee2.salary));

query.from(employee)
.where(JPAExpressions.selectFrom(employee2)
            .where(rankFilterBuilder)
            .select(employee2.count())
            .loe(10))
.orderBy(employee.salary);

QueryDSL Window functions

2 Answers2

Linked