2

i am trying to set the same order in Spring Data for Practice object as illustrated below in mysql:

select * from practice order by FIELD(id, 5, 2, 1, 8, 3) desc

For now, i can set just something like

new Sort.Order(Sort.Direction.DESC, "id")

How can i achieve this?

Aleksei
  • 165
  • 8
  • I don't know if it's possible with column-indices, but [this might perhaps be useful](https://stackoverflow.com/a/48254206/1682559) for sorting on multiple columns in SpringData. – Kevin Cruijssen Sep 30 '19 at 11:48
  • Using ORM frameworks is great until you have to do something which is "non standard".. Maybe you can do `new Sort.Order(Sort.Direction.DESC, "FIELD(id, 5, 2, 1, 8, 3)")`. But most likely you would have to use a raw query here if that framework supports that feature.. – Raymond Nijland Sep 30 '19 at 11:56
  • @RaymondNijland, unfortunately your solution throwing org.springframework.data.mapping.PropertyReferenceException: No property FIELD(id, 5, 2, 1, 8, 3) found for type – Aleksei Sep 30 '19 at 12:07
  • @RaymondNijland are you using spring data jpa as dependency? – Hatice Sep 30 '19 at 12:30
  • *"are you using spring data jpa as dependency?"* @Hatice i believe i did in the past as i did Java programming more then a decade ago.. i believe when you need to made a custom sort you would need to made a Repository and use `@Query("", nativeQuery = true)` in that Repository more or less explained on https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query .. Also notice a RDMS function `LENGHT(firstname)` is used with `JpaSort.unsafe(..)` – Raymond Nijland Sep 30 '19 at 12:58
  • 1
    @RaymondNijland So sorry Raymond, I tagged you wrongly. I want to ask question Aleksei. Because, I will suggest the same documentation him, but I could not understand what he has dependencies. – Hatice Sep 30 '19 at 13:02
  • No worries .. @Hatice atleast i've given some more research options in the manual which seams into getting a solution for the topicstarter – Raymond Nijland Sep 30 '19 at 13:03
  • 1
    *" I want to ask question Aleksei. Because, I will suggest the same documentation him, but I could not understand what he has dependencies. "* ... also the topicstarter could have shared more code indeed @Hatice – Raymond Nijland Sep 30 '19 at 13:06
  • @RaymondNijland, yes, i'am using spring data jpa.As for native query, i can't use it. I have to generate org.springframework.data.domain.Sort object and send it to service layer. So, maybe exists a variant, where i can create only org.springframework.data.domain.Sort object using native sql query? – Aleksei Sep 30 '19 at 13:14
  • *"So, maybe exists a variant, where i can create only org.springframework.data.domain.Sort object using native sql query? "* check [5.3.5. Using Sort](https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.sorting) in the manual? The Document was the same as i provided before assuming `new Sort(..)` in that context is `org.springframework.data.domain.Sort` object, which is not clear in that document.. Maybe in that context you can use `JpaSort.unsafe("FIELD(id, 5, 2, 1, 8, 3) [desc]")` aswell.. – Raymond Nijland Sep 30 '19 at 13:22

0 Answers0