Hal pertama, data saya POKEMON!!! selamat menikmati

Saya perlu melakukan ini di sisi basis data, memfilter dan menyortir data yang dikembalikan bukanlah pilihan karena menggunakan Paging...

Saya menggunakan Kamar Saya memiliki basis data yang berfungsi dengan baik tetapi sekarang saya ingin menanyakan daftar pokemonType di relasi saya

Mengingat kelas data ini

data class PokemonWithTypesAndSpecies @JvmOverloads constructor(

    @Ignore
    var matches : Int = 0,

    @Embedded
    val pokemon: Pokemon,
    @Relation(
        parentColumn = Pokemon.POKEMON_ID,
        entity = PokemonType::class,
        entityColumn = PokemonType.TYPE_ID,
        associateBy = Junction(
            value = PokemonTypesJoin::class,
            parentColumn = Pokemon.POKEMON_ID,
            entityColumn = PokemonType.TYPE_ID
        )
    )
    val types: List<PokemonType>,
    @Relation(
        parentColumn = Pokemon.POKEMON_ID,
        entity = PokemonSpecies::class,
        entityColumn = PokemonSpecies.SPECIES_ID,
        associateBy = Junction(
            value = PokemonSpeciesJoin::class,
            parentColumn = Pokemon.POKEMON_ID,
            entityColumn = PokemonSpecies.SPECIES_ID
        )
    )
    val species: PokemonSpecies?
)

Saya bisa mendapatkan data saya dengan kueri sederhana dan bahkan mencarinya

@Query("SELECT * FROM Pokemon WHERE pokemon_name LIKE :search")
fun searchPokemonWithTypesAndSpecies(search: String): LiveData<List<PokemonWithTypesAndSpecies>>

Tapi sekarang yang saya inginkan adalah menambahkan pemfilteran pada jenis pokemon yang seperti yang Anda lihat adalah daftar (yang mungkin merupakan transaksi di bawah tenda) dan berada di tabel terpisah, jadi diberi daftar string yang disebut filter, saya ingin:

  • hanya kembalikan pokemon yang berisi item dalam filter
  • urutkan pokemon berdasarkan jumlah jenis yang cocok dan berdasarkan ID

Jadi saya ingin tes saya terlihat seperti ini

val bulbasaurSpeciesID = 1
val squirtleSpeciesID = 2
val charmanderSpeciesID = 3
val charizardSpeciesID = 4
val pidgeySpeciesID = 5
val moltresSpeciesID = 6

val bulbasaurID = 1
val squirtleID = 2
val charmanderID = 3
val charizardID = 4
val pidgeyID = 5
val moltresID = 6

val grassTypeID = 1
val poisonTypeID = 2
val fireTypeID = 3
val waterTypeID = 4
val flyingTypeID = 5

val emptySearch = "%%"

val allPokemonTypes = listOf(
    "normal",
    "water",
    "fire",
    "grass",
    "electric",
    "ice",
    "fighting",
    "poison",
    "ground",
    "flying",
    "psychic",
    "bug",
    "rock",
    "ghost",
    "dark",
    "dragon",
    "steel",
    "fairy",
    "unknown",
)


@Before
fun createDb() {
    val context = ApplicationProvider.getApplicationContext<Context>()
    db = Room.inMemoryDatabaseBuilder(
        context, PokemonRoomDatabase::class.java,
    ).setTransactionExecutor(Executors.newSingleThreadExecutor())
        .allowMainThreadQueries()
        .build()
    pokemonDao = db.pokemonDao()
    speciesDao = db.pokemonSpeciesDao()
    speciesJoinDao = db.pokemonSpeciesJoinDao()
    pokemonTypeDao = db.pokemonTypeDao()
    pokemonTypeJoinDao = db.pokemonTypeJoinDao()
}

@After
@Throws(IOException::class)
fun closeDb() {
    db.close()
}

@Test
@Throws(Exception::class)
fun testFiltering() {

    insertPokemonForFilterTest()

    val pokemon =
        pokemonDao.searchAndFilterPokemon(search = emptySearch, filters = allPokemonTypes)
            .getValueBlocking(scope)

    assertThat(pokemon?.size, equalTo(6)) // This fails list size is 9 with the current query

    val pokemonFiltered =
        pokemonDao.searchAndFilterPokemon(search = emptySearch, filters = listOf("fire", "flying"))
            .getValueBlocking(scope)

    assertThat(pokemon?.size, equalTo(4))
    
    assertThat(pokemonFiltered!![0].pokemon.name, equalTo("charizard")) // matches 2 filters and ID is 4
    assertThat(pokemonFiltered!![1].pokemon.name, equalTo("moltres")) // matches 2 filters and ID is 6
    assertThat(pokemonFiltered!![2].pokemon.name, equalTo("charmander")) // matches one filter and ID is 3
    assertThat(pokemonFiltered!![3].pokemon.name, equalTo("pidgey")) // mayches one filter and ID is 5

}

private fun insertPokemonForFilterTest() = runBlocking {
    insertBulbasaur()
    insertSquirtle()
    insertCharmander()
    insertCharizard()
    insertMoltres()
    insertPidgey()
}



private fun insertBulbasaur() = runBlocking {

    val bulbasaur = bulbasaur()
    val grassJoin = PokemonTypesJoin(pokemon_id = bulbasaurID, type_id = grassTypeID)
    val poisonJoin = PokemonTypesJoin(pokemon_id = bulbasaurID, type_id = poisonTypeID)
    val bulbasaurSpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = bulbasaurID, species_id = bulbasaurSpeciesID)

    pokemonDao.insertPokemon(bulbasaur.pokemon)

    speciesDao.insertSpecies(bulbasaur.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(bulbasaurSpeciesJoin)

    pokemonTypeDao.insertPokemonType(pokemonType = bulbasaur.types[0])
    pokemonTypeDao.insertPokemonType(pokemonType = bulbasaur.types[1])
    pokemonTypeJoinDao.insertPokemonTypeJoin(grassJoin)
    pokemonTypeJoinDao.insertPokemonTypeJoin(poisonJoin)
}

private fun insertSquirtle() = runBlocking {

    val squirtle = squirtle()
    val squirtleSpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = squirtleID, species_id = squirtleSpeciesID)
    val waterJoin = PokemonTypesJoin(pokemon_id = squirtleID, type_id = waterTypeID)

    pokemonDao.insertPokemon(squirtle.pokemon)

    speciesDao.insertSpecies(squirtle.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(squirtleSpeciesJoin)

    pokemonTypeDao.insertPokemonType(pokemonType = squirtle.types[0])
    pokemonTypeJoinDao.insertPokemonTypeJoin(waterJoin)

}

private fun insertCharmander() = runBlocking {

    val charmander = charmander()
    val fireJoin = PokemonTypesJoin(pokemon_id = charmanderID, type_id = fireTypeID)
    val charmanderSpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = charmanderID, species_id = charmanderSpeciesID)

    pokemonDao.insertPokemon(charmander.pokemon)
    speciesDao.insertSpecies(charmander.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(charmanderSpeciesJoin)
    pokemonTypeDao.insertPokemonType(pokemonType = charmander.types[0])
    pokemonTypeJoinDao.insertPokemonTypeJoin(fireJoin)
}

private fun insertCharizard() = runBlocking {

    val charizard = charizard()
    val charizardSpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = charizardID, species_id = charizardSpeciesID)

    val fireJoin = PokemonTypesJoin(pokemon_id = charizardID, type_id = fireTypeID)
    val flyingJoin = PokemonTypesJoin(pokemon_id = charizardID, type_id = flyingTypeID)

    pokemonDao.insertPokemon(charizard.pokemon)

    speciesDao.insertSpecies(charizard.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(charizardSpeciesJoin)

    pokemonTypeDao.insertPokemonType(pokemonType = charizard.types[0])
    pokemonTypeDao.insertPokemonType(pokemonType = charizard.types[1])
    pokemonTypeJoinDao.insertPokemonTypeJoin(fireJoin)
    pokemonTypeJoinDao.insertPokemonTypeJoin(flyingJoin)
}

private fun insertPidgey() = runBlocking {

    val pidgey = pidgey()
    val pidgeySpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = pidgeyID, species_id = pidgeySpeciesID)
    val flyingJoin = PokemonTypesJoin(pokemon_id = pidgeyID, type_id = flyingTypeID)

    pokemonDao.insertPokemon(pidgey.pokemon)

    speciesDao.insertSpecies(pidgey.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(pidgeySpeciesJoin)

    pokemonTypeDao.insertPokemonType(pokemonType = pidgey.types[0])
    pokemonTypeJoinDao.insertPokemonTypeJoin(flyingJoin)
}

private fun insertMoltres() = runBlocking {

    val moltres = moltres()
    val moltresSpeciesJoin =
        PokemonSpeciesJoin(pokemon_id = moltresID, species_id = moltresSpeciesID)

    val fireJoin = PokemonTypesJoin(pokemon_id = moltresID, type_id = fireTypeID)
    val flyingJoin = PokemonTypesJoin(pokemon_id = moltresID, type_id = flyingTypeID)

    pokemonDao.insertPokemon(moltres.pokemon)

    speciesDao.insertSpecies(moltres.species!!)
    speciesJoinDao.insertPokemonSpeciesJoin(moltresSpeciesJoin)

    pokemonTypeDao.insertPokemonType(pokemonType = moltres.types[0])
    pokemonTypeDao.insertPokemonType(pokemonType = moltres.types[1])
    pokemonTypeJoinDao.insertPokemonTypeJoin(fireJoin)
    pokemonTypeJoinDao.insertPokemonTypeJoin(flyingJoin)
}

fun bulbasaur(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = bulbasaurID, name = "bulbasaur"),
    species = PokemonSpecies(
        id = bulbasaurSpeciesID,
        species = "Seed pokemon",
        pokedexEntry = "There is a plant seed on its back right from the day this Pokémon is born. The seed slowly grows larger."
    ),
    types = listOf(
        PokemonType(id = poisonTypeID, name = "poison", slot = 1),
        PokemonType(id = grassTypeID, name = "grass", slot = 2)
    )
)

fun squirtle(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = squirtleID, name = "squirtle"),
    species = PokemonSpecies(
        id = squirtleSpeciesID,
        species = "Turtle pokemon",
        pokedexEntry = "Small shell pokemon"
    ),
    types = listOf(PokemonType(id = waterTypeID, name = "water", slot = 1))
)

fun charmander(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = charmanderID, name = "charmander"),
    species = PokemonSpecies(
        id = charmanderSpeciesID,
        species = "Fire lizard pokemon",
        pokedexEntry = "If the flame on this pokemon's tail goes out it will die"
    ),
    types = listOf(PokemonType(id = fireTypeID, name = "fire", slot = 1))
)

fun charizard(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = charizardID, name = "charizard"),
    species = PokemonSpecies(
        id = charizardSpeciesID,
        species = "Fire flying lizard pokemon",
        pokedexEntry = "Spits fire that is hot enough to melt boulders. Known to cause forest fires unintentionally"
    ),
    types = listOf(
        PokemonType(id = fireTypeID, name = "fire", slot = 1),
        PokemonType(id = flyingTypeID, name = "flying", slot = 2)
    )
)

fun moltres(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = moltresID, name = "moltres"),
    species = PokemonSpecies(
        id = moltresSpeciesID,
        species = "Fire bird pokemon",
        pokedexEntry = "Known as the legendary bird of fire. Every flap of its wings creates a dazzling flash of flames"
    ),
    types = listOf(
        PokemonType(id = fireTypeID, name = "fire", slot = 1),
        PokemonType(id = flyingTypeID, name = "flying", slot = 2)
    )
)

fun pidgey(): PokemonWithTypesAndSpecies = PokemonWithTypesAndSpecies(
    pokemon = Pokemon(id = pidgeyID, name = "pidgey"),
    species = PokemonSpecies(
        id = pidgeySpeciesID,
        species = "Bird pokemon",
        pokedexEntry = "Pidgey is a Flying Pokémon. Among all the Flying Pokémon, it is the gentlest and easiest to capture. A perfect target for the beginning Pokémon Trainer to test his Pokémon's skills."
    ),
    types = listOf(PokemonType(id = flyingTypeID, name = "flying", slot = 1))
)

Dan pertanyaannya adalah

@Query("SELECT * FROM Pokemon INNER JOIN PokemonType, PokemonTypesJoin ON Pokemon.pokemon_id = PokemonTypesJoin.pokemon_id AND PokemonType.type_id = PokemonTypesJoin.type_id WHERE pokemon_name LIKE :search AND type_name IN (:filters) ORDER BY pokemon_id ASC")
fun searchAndFilterPokemon(search: String, filters: List<String>): LiveData<List<PokemonWithTypesAndSpecies>>

Saya kira ini tidak berhasil karena pada titik ini Room belum mengumpulkan tipe dari tabel lain dan mungkin bahkan tidak menanyakan daftar, saya pikir bagian ini

type_name IN (:filters)

Sedang memeriksa kolom melawan daftar ketika yang saya inginkan adalah Daftar melawan daftar ️ tapi jujur ​​​​saya senang hanya mengatakan saya jatuh dan tidak bisa bangun ada yang bisa membantu? bantuan apa pun dihargai

-1
martinseal1987 22 Desember 2020, 00:36

3 jawaban

Jawaban Terbaik

Mungkin saya bisa menyalahgunakan beberapa nama kolom, tetapi coba kueri ini:

@Query("SELECT pok.id, pok.name FROM Pokemon AS pok
INNER JOIN PokemonTypesJoin AS p_join ON pok.id = p_join.pokemon_id
INNER JOIN PokemonType AS pok_type ON pok_type.id = p_join.type_id
WHERE pok.name LIKE :search AND pok_type.name IN (:filters) 
GROUP BY pok.id, pok.name ORDER BY count(*) DESC, pok.id ASC")
1
sergiy tikhonov 26 Desember 2020, 20:49

Sudahkah Anda membandingkan Kamar dengan Cmobilecom-JPA untuk android? JPA sangat baik dalam hubungan kueri. Keuntungan menggunakan JPA (standar) jelas, membuat kode Anda dapat digunakan kembali di android, java sisi server, atau proyek ayunan.

0
Sunnyday 24 Desember 2020, 00:00

Terima kasih kepada @serglytikhonov, kueri saya berfungsi dan sekarang terlihat seperti ini

@Query("""SELECT * FROM Pokemon 
                 INNER JOIN PokemonTypesJoin 
                 ON Pokemon.pokemon_id = PokemonTypesJoin.pokemon_id 
                 INNER JOIN PokemonType 
                 ON PokemonType.type_id = PokemonTypesJoin.type_id 
                 WHERE pokemon_name LIKE :search AND type_name IN (:filters)
                 GROUP BY Pokemon.pokemon_id, Pokemon.pokemon_name
                 ORDER BY count(*) DESC, pokemon_id ASC""")
fun searchAndFilterPokemon(search: String, filters: List<String>): LiveData<List<PokemonWithTypesAndSpecies>>

Bagian utama adalah hitungan ini (*) dan grup dengan banyak terima kasih

0
martinseal1987 27 Desember 2020, 05:13