如何在“房间查询"中将字符串列表转换为LIKE子句?

问题描述

我有一个名为games的表，该表具有一个名为platforms的列，其中包含平台缩写的列表.这是特定游戏出现的所有平台的列表.这是platforms中的单元格之一的示例:

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

因此，我需要一种以编程方式构造房间"查询的方法，该查询可以在所选平台上找到游戏.我需要以某种方式生成任意数量的LIKE子句，并将其注入查询中.我尝试了以下操作，但未返回任何结果:

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

我猜RawQuery可以解决这个问题吗?但是还有另一种方法吗?

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

推荐答案

您可以使用 @RawQuery 并动态构建 SimpleSQLiteQuery :

You can use @RawQuery and build SimpleSQLiteQuery dynamically:

在岛上

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

此处buildFinalQuery功能:

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)

这篇关于如何在“房间查询"中将字符串列表转换为LIKE子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！