Room Database Full Dynamic Query

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 OR

Solution 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.

Solution 2:

Below is the example in kotlin language of how the values can be totally dynamic,

@Query("SELECT * FROM Test where testId = :arg0")funloadTestById(testId: Int): Test

Here Test is the tableName, testId is the field on which WHERE clause is applied, testId is the parameter passed to the function loadTestById() and the return type is the data model class named Test.

Query("select * from coin ORDER BY order=:arg0 asc limit numberOfCoins=:arg1")
fungetAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>