Room user configurable order by queries

Question

I am migrating an app to use Room from normal Sqlite and one part that I am having trouble with is that there are several queries that have an order by statement that are user configurable, meaning they can change how they want to view the list order.

What it seems is the Room does not allow for dynamic order by statements so I would have to make individual queries specific for each order by statement.

Has anyone found a better way around this issue so I can have 1 query statement where the only thing that changes is the order by clause vs having to write what in my case would be about 15 extra query statements all basically the same?

Answer 1

I'm facing the same problem right now. The thing that can be done is to use CASE:

SELECT * FROM Table
ORDER BY 
CASE WHEN :parameter = 1 THEN Column END ASC,
CASE WHEN :parameter = 2 THEN Column2 END DESC

But I'm assuming you are using a ViewModel as well, so you will probably have to reinitialise it every time. I think this way is a little bit better than writing 15 queries, maybe not as compact though.

This answer also has a nice example, but the logic is reversed I believe.