How to create a junction query, that returns a value from the junction itself?

Question

I am trying to query a character with his stats, the problem is that the junction table holds the value of the stat. For example, the character is Fred, the stat is agility, and the value is 10. Meaning Fred has the stat of agility, and his value at it is 10. Is it possible to write a data class with @Relation and Junction to query for this?

I don't see a way to accomplish this.

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

The code that I am providing is not returning the value from the junction. StatsEntity only holds the stats name, I would need a new entity StatWithValue, that would combine StatEntity and CharactersStatsEntity, and it would hold the stat name and value for the specific character.

Answer 1

It's not at all clear without including the @Dao, @Entities and invoking code.

However, the following loosely based upon the available information is an example of retrieving values.

• Note to make the columns in the entitities unambiguous/more easily distinguishable unique names have been used.

First the 3 core entities that define the 3 tables namely:-

• the CharacterEntity table,
• the StatsEntity table and
• the CharactersStats table (the junction table).

:-

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

The CharacterWithStatsEntity class

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

The Dao's to allow Characters, Stats and junctions to be added and to alow extraction via the junction :-

@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>
}

Finally the invoking code that adds 2 characters and 4 stats and junctions with Character Fred having Stat1 and Stat4 and Character Mary having Stat2 and Stat4 and then finally reporting the characters and thier stats via the CharactersWithStatsEntity class :-

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

When run the result is (i.e. the statsName values being retrieved via the junction) :-

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

If the junction table has additional values other than the relationships between the tables then I don't believe that the other values can be retrieved directly as the junction is intended to link the junctioned tables.

However, such values can be obtained, for example consider :-

1. The CharacterStatsEntity as being

:-

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

2. Additional Dao's (1 of the 2 would suffice)

:-

@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

3. Alteration of the invoking code

   1. Add the extra data using

:-

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

2. And the loop changed to retrieve the other values

:-

    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!!)
            )
        }
    }

The the Result would be :

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