0

I have a table like below in Room, in a Android application, I use Raw Query to get data. Can it be sorted by second value in array sorting_field?

---------------------------------------------
| id    | other_fields  | sorting_field     |
---------------------------------------------
| 1001  |   …           | ["24","0.02","2"] |
---------------------------------------------

Initially I did the sorting part in Repository with Transformations.switchMap, inside the function a MutableLiveData> and applied Collections.sort.

It worked like a charm: 

Collections.sort(list, (o1, o2) -> Double.compare(Double.valueOf(o1.sorting_field().get(positionInList)), Double.valueOf(o2.sorting_field().get(positionInList))));

After Paging implementation, I took the sorting logic out, moved to queries builder and here I am.

Rares Serban
  • 151
  • 3
  • 9
  • SQLite does not support arrays to begin with, so I doubt you can do a proper sort on the "second" element of a comma delimited string that is surrounded with square brackets. –  Jul 29 '19 at 09:53
  • I know that. I was looking for a solution that does not implies taking the String [] to another table and use INNER, as I have 12 such fields. – Rares Serban Jul 29 '19 at 13:09
  • 1
    If the version of SQLite you're using has the [JSON extension](https://www.sqlite.org/json1.html) built in, you may be able to do something like `select * from table_name order by json_extract( sorting_field, $[1] )` where `$[1]` will select the second field in the json-like string field. Because your example uses an array of strings, if you need "numeric" sorting, you would need `...order by cast( json_extract(...) as real )` (or `as integer`). – TripeHound Jul 29 '19 at 14:27
  • Looks like in Android, extensions (like the beautifull JSON1), are disabled by default. [link](https://android.googlesource.com/platform/external/sqlite/+/master/dist/Android.bp) – Rares Serban Jul 30 '19 at 07:04

0 Answers0