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

The bounty在3天后过期。这个问题的答案有资格获得+250声望奖励。
                            Decent Dabbler正在寻找一个规范的答案。
                        
                    
                
                            
                    
我正在尝试用他的统计信息查询字符，问题是联结表保存了统计信息的值。例如，字符为Fred，stat为敏捷，值为10。这表示Fred具有敏捷的stat，其值为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仅保存stats名称，我需要一个新实体StatWithValue，它将StatStatus和CharactersStatsEntity结合在一起，并且它将保存stat名称和特定字符的值。

最佳答案

如果不包括@ 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个统计数据，并与Character Fred具有Stat1和Stat4以及Character Mary具有Stat2和Stat4的联结，然后最终通过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

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

但是，可以获取这样的值，例如考虑：


CharacterStatsEntity为


：-

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

附加道（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

修改调用代码


使用添加额外的数据



：-

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"))

并且循环更改为检索其他值


：-

    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