Search code examples
databasesortinggrailsmappinggrails-orm

Grails map values before sorting

Is it at all possible to map or transform database query results before sorting them in Grails. What I am trying to accomplish is this:

Lets say I have database column with values:

codes
 01,
 02,
 03,
 04,

And a function that is capable of mapping like this:

{
 01 -> B
 02 -> C
 03 -> A
 04 -> D
}

I would like the end result to be something like this;

[03, 01, 02, 04]

Id like it to sort using the values in the map and not by directly using the values in the database

Solution

  • For anyone that stumbles upon this question.

    The answer Orubel provided would work, but this means you are sorting data in memory.

    One can also make an additional query but then you deal with I/O overhead.

    Unfortunately there is no PERFECT answer for this issue, but when having to choose, the best solution is PROCESSING over I/O as processing will generally be faster than having to do it over a remote connection

    The way I did it was I wrote it in SQL. And to map/transform my data I used SQL CASE statements.(https://www.w3schools.com/sql/sql_case.asp) And then placed that pice of SQL inside a WITH clause to query the data as needed. (https://www.geeksforgeeks.org/sql-with-clause/)

    This adds I/O and isn't perfect but it was my solution