Issue with SupportSQLiteQueryBuilder's SELECTION method - Room Raw Query

Issue

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.

1. The column name (for the WHERE condition)
2. 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
}

Solution

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

Answered By - CommonsWare