How can I turn a list of strings into LIKE clauses in a Room Query?

Question

I have a table called games that has a column called platforms, which contains a list of platform abbreviations. This is a list of all the platforms that specific game came out on. Here's an example of one of the cells in platforms:

AMI,GG,SNES,CPC,AST,C64,SPEC,MAC,PS2,NES,3DO,ARC,XBGS,PS3N,PC,IPHN,DSI,HALC,PSPN,ANDR,

The user can choose any number of platforms they wish to view games for. For example, they may choose to see games for the following platforms:

SNES, MAC, PC

So I need a way to programmatically construct a Room query that would find games in the selected platforms. I need to somehow generate an arbitrary number of LIKE clauses and inject them into the query. I tried the following, but it returned no results:

private fun fetchLikeClauses(platformIndices: MutableSet<Int>): String {
    val sb = StringBuilder()

    // Game.platforms LIKE '%XONE%' OR Game.platforms LIKE '%PC%'

    for (platformIndex in platformIndices) {
        sb.append("platforms LIKE '%${allPlatforms[platformIndex].abbreviation}%'")
        sb.append(" OR ")
    }
    return sb.toString().removeSuffix(" OR ")
}




@Query("SELECT * FROM Game WHERE :likeClauses")
fun getGames(likeClauses: String): DataSource.Factory<Int, Game>

Here's another thing I tried, but it didn't work for some reason: Passing in a string to use as part of a Room query

I'm guessing RawQuery would work for this? Is there another way though?

Answer 1

You can use @RawQuery and build SimpleSQLiteQuery dynamically:

In dao:

@RawQuery(observedEntities = [Game::class])
fun getGames(query: SupportSQLiteQuery): DataSource.Factory<Int, Game>

Here buildFinalQuery function:

fun buildFinalQuery(platforms: List<String>): SimpleSQLiteQuery {
    val selectQuery = "SELECT * FROM Game"

    val finalQuery = selectQuery + platforms.joinToString(prefix = " WHERE ", separator = " OR ") {
        "Game.platforms LIKE '%$it%'"
    }

    return SimpleSQLiteQuery(finalQuery)
}

val query = buildFinalQuery("SNES", "MAC", "PC")
dao.getGames(query)