I am writing SQL query in my android app which I though will work but doesn't. So it goes like this:

@Query("SELECT * FROM ${AppDatabase.GAME_TABLE} WHERE :collectionId IN (themesIds)")
    fun getAllGamesForCollection(collectionId: String): List<GameEntity>

In my Game_Table entity, themeIds field is represented like this:

@ColumnInfo(name = "themesIds")
    var themesIds: List<String> = mutableListOf()

What I wanted is to check what games contain "collectionId" (single string that I send in my code) in their themesIds (List of strings that contain different Ids and are saved in database). I use room library (just for the records).

Why is this not working?

CodePudding user response：

You could use WHERE instr(themesIds,:collectionId) or WHERE themesIds LIKE '%'||:collectionId||'%'.

This does rely upon the stored representation of the List (see below) containing the textual values (if the List is converted to a bytestream then the above would not work without converting the bytes).

However, both are prone to other unwanted matches; for example AAA would also find AAABBB BBBAAACCC as AAA exists in all of these.

The likely reason that IN is not working is that it expects the value after IN to either be a comma separated list of values or the result of a select query. It is unlikely that the data (List of strings that contain different Ids and are saved in database) are just such a CSV it will be a single value holding all of the values of the list.

• based upon the the bolded and italicised quote from the question.

If the weakness mentioned above is an issue then you would have to be more specific about exactly what List of strings that contain different Ids and are saved in database is in fact; it cannot be a List. That is SQLite (the database which Room is a wrapper around) does not store lists it stores single values per column which may reflect an object (List).

• Say the List consists of the elements
  • The
  • Quick
  • Brown
  • Fox

Then the 4 values have to be represented by a single value.

If that single value were The,Quick,Brown,Fox (CSV) then IN would not work as it is a single value (if it were 'The','Quick','Brown','Fox' then it would work but to get that from the column would be quite complex (a recursive subquery that splits the string)).

However, there is no reason why the representation could instead be The;Quick;Brown;Fox in which case IN would not work.

• For Room this would depend upon the TypeConverter used to store the List.

You may wish to edit your question to include an example of the actual data stored in the themesIds column (App Inspection can be used for this)