How to order by multiple columns in Spring data JPA query with calculation in order by?

Question

I am trying to add the following query to the JPA repository @Query

"select st from Strategy st where st.unId= ?1 order by ( st.totalMargin / st.totalRevenue ) desc"

However, this query does not parse due to the "(" brackets in the query.

The error thrown while bean initialization is 
Caused by: <openjpa-2.2.2-r422266:1468616 nonfatal user error> org.apache.openjpa.persistence.ArgumentException: "Encountered "(" at

Is there any way this can be achieved in @Query ?

This isn't really Spring Data JPA related, Open JPA chokes on that... The query is passed, as is, to your JPA provider. However I wonder if that is even possible in JPA... — M. Deinum, May 27 '15 at 13:20
@NitinDandriyal - trying to do order by on value using division between 2 columns ( x / y ). — OkraSala, May 27 '15 at 13:32
Yes, I get that but I think your query will run without brackets as well — Nitin Dandriyal, May 27 '15 at 13:33
No, it does not. In that case it is not able to parse the " / " symbol. — OkraSala, May 27 '15 at 13:34
What if you re-write your query as `select st, st.totalMargin / st.totalRevenue as ratio from Strategy st where st.unId= ?1 order by ratio desc` — Nitin Dandriyal, May 27 '15 at 13:55

score 0 · Answer 1 · answered May 27 '15 at 13:49

0

In your Strategy object, u can add a getter :

public double getMarginRatio() {
  return (this.totalMargin / this.totalRevenue);
}

then, your order by statement will become "order by st.marginRatio"

answered May 27 '15 at 13:49

Tristan

8,733
7
48
96

Thanks Tristan, I tried this. This does not work. It expects one of the existing column names. – OkraSala May 27 '15 at 16:03

score 0 · Answer 2 · answered May 27 '15 at 15:29

0

Yes with nativeQuery

@Query(value = "select st from STRATEGY st where st.unId= ?1 order by ( st.totalMargin / st.totalRevenue ) desc", nativeQuery = true)

answered May 27 '15 at 15:29

iamiddy

3,015
3
30
33

How to order by multiple columns in Spring data JPA query with calculation in order by?

2 Answers2