android - Android Room 搜索和过滤使用关系，查询多个表

问题描述

如果您想发出拉取请求，请在下面添加评论。

我为我的 PokemonApp 创建了一个 Room 数据库，我希望能够根据 Pokemon 名称和 Pokemon 类型过滤和搜索数据库。

我有一个用于 Pokemon 实体的表、一个用于 PokemonType 实体的表和一个用于 PokemonTypeJoin 实体的连接表，我还有一个数据类 PokemonWithTypes，它嵌入了一个 Pokemon 实体并定义了它与 PokemonType 实体列表之间的关系。

宝可梦实体：

    @TypeConverters(RoomStringListConverter::class)
    @Entity
    data class Pokemon(
        @NotNull
        @PrimaryKey
        @ColumnInfo(name = POKEMON_ID)
        var id: Int,
    
        @ColumnInfo(name = POKEMON_NAME)
        var name: String,
    
        @ColumnInfo(name = POKEMON_URL)
        var url: String,
    
        @ColumnInfo(name = POKEMON_WEIGHT)
        val weight: Int,
    
        @ColumnInfo(name = POKEMON_HEIGHT)
        val height: Int,
    
        @ColumnInfo(name = POKEMON_SPECIES)
        var species: String,
    
        @ColumnInfo(name = POKEMON_MOVES)
        val moves: List<String>
    
    ) 

    const val POKEMON_ID: String = "pokemon_id"
    const val POKEMON_NAME: String = "pokemon_name"
    const val POKEMON_URL: String = "pokemon_url"
    const val POKEMON_HEIGHT: String = "pokemon_height"
    const val POKEMON_WEIGHT: String = "pokemon_weight"
    const val POKEMON_MOVES: String = "pokemon_moves"
    const val POKEMON_SPECIES: String = "pokemon_species"

PokemonType 实体：

    @Entity
    data class PokemonType (
    
        @NotNull
        @PrimaryKey
        @ColumnInfo(name = POKEMON_TYPE_ID)
        var id: Int,
    
        @ColumnInfo(name = POKEMON_TYPE_NAME)
        var name: String,
    
        @ColumnInfo(name = POKEMON_TYPE_SLOT)
        var slot: Int
    
    )

    const val POKEMON_TYPE_ID: String = "type_id"
    const val POKEMON_TYPE_NAME: String = "type_name"
    const val POKEMON_TYPE_SLOT: String = "type_slot"

PokemonTypesJoin 实体：

    @Entity(primaryKeys = [POKEMON_ID, POKEMON_TYPE_ID])
    class PokemonTypesJoin(
        @NotNull
        @ColumnInfo(name = POKEMON_ID, index = true)
        val pokemon_id: Int,
    
        @NotNull
        @ColumnInfo(name = POKEMON_TYPE_ID, index = true)
        val pokemon_type_id: Int
    
    )
    
    const val POKEMON_ID: String = "id"
    const val POKEMON_TYPE_ID: String = "type_id"

PokemonWithTypes 类

    data class PokemonWithTypes(
        @Embedded
        val pokemon: Pokemon,
        @Relation(
            parentColumn = Pokemon.POKEMON_ID,
            entity = PokemonType::class,
            entityColumn = PokemonType.POKEMON_TYPE_ID,
            associateBy = Junction(
                value = PokemonTypesJoin::class,
                parentColumn = PokemonTypesJoin.POKEMON_ID,
                entityColumn = PokemonTypesJoin.POKEMON_TYPE_ID
            )
        )
        val types: List<PokemonType>
    )

鉴于此结构，我可以使用以下查询通过 pokemon_name 所有 PokemonWithTypes 获取和搜索：

@Transaction
@Query("SELECT * FROM pokemon WHERE pokemon_name LIKE :search ORDER BY pokemon_id ASC")
fun getPokemonWithTypes(search: String?): LiveData<List<PokemonWithTypes>>

但是我现在如何添加一个过滤器（字符串列表），它只返回 PokemonWithTypes ，其中任何 PokemonWithTypes.types 与过滤器列表中的给定类型匹配？

所以给定 3 个口袋妖怪（为简洁起见，删除了一些数据）

PokemonWithTypes(pokemon=Pokemon(id=1, name=bulbasaur, types=[PokemonType(id=4, name=poison, slot=2), PokemonType(id=12, name=grass, slot=1)])
PokemonWithTypes(pokemon=Pokemon(id=4, name=charmander, types=[PokemonType(id=10, name=fire, slot=2), PokemonType(id=12, name=grass, slot=1)])
PokemonWithTypes(pokemon=Pokemon(id=7, name=squirtle, types=[PokemonType(id=11, name=water, slot=2), PokemonType(id=12, name=grass, slot=1)])

我目前得到所有口袋妖怪并且可以通过口袋妖怪名称搜索但我希望能够只显示水类型或只显示草类型欢迎任何想法，

我尝试使用这样的查询过滤字符串而不是字符串列表

@Transaction
@Query("SELECT * FROM pokemon, pokemonType WHERE type_name LIKE :filter AND pokemon_name LIKE :search ORDER BY pokemon_id ASC")
fun getPokemonWithTypes(search: String?, filter: String): LiveData<List<PokemonWithTypes>>

但它没有用

你可以在这里查看完整的东西https://github.com/martipello/PokeApp/tree/add_filters

标签： androidsqlandroid-room

android - Android Room 搜索和过滤使用关系，查询多个表

问题描述

解决方案

推荐阅读