CODEWITHSUNDEEP
androidmysqlsqlitekotlinandroid-room
Shahar
Shahar

Reputation: 3692

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

Upvotes: 8

Views: 7899

Answers (2)

hetsgandhi
hetsgandhi

Reputation: 806

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

@Query("SELECT * FROM Test where testId = :arg0")
fun loadTestById(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")
fun getAllTop(order: String, numberOfCoins: Int): Flowable<List<CoinDB>>

Upvotes: 2

Sentinel
Sentinel

Reputation: 6449

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.

Upvotes: 18

Related Questions