ROOM Database Query syntax. Query runs correctly directly in Database Inspector but not in ROOM DAO query

I want to add a variable WHERE parameter to my DAO query but it is not working as I expect.

@Query("SELECT * FROM item WHERE category isnull and subroomId= :subroomId and :filter ORDER BY category, itemtype, itemname ASC")
fun getitems(subroomId: Long, filter:String = "(1 = 1)": Flow<List<ItemEntity>>

The [:filter] parameter will allow to me to add in the program more complex expressions, for example (itemname like 'test'), and other much more complex expressions, without having to rewrite the DAO query.

The Query with no parameter I think should translate to:

SELECT * FROM item WHERE category isnull and subroomId= 1 and (1=1) ORDER BY category, itemtype, itemname ASC

When I execute the query in the Database Inspector it returns what I expect

Repository code:

fun getitems(subroomId:Long, filter:String = "(1=1)"):Flow<List<ItemEntity>> {
    val member = inventoryDB.itemDao().getitems(subroomId, filter)
    return member
}

ViewModel:

fun items(subroomId: Long, filter:String = "(1 = 1)"): Flow<List<ItemEntity>> {
    val flow: Flow<List<ItemEntity>> = flow {
        val source = repository.getitems(subroomId, filter)
        emitAll(source)
    }
    return flow
}

Inside Composable:

var filteringOptions by remember { mutableStateOf("(1=1)") }
val items by viewModel.items(parentId, filteringOptions).collectAsState(initial = emptyList())

if (items.isEmpty()) {
   Text(text = "No Items Yet", fontSize = 18.sp, color = Color.Cyan)
} else {
   //Show Records
}

View Result: Note Cyan Message

Room Entity Definition:

@Entity(tableName = "Item")
data class ItemEntity(

@PrimaryKey(autoGenerate = true)
val itemId: Long,

@ColumnInfo(name = "subroomId")
val subroomId: Long?,

@ColumnInfo(name = "itemname", collate = ColumnInfo.NOCASE)
val itemname: String,
val itemtype: String,

@ColumnInfo(name = "SN", collate = ColumnInfo.NOCASE)
val SN: String,
@ColumnInfo(name = "snmarker")
var snmarker:Date?,

val quantity: Double,

val holdId: Long?,
@ColumnInfo(name = "category")
val category: String?,

val description: String,

var fototype: Int,
@ColumnInfo(typeAffinity = ColumnInfo.BLOB)
val foto: ByteArray? = null,
var fotoname: String,

val receipt: Boolean,
val receiptId: Long? = null,
val rmarker: Date?,

val purchaseValue: Double,

val warranty: Boolean,
val warrantyId: Long,
val wmarker: Date?,

val itemLocation:String

)

Even the DB Inspector returns rows, ROOM DAO returns empty.

Filter, being a String, will be enclosed in single quotes (by SQLite's parameter binding) so you are effectively using

SELECT * FROM item WHERE category isnull and subroomId= 1 and 'itemname like ''test''' ORDER BY category, itemtype, itemname ASC

and hence unexpected results.

Furthermore Columns, and all element names (such as tables and views), cannot be passed as parameters as parameters are bound according to SQLite parameter binding (i.e. the work is done by the SQLite API).

You have two potential ways of coping with what you want.

1. Either using a raw query which is built noting that they are not validated at compile time. See How to get specific column from android room db?, or

2. Utilise the SQL CASE WHEN THEN ELSE END construct, which could prove to be quite complex. perhaps see how to use Android Room Database and query using SQLite's Case Expression

A third option, which you exclude by saying a single @Dao annotated function, would be to have multiple such functions (also mentioned in the 2nd link).