Room database full dynamic query

Question

I Have room database with Object coin.

I want to make a dynamic query with parameter.

When i use the parameter for a value, it works great, like this:

@Query("select * from coin ORDER BY percent_change_24h asc limit :numberOfCoins")
fun getAllTop(numberOfCoins: Int): Flowable<List<CoinDB>>

But when i want to use a parameter for the WHERE clause, that does not work. here is my query:

@Query("select * from coin ORDER BY :order asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

And I call it like that:

AppDatabase.getInstance(this).coinDao().getAllTop("percent_change_24h",5)

Calling the same query with implicit WHERE clause work fine (like this:)

@Query("select * from coin ORDER BY percent_change_24h asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

Answer 1

You can't use bind variables (parameters) to reference columns in the ORDER BY clause. However, you can use the bind variable in an expression like so:

@Query("select * from coin ORDER BY
CASE :order
WHEN 'percent_change_24h' THEN percent_change_24h
WHEN 'other_column_name' THEN other_column_name
END asc limit :numberOfCoins")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

You will need to add a separate WHEN clause to the CASE statement for each column/expression you want to sort by, and you may need or want to add an ELSE clause for the situations where the :order bind variable doesn't match any of your standard cases.

The restriction on bind variables also holds true for the where clause and the projection (select list). Bind variable have their own values in your examples either String or Int for :order and :numberOfCoins respectively.