Is it possible to use SQLite's IN
condition with Room?
I'm trying to select a list of items from my database where the value of a certain column (in this case a TEXT
column) matches any one of a set of filter values. That's pretty easily done in SQL and SQLite, by my knowledge, just by adding an IN
condition to your SELECT
statement (see here). However, I can't seem to make it work with Room.
I keep getting this error:
Error:(70, 25) error: no viable alternative at input 'SELECT * FROM Table WHERE column IN :filterValues'
(where the input to the DAO @Query
-annotated method is called filterValues
)
I have tried three different methods now:
List<String>
String[]
String
, but formatted as (value_1, value_2, ..., value_n)
The last one in particular should work easily, as it will (or at least, it should) directly translate to SELECT * FROM Table WHERE column IN (value_1, value_2, ..., value_n)
, which is the exact way you would manually write out the SELECT
if you were just accessing the database directly.
So as I was preparing to submit this, I double-checked a bunch of the stuff I had looked up previously and found the thing I had somehow missed and would have saved this question from being necessary.
As it turns out, both of these options:
List<String>
String[]
are viable (and you can replace String
with any type the database can represent, such as char
or int
), you simply need to change the syntax in the @Query
annotation from this:
@Query("SELECT * FROM Table WHERE column IN :filterValues")
to this:
@Query("SELECT * FROM Table WHERE column IN (:filterValues)")
Easy as pie, right?
Note that the third method above (passing the argument as simply a String
, but formatted as (value_1, value_2, ..., value_n)
) does not appear to be supported by Room, but that's probably not a bad thing, since that's the hard way.
Since I already had the whole thing typed out, I figured I would leave the question up in case other people are have as much difficulty finding this solution as I did and stumble upon this question.