Android Room实现带重复有序训练关联Exercise列表问题求助

核心问题 #

你的实现存在三个关键问题，导致返回空列表或无法满足重复动作+有序排列的需求：

1. 中间表主键设计错误：将trainingIdRef、exerciseIdRef和trainingExerciseMappingId同时设为主键，导致同一训练下无法重复添加同一动作（主键冲突），违背了需求。
2. @Relation注解局限性：Room的@Relation默认对关联结果去重，且不会自动使用中间表的indexExercise字段排序，无法保留重复动作和预定顺序。
3. DAO查询逻辑无效：当前的ORDER BY是针对Training表的查询，而@Relation会单独执行关联查询，这个排序不会作用到Exercise列表上。

修正步骤 #

1. 调整中间表实体定义 #

修改TrainingExerciseMappingTable，仅保留trainingExerciseMappingId作为主键，允许同一训练-动作组合重复出现：

@Entity(
    primaryKeys = [ModelConstants.TRAINING_EXERCISE_MAPPING_ID],
    foreignKeys = [
        ForeignKey(
            entity = Training::class,
            parentColumns = [ModelConstants.TRAINING_ID],
            childColumns = [ModelConstants.TRAINING_ID_REF],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Exercise::class,
            parentColumns = [ModelConstants.EXERCISE_ID],
            childColumns = [ModelConstants.EXERCISE_ID_REF],
            onUpdate = ForeignKey.CASCADE,
            onDelete = ForeignKey.CASCADE
        )
    ],
    // 添加联合索引优化查询性能
    indices = [Index(value = [ModelConstants.TRAINING_ID_REF, ModelConstants.INDEX_EXERCISE])]
)
data class TrainingExerciseMappingTable(
    val trainingIdRef: String,
    val exerciseIdRef: String,
    val indexExercise: Int,
    @ColumnInfo(index = true, name = ModelConstants.TRAINING_EXERCISE_MAPPING_ID)
    val trainingExerciseMappingId: String = UUID.randomUUID().toString(),
)

2. 创建带排序的关联数据类 #

由于@Relation无法处理重复和自定义排序，创建包含中间表索引的复合数据类，直接映射关联结果：

data class OrderedExercise(
    @Embedded val mapping: TrainingExerciseMappingTable,
    @Relation(
        parentColumn = ModelConstants.EXERCISE_ID_REF,
        entityColumn = ModelConstants.EXERCISE_ID
    )
    val exercise: Exercise
)

data class TrainingWithOrderedExercises(
    @Embedded val training: Training,
    val exercises: List<OrderedExercise>
)

3. 修改DAO查询方法 #

直接查询训练对应的所有关联动作，按indexExercise排序并保留重复项：

@Dao
interface CustomTrainingDao {
    // 查询目标训练
    @Query("SELECT * FROM Training WHERE trainingId = :trainingIdSelected")
    suspend fun getTrainingById(trainingIdSelected: String): Training

    // 查询该训练下所有带索引的动作，按顺序排列
    @Transaction
    @Query("SELECT * FROM TrainingExerciseMappingTable WHERE trainingIdRef = :trainingIdSelected ORDER BY indexExercise ASC")
    suspend fun getOrderedExercisesForTraining(trainingIdSelected: String): List<OrderedExercise>

    // 组合成完整的训练计划
    suspend fun getTrainingProgram(trainingIdSelected: String): TrainingWithOrderedExercises {
        val training = getTrainingById(trainingIdSelected)
        val orderedExercises = getOrderedExercisesForTraining(trainingIdSelected)
        return TrainingWithOrderedExercises(training, orderedExercises)
    }

    // 直接返回排序后的Exercise列表（如果不需要Training信息）
    @Transaction
    @Query("SELECT e.* FROM Exercise e " +
            "INNER JOIN TrainingExerciseMappingTable stmt ON e.exerciseId = stmt.exerciseIdRef " +
            "WHERE stmt.trainingIdRef = :trainingIdSelected " +
            "ORDER BY stmt.indexExercise ASC")
    suspend fun getExerciseListForTraining(trainingIdSelected: String): List<Exercise>
}

4. 检查数据插入逻辑 #

确保插入数据的顺序和正确性：

• 先插入Training和Exercise实体
• 再插入TrainingExerciseMappingTable条目，每个条目对应训练中的一个动作位置，正确设置trainingIdRef、exerciseIdRef和indexExercise（比如从0开始递增）

额外优化建议 #

• 使用suspend修饰DAO方法，符合Room协程最佳实践
• 给TrainingExerciseMappingTable添加trainingIdRef和indexExercise的联合索引，提升查询性能
• 如果不需要中间表的额外信息，可直接通过自定义SQL查询返回排序后的Exercise列表，无需嵌套数据类

内容的提问来源于stack exchange，提问作者Thibaut_Esteve