Android ROOM中使用关联表查询单表数据的正确方案

1. 修正关联表实体类定义 #

首先确保AppointmentJoinRef实体类正确映射关联表，同时配置外键级联规则以清理关联记录：

@Entity(
    tableName = "AppointmentJoinRef",
    foreignKeys = [
        ForeignKey(
            entity = AppointmentModel::class,
            parentColumns = ["ID"],
            childColumns = ["ID"],
            onDelete = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = AppointmentModel::class,
            parentColumns = ["ID"],
            childColumns = ["LINKED_ID"],
            onDelete = ForeignKey.CASCADE
        )
    ],
    primaryKeys = ["ID", "LINKED_ID"] // 复合主键避免重复双向关联记录
)
data class AppointmentJoinRef(
    @ColumnInfo(name = "ID") val appointmentId: Int,
    @ColumnInfo(name = "LINKED_ID") val linkedAppointmentId: Int
)

这里用复合主键可以避免存储重复的双向关联（比如同时存(1,2)和(2,1)），外键的onDelete = CASCADE会在删除预约时自动清理关联表中对应的记录。

2. 修正POJO的@Relation配置 #

原POJO的@Relation参数错误，导致查询到自身而非关联预约。正确配置需让Room通过关联表匹配当前预约与关联预约：

data class AppointmentWithLinked(
    @Embedded val appointment: AppointmentModel,
    @Relation(
        entity = AppointmentModel::class,
        parentColumn = "ID",
        entityColumn = "ID",
        associateBy = Junction(
            value = AppointmentJoinRef::class,
            parentColumn = "ID", // 关联表中对应父预约ID的字段
            entityColumn = "LINKED_ID" // 关联表中对应关联预约ID的字段
        )
    )
    val linkedAppointments: List<AppointmentModel>
)

通过指定Junction的两个列映射，Room就能正确查询到当前预约关联的其他预约，不会再返回自身重复数据。

3. 实现双向级联删除（删除预约时同步删除关联预约） #

Room的外键级联仅能清理关联表记录，无法直接删除关联的预约实体。要实现删除1时自动删除2，可通过两种方式实现：

方式一：DAO层手动处理 #

在DAO中添加事务逻辑，先查询关联预约ID，再批量删除：

@Dao
interface AppointmentDao {
    @Transaction
    suspend fun deleteAppointmentAndLinked(appointmentId: Int) {
        // 查询当前预约的所有关联ID
        val linkedIds = getLinkedAppointmentIds(appointmentId)
        // 批量删除当前预约和关联预约
        deleteAppointments(listOf(appointmentId) + linkedIds)
    }

    // 若关联表仅存单向记录，用此查询获取双向关联ID
    @Query("SELECT CASE WHEN ID = :appointmentId THEN LINKED_ID ELSE ID END FROM AppointmentJoinRef WHERE ID = :appointmentId OR LINKED_ID = :appointmentId")
    suspend fun getLinkedAppointmentIds(appointmentId: Int): List<Int>

    @Query("DELETE FROM APPOINTMENTS WHERE ID IN (:ids)")
    suspend fun deleteAppointments(ids: List<Int>)
}

方式二：数据库触发器自动处理 #

通过Room的Callback添加数据库触发器，实现完全自动的级联删除：

val db = Room.databaseBuilder(context, AppDatabase::class.java, "app_db")
    .addCallback(object : RoomDatabase.Callback() {
        override fun onCreate(db: SupportSQLiteDatabase) {
            super.onCreate(db)
            db.execSQL("""
                CREATE TRIGGER delete_linked_appointment AFTER DELETE ON APPOINTMENTS
                FOR EACH ROW
                BEGIN
                    DELETE FROM APPOINTMENTS WHERE ID IN (SELECT LINKED_ID FROM AppointmentJoinRef WHERE ID = OLD.ID);
                    DELETE FROM AppointmentJoinRef WHERE ID = OLD.ID OR LINKED_ID = OLD.ID;
                END;
            """)
        }
    })
    .build()

触发器会在删除任意预约后，自动删除其所有关联预约及对应的关联表记录。

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