Android Room: cross-referencing more than once or with a count

Issue

In my app, I've got the concept of a NutritionDate and FoodItems.

@Entity
data class NutritionDateEntity(
    @PrimaryKey val epochDay: Long,
    val year: Int,
    val month: Int,
)

@Entity
data class FoodItemEntity(
    @PrimaryKey
    val createdAt: Long = Instant.now().toEpochMilli(), // guaranteed to be unique, ascending
    val forEpochDay: Long = 0L,
    val name: String = "",
    val fact: String = ""
)

I've also got a cross-reference table to track which FoodItems are included in which NutritionDates:

@Entity(primaryKeys = ["epochDay", "foodItemCreatedAt"])
data class NutritionDateAndFoodItemCrossRefEntity(
    val epochDay: Long,
    val foodItemCreatedAt: Long,
)

I came across the issue that I can't include a food item in the list multiple times (say I ate two burritos and wanted to add it twice).

I thought about adding a count property to the cross reference table, or trying to make some kind of lookup table for cross-reference + count, but the primary key for the cross-reference entries are composite. Maybe trying to add an auto-incrementing primary key to the cross reference entries to make them unique? Couldn't really find any good info on doing that either.

I'd like to do this in an idiomatic way if possible. Is there a good / standard way to do this with Room?

Solution

After trying a lot of different strategies, I ended up getting it to work well with something relatively simple that I should have tried in the first place:

@Entity
data class NutritionDateEntity(
    @PrimaryKey 
    val epochDay: Long,
    val year: Int,
    val month: Int,
) 

@Entity
data class FoodItemEntity(
    @PrimaryKey
    val createdAt: Long = Instant.now().toEpochMilli(), // guaranteed to be unique, ascending
    val forEpochDay: Long = 0L,
    val name: String = "",
    val fact: String = ""
)

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = NutritionDateEntity::class,
            parentColumns = arrayOf("epochDay"),
            childColumns = arrayOf("epochDay"),
            onDelete = CASCADE,
            onUpdate = CASCADE
        ),
        ForeignKey(
            entity = FoodItemEntity::class,
            parentColumns = arrayOf("createdAt"),
            childColumns = arrayOf("createdAt"),
            onDelete = CASCADE,
            onUpdate = CASCADE
        )
    ]
)
data class FoodEntryEntity( // formerly CrossRef
    @PrimaryKey(autoGenerate = true)
    val id: Int = 0,
    val epochDay: Long,
    val createdAt: Long,
)

Instead of using a composite primary key for the cross reference table, I decided denote it as a "food entry" and give it an auto-incrementing @PrimaryKey. I can still use it in a @Relation annotation to create the following when I query NutritionDates:

data class NutritionDateWithFoodItems(
    @Embedded
    val nutritionDate: NutritionDateEntity,

    @Relation(
        parentColumn = "epochDay",
        entityColumn = "createdAt",
        associateBy = Junction(FoodEntryEntity::class)
    )
    val foodItems: List<FoodItemEntity>
)

Also necessary was an upsert implementation to prevent @ForeignKey delete cascades when calling an @Insert(onConflictStrategy = REPLACE) method (note the REPLACE is now IGNORE):

@Dao
interface FoodItemEntityDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    suspend fun insert(foodItemEntity: FoodItemEntity): Long

    @Update(onConflict = OnConflictStrategy.IGNORE)
    suspend fun update(foodItemEntity: FoodItemEntity)

    @Transaction
    suspend fun upsert(foodItemEntity: FoodItemEntity) {
        val result = insert(foodItemEntity)
        if (result == -1L) update(foodItemEntity)
    }
}

Answered By - Carter Hudson