88

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:

  1. Passing the argument as a List<String>
  2. Passing the argument as a String[]
  3. And lastly passing the argument as simply a 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.

Phantômaxx
  • 37,901
  • 21
  • 84
  • 115
VerumCH
  • 2,995
  • 2
  • 15
  • 22
  • 2
    https://stackoverflow.com/a/48370198/115145 – CommonsWare Jan 23 '18 at 16:24
  • @CommonsWare Yea, that would have fixed my issue immediately. Unfortunately, I never ran across that question in all of my searches since I was looking specifically for information regarding Room and `SELECT` with an `IN` condition (which aren't actually mentioned in that question). – VerumCH Jan 23 '18 at 16:27
  • Note that all of those things are mentioned in [the question](https://stackoverflow.com/q/48370045/115145). – CommonsWare Jan 23 '18 at 16:29

3 Answers3

152

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:

  1. Passing the argument as a List<String>
  2. Passing the argument as a 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.

VerumCH
  • 2,995
  • 2
  • 15
  • 22
  • Any way to get this to work with custom classes? eg. enums. It doesn't seem to use TypeConverter to convert lists of custom classes. – Tom Jun 06 '19 at 03:15
  • Not sure if I understand entirely - you want to search through a collection/list of custom objects? Custom objects/classes can be used in Room with `@TypeConverter`s, but that's used to convert an entire table (or a subset of that table's columns) into a class (or vice-a-versa). SQL queries search/index individual columns, which must still be SQL-supported data types (String, Int, etc.). What you can do instead of passing a list of custom objects is have multiple arguments corresponding to certain fields in those objects, which each map to a single column. – VerumCH Jun 13 '19 at 23:18
  • eg `@Query("SELECT * FROM agenda WHERE day IN (:days)") fun getAgenda(days: List)` where `enum class DayOfWeek { MON, TUES, ... }`. A `TypeConverter` can be used to query/fetch `DayOfWeek` in all other contexts, except (it seems) for `IN` – Tom Jun 16 '19 at 20:52
  • I'm guessing the issue is the the `TypeConverter` doesn't know how to parse the List. Try making a converter that accepts a List and spits out a List (I'm assuming it's an int or string). Alternatively, just build that List yourself and send that as the argument. – VerumCH Jun 16 '19 at 21:33
  • Looks like this may have been fixed in 2.1.0-alpha07 :) https://issuetracker.google.com/issues/122066791 – Tom Jun 17 '19 at 01:24
56

Hi you can use this query:

 @Query("SELECT * FROM user WHERE uid IN(:userIds)")
 public abstract List findByIds(int[] userIds);

or

 @Query("SELECT * FROM user WHERE uid IN(:userIds)")
 public abstract List findByIds(List<Integer> userIds);
Ahmad Aghazadeh
  • 16,571
  • 12
  • 101
  • 98
19

Similarly to above answers in Kotlin you can use vararg instead of array or list:

@Query("SELECT * FROM user WHERE uid IN (:userIds)")
fun getUsers(vararg userIds: Int): List<User>?

and use it like repository.getUsers(1, 2, 3).

If needed, to convert vararg to list see https://proandroiddev.com/kotlins-vararg-and-spread-operator-4200c07d65e1 (use *: format(output, *params)).

CoolMind
  • 26,736
  • 15
  • 188
  • 224