Usage of room database querying with IN operator providing list of Enums

Question

Querying room database with List of Enums as an argument yields results only when a single Enum is provided in the list and returns no results when multiple Enums in list. Given a list of a single Enum in the query the resuls returned will only be those that match exactly that Enum and no results that consist of that Enum and other Enums combined.

However, according to this post results should be returned that match a containing element. Additionally, android documentation outlines the expected behaviour to be the same as far as i understand. I found this issue tracked which is of a very similar nature, albeit, not exact which led to a merge in AOSP but has provided little insight into my issue.

Any help appreciated, code below:

Enum

enum class Type(val type: String) {
    NORMAL("normal"),
    FIGHTING("fighting"),
    FLYING("flying"),
    POISON("poison"),
    GROUND("ground"),
    ROCK("rock"),
    BUG("bug"),
    GHOST("ghost"),
    FIRE("fire"),
    WATER("water"),
    GRASS("grass"),
    ELECTRIC("electric"),
    PSYCHIC("psychic"),
    ICE("ice"),
    DRAGON("dragon"),
    FAIRY("fairy"),
    STEEL("steel");
}

Dao

@Query("SELECT * from pokemon_table WHERE types IN (:typesList)")
fun getFilteredPokemon(typesList: List<Type>): LiveData<List<Pokemon>>

Type Converter

@TypeConverter
fun fromString(value: String?): List<Type>? {
    val listType = object : TypeToken<List<Type>>() {}.type
    return Gson().fromJson(value, listType)
}

@TypeConverter
fun fromList(list: List<Type>?): String? {
    val listType = object : TypeToken<List<Type>>() {}.type
    return Gson().toJson(list, listType)
}

Here is a shot of the database to illustrate the values stored, querying with listOf(Type.FIRE) will return charmeleon as a result, but not charizard.

Just to clarify what do you expect by `IN` operator - should your query return something with `listOf(Type.FIRE,Type.GRASS)` if your database has just those 6 rows? — sergiy tikhonov, Jul 28 '20 at 20:16
With just the 6 rows above, querying with fire and grass I would expect all 6 results. — JakeB, Jul 28 '20 at 20:21
As far as I understand, Room uses TypeConverter in query just to turn your List to String and then Room sends modified query to sqlite. Then indeed `"[FIRE]"` in `["FIRE"]` gives `true`, and `["FIRE", "FLYING"]` in `["FIRE"]` gives `false`. I don't honestly know simple decision for what you want (without change you tables' structure to use relations instead of JSON String). — sergiy tikhonov, Jul 28 '20 at 20:46
Correct, this could be reworked to use a Type table and structure the query in a way that returns what I want. The question now on my mind is if IN is any different to LIKE, and what purpose it serves. Thanks for your help. — JakeB, Jul 28 '20 at 21:00

JakeB · Accepted Answer · 2020-08-02T18:21:55.030

As sergiy-tikhonov identified in his comment, the sql IN operator only returns results matched on a single occurance in the list provided.

I've managed to rectify my issue by assigning each entry two colums for 'Type' rather than a single column that contains a list of 'Type' (since each pokemon can only have two types this works without the use of a relationship between tables). Allocating each entry a primary and secondary Type allows me to query both columns with a single list of types:

@Query("SELECT * FROM pokemon_table WHERE type_primary IN (:types) UNION SELECT * FROM pokemon_table WHERE type_secondary IN (:types)")
fun getFilteredPokemon(types: List<Type>): LiveData<List<Pokemon>>

I had to modify the TypeConverter to achieve this:

class TypeConverter {
    @TypeConverter fun fromString(value: String) = Type.getTypeByName(value)
    @TypeConverter fun fromList(type: Type) = type.type
}

Usage of room database querying with IN operator providing list of Enums

1 Answers1