android - 如何创建一个从联结本身返回值的联结查询？

Question

我正在尝试用他的统计数据查询角色，问题是联结表包含统计数据的值。比如角色是Fred，stat是agility，值为10。意思是Fred的stat是agility，他的值为10。是否可以用@Relation和Junction写一个数据类来查询为此？

我看不出有什么办法可以做到这一点。

data class CharacterWithStatsEntity(
    @Embedded val character: CharacterEntity,
    @Relation(
        parentColumn = "id",
        entityColumn = "id",
        entity = StatsEntity::class,
        associateBy = Junction(
            value = CharactersStatsEntity::class,
            parentColumn = "characterId",
            entityColumn = "statsId"
        )
    ) val stats: List<StatsEntity>
)

我提供的代码没有从连接处返回值。 StatsEntity 仅包含统计名称，我需要一个新的实体 StatWithValue，它将结合 StatEntity 和 CharactersStatsEntity，并且它将包含特定角色的统计名称和值。

Answer 1

如果不包括@Dao、@Entities 和调用代码，则一点都不清楚。

但是，以下粗略地基于可用信息是检索值的示例。

• 注意使实体中的列明确/使用更容易区分的唯一名称。

首先是定义 3 个表的 3 个核心实体，即:-

• CharacterEntity 表，
• StatsEntity 表和
• CharactersStats 表(连接表)。

:-

@Entity
class CharacterEntity (

    @PrimaryKey
    var characterId: Long?,
    var name: String
)

@Entity
data class StatsEntity (

    @PrimaryKey
    var statsId: Long?,
    var statsName: String
)

@Entity(primaryKeys = ["characterIdReference","statsIdReference"])
data class CharacterStatsEntity (
    val characterIdReference: Long,
    val statsIdReference: Long
    )

CharacterWithStatsEntity 类

data class CharacterWithStatsEntity(
    @Embedded val character: CharacterEntity,
    @Relation(
        parentColumn = "characterId",
        entityColumn = "statsId",
        entity = StatsEntity::class,
        associateBy = Junction(
            value = CharacterStatsEntity::class,
            parentColumn = "characterIdReference", 
            entityColumn = "statsIdReference"
        )
    ) val stats: List<StatsEntity>
)

Dao 允许添加角色、统计数据和连接点，并允许通过连接点提取:-

@Dao
interface CharacterStatsDao {
    @Insert
    fun insertCharacter(characterEntity: CharacterEntity) :Long

    @Insert
    fun insertStats(statsEntity: StatsEntity) :Long

    @Insert
    fun insertCharacterStatsJunction(characterStatsEntity: CharacterStatsEntity) :Long

    @Query("SELECT * FROM characterentity")
    fun getAllCharactersWithStats() : List<CharacterWithStatsEntity>
}

最后是添加 2 个角色和 4 个统计数据的调用代码，并与具有 Stat1 和 Stat4 的角色 Fred 以及具有 Stat2 和 Stat4 的角色 Mary 连接，然后最终通过 CharactersWithStatsEntity 类报告角色和他们的统计数据:-

    val characterStatsDao = db.characterStatsDao()
    var char1 = characterStatsDao.insertCharacter(CharacterEntity(null,"Fred"))
    var char2 = characterStatsDao.insertCharacter(CharacterEntity(null,"Mary"))
    var stat1 = characterStatsDao.insertStats(StatsEntity(null,"STAT1"))
    var stat2 = characterStatsDao.insertStats(StatsEntity(null,"STAT2"))
    var stat3 = characterStatsDao.insertStats(StatsEntity(null, "STAT3"))
    var stat4 = characterStatsDao.insertStats(StatsEntity( null, "STAT4"))
    characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char1,stat1))
    characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char1,stat4))
    characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char2,stat2))
    characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char2,stat4))
    var charactersWithStatsList: List<CharacterWithStatsEntity> = characterStatsDao.getAllCharactersWithStats()

    for (cwsl: CharacterWithStatsEntity in charactersWithStatsList) {
        val currentChar  = cwsl.character
        for (se: StatsEntity in cwsl.stats) {
            Log.d("CWSLINFO","Character name is " + currentChar.name + " This stat is " + se.statsName)
        }
    }

运行时的结果是(即通过连接检索的 statsName 值):-

11-04 11:10:32.731 D/CWSLINFO: Character name is Fred This stat is STAT1
11-04 11:10:32.731 D/CWSLINFO: Character name is Fred This stat is STAT4
11-04 11:10:32.731 D/CWSLINFO: Character name is Mary This stat is STAT2
11-04 11:10:32.731 D/CWSLINFO: Character name is Mary This stat is STAT4

如果联结表除了表之间的关系之外还有其他值，那么我不认为可以直接检索其他值，因为联结旨在链接联结表。

但是，可以获得这样的值，例如考虑:-

1. CharacterStatsEntity 是

:-

@Entity(primaryKeys = ["characterIdReference","statsIdReference"])
data class CharacterStatsEntity (
    val characterIdReference: Long,
    val statsIdReference: Long,
    val otherValue: String //<<<<<<<<<< ADDED
)

1. 其他 Dao 的(2 个中的 1 个就足够了)

:-

@Query("SELECT * FROM characterstatsentity WHERE characterIdReference = :characterId AND statsIdReference = :statsId")
fun getOtherValueFromJunction(characterId: Long, statsId: Long) :CharacterStatsEntity

@Query("SELECT otherValue FROM characterstatsentity WHERE characterIdReference = :characterId AND statsIdReference = :statsId")
fun getAltOtherValueFromJunction(characterId: Long, statsId: Long) :String

1. 修改调用代码

   1. 使用
   添加额外数据

:-

characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char1,stat1,"othervalue A"))
characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char1,stat4,"othervalue B"))
characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char2,stat2, "othervalue C"))
characterStatsDao.insertCharacterStatsJunction(CharacterStatsEntity(char2,stat4,"othervalue D"))

1. 循环更改为检索其他值

:-

    for (cwsl: CharacterWithStatsEntity in charactersWithStatsList) {
        val currentChar  = cwsl.character
        for (se: StatsEntity in cwsl.stats) {
            Log.d("CWSLINFO",
                "Character name is " + currentChar.name +
                        " This stat is " + se.statsName +
                        " OV = " + characterStatsDao.getOtherValueFromJunction(currentChar.characterId!!,se.statsId!!).otherValue +
                        " ALTOV = " + characterStatsDao.getAltOtherValueFromJunction(cwsl.character.characterId!!,se.statsId!!)
            )
        }
    }

结果将是:

2019-11-13 10:30:22.298 D/CWSLINFO: Character name is Fred This stat is STAT1 OV = othervalue A ALTOV = othervalue A
2019-11-13 10:30:22.300 D/CWSLINFO: Character name is Fred This stat is STAT4 OV = othervalue B ALTOV = othervalue B
2019-11-13 10:30:22.302 D/CWSLINFO: Character name is Mary This stat is STAT2 OV = othervalue C ALTOV = othervalue C
2019-11-13 10:30:22.304 D/CWSLINFO: Character name is Mary This stat is STAT4 OV = othervalue D ALTOV = othervalue D