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

I'm presuming this is Kotlin code and tagged it accordingly. If that's wrong, please edit. — tadman, Jan 09 '18 at 17:17
Your example shows using a parameter (bind value) for the ORDER BY clause not the WHERE clause. — Sentinel, Jan 09 '18 at 17:24

Sentinel · Accepted Answer · 2018-01-09T17:32:27.173

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.

This won't work. Unfortunately, you need to write separate queries. — Kristy Welsh, Oct 11 '19 at 20:18

hetsgandhi · Answer 2 · 2018-07-06T10:46:30.017

2

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

edited Jul 06 '18 at 10:46

answered Jul 06 '18 at 10:07

hetsgandhi

726
3
9
25

@Query("SELECT * FROM Test where testId = :arg0") arg0 - can't resolve – Vladyslav Ulianytskyi Feb 15 '19 at 11:35
@Vladyslav Ulianytskyi Can you show me your query? so that i can know what problem you are facing. – hetsgandhi Feb 15 '19 at 12:58
@Query("SELECT * FROM History WHERE date = :date") fun getTodayHistory(date: Date): List have tried: WHERE date = Date(:date) or WHERE date = date(:date) but with Date it not working.. – Vladyslav Ulianytskyi Feb 15 '19 at 13:56
@Vladyslav Ulianytskyi Try this way: @Query("SELECT * FROM History WHERE date = :arg0") fun getTodayHistory(date: Date): – hetsgandhi Feb 16 '19 at 04:58

Room database full dynamic query

2 Answers2

Linked