0

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?

Gavin Wright
  • 3,124
  • 3
  • 14
  • 35

2 Answers2

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)
Natig Babayev
  • 3,128
  • 15
  • 23
1

I think your solution does not work because Room automatically escapes your generated string to prevent a SQL Injection.

To solve your problem you should use the SQL-In operator.
Read more about the SQL-IN operator

Your DAO could look like this

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

Here is a StackOverflow Post with the same solution

NOTE THIS SOLUTION ONLY WORKS WITH EXACT SEARCH RESULTS
To support upper and lower case you could use the SQL UPPER or LOWER function. 

SELECT * FROM Game
WHERE UPPER(platforms) IN ('AMI', 'SNES', 'PC');
Robin
  • 561
  • 1
  • 4
  • 16
  • IN and LIKE is not same: https://stackoverflow.com/a/1865377/4699509 – Natig Babayev Mar 06 '20 at 09:10
  • You're probably right about the SQL injection thing. But `IN` doesn't work here because we need to search through a list of platforms. We're not looking for exact matches. – Gavin Wright Mar 06 '20 at 09:11
  • @GavinWright you're right SQL-IN needs a exact search result. But if you only want to consider upper and lower case, you can also use UPPER/LOWER. ``` SELECT * FROM Game WHERE UPPER(platforms) IN ('AMI', 'SNES', 'PC'); ``` – Robin Mar 06 '20 at 09:22
  • The case isn't the problem. We have two lists: 1) each cell in the `platforms` column of the database is a list, and 2) the user-selected platforms also comprise a list. So we need to search list 1 to see if it contains any instances of the items in list 2, and we need to do that for every row in the database. – Gavin Wright Mar 06 '20 at 09:37