Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

QueryDSL Window functions

Tags:

java

jpa

querydsl

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?

like image 323
matteobarbieri Avatar asked Jun 12 '15 07:06

matteobarbieri


1 Answers

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();
like image 96
Timo Westkämper Avatar answered Nov 04 '22 03:11

Timo Westkämper