Issue with SupportSQLiteQueryBuilder's SELECTION method - Room Raw Query

Question

I am trying to generate a RawQuery using SupportSQLiteQueryBuilder and it worked well but when I tried adding the selection method for generating a WHERE condition it just doesn't work.

Referred the docs from here: https://developer.android.com/reference/androidx/sqlite/db/SupportSQLiteQueryBuilder#selection(java.lang.String,%20java.lang.Object[]) and found out that the method takes in two arguments.

The column name (for the WHERE condition)
The value

Tried it but somehow, the second argument i.e bindArgs array is not added to the query

The query I want:

SELECT * FROM plants WHERE plantOrigin = "Japan"

The query being generated:

SELECT * FROM plants WHERE plantOrigin

Tried this answer https://stackoverflow.com/a/56780629/8442557 but still couldn't get the desired results.

Dao

@RawQuery(observedEntities = [Plant::class])
fun getPlantsFromOrigin(query: SupportSQLiteQuery): LiveData<List<Plant>>

Repository

 fun getPlantsFromOrigin(origin: String, isFavorite: Boolean = false): LiveData<List<Plant>> {
    return plantDao.getPlantsFromOrigin(QueryUtils().getPlantsBasedOnOrigin(origin, isFavorite))
}

QueryUtils class - getPlantsBasedOnOrigin method

fun getPlantsBasedOnOrigin(origin: String, showOnlyFav: Boolean): SimpleSQLiteQuery {
    val queryBuilder = SupportSQLiteQueryBuilder
        .builder(TABLE_NAME)
        .selection(COL_ORIGIN, arrayOf(origin))
    if (showOnlyFav) {
        queryBuilder.selection(COL_FAVORITE, arrayOf("1"))
    }
    val query = SimpleSQLiteQuery(queryBuilder.create().sql)
    Log.d("Generated SQL query", query.sql)
    return query
}

Based on the implementation of `SupportSQLiteQueryBuilder`, it looks like `COL_ORIGIN` would need to be `plantOrigin = ?`, and you would get `query` via `val query = queryBuilder.create()`. As it stands, you are dropping `arrayOf(origin)` by only extracting `sql` from the created query. — CommonsWare, Nov 08 '21 at 13:34
Hi, thanks for the response. So do you mean changing to this - .selection("$COL_ORIGIN = ?", arrayOf(origin)) — Shizuku, Nov 08 '21 at 13:40
Tried updating as suggested - `private const val COL_ORIGIN: String = "plantOrigin = ?"` but still this is the generated SQL that I am getting - **SELECT * FROM plants WHERE plantOrigin = ?** — Shizuku, Nov 08 '21 at 13:46
"still this is the generated SQL that I am getting" -- that would appear to be the correct SQL. Did you fix `query` as well, as I originally suggested? Right now, your `arrayOf(origin)` is pointless, because you are only using `queryBuilder` to generate SQL, rather than using it to generate the `SupportSQLiteQuery`. — CommonsWare, Nov 08 '21 at 14:10
Thanks a ton! It works. I changed the returning value to this - `return queryBuilder.create()` Now I am getting the required results. Thank you! :) — Shizuku, Nov 08 '21 at 15:07

score 1 · Accepted Answer · answered Nov 08 '21 at 15:12

The documentation for SupportSQLiteQueryBuilder is limited.

For selection(), the first parameter is the actual WHERE clause content, minus the WHERE keyword, and using ? where any arguments should go. So, in your case, that would be plantOrigin = ?.

Then, if you use selection(), you need to use the query returned by create() completely. That contains both the generated SQL and the second parameter that you passed to selection(). In your case, you were just extracting the generated SQL, which means that you lost your plant origin value to use in place of the ?.

score 0 · Answer 2 · answered Nov 14 '21 at 03:46

The answer provided by CommonsWare helped me. Posting the solution code implemented with the help of the answer for reference. Note: If you want to return SupportSQLiteQuery use queryBuilder.create() but I wanted to return SimpleSQLiteQuery, so I used this -

SimpleSQLiteQuery(queryBuilder.create().sql + " AND $COL_FAVORITE", arrayOf(origin, getFavoriteValue(showOnlyFav)))

The reason why I hardcoded the COL_FAVORITE is because a second selection() is overwriting the first one but I wanted both. Would really appreciate it if anyone could provide a better solution rather than hardcoding it.

fun getPlantsBasedOnOrigin(origin: String, showOnlyFav: Boolean): SimpleSQLiteQuery {
    val queryBuilder = SupportSQLiteQueryBuilder
        .builder(TABLE_NAME)
        .selection(COL_ORIGIN, arrayOf(origin))
    val getFav = if (showOnlyFav) "1" else "0"
    val query = SimpleSQLiteQuery(
        queryBuilder.create().sql + " AND $COL_FAVORITE",
        arrayOf(origin, getFav)
    )
    return query
}

Don't forget to add "= ?" for your column names as mentioned by CommonsWare (refer the accepted answer)

private const val TABLE_NAME: String = "plants"
private const val COL_FAVORITE: String = "isFavorite = ?"
private const val COL_ORIGIN: String = "plantOrigin = ?"

Issue with SupportSQLiteQueryBuilder's SELECTION method - Room Raw Query

2 Answers2