多表查询需求：获取CommitteeDetails列表的实现咨询

实体类回顾 #

先明确你的三个Room实体类：

@Entity(foreignKeys = [ForeignKey(entity = SelfHelpGroup::class, parentColumns = ["shgId"], childColumns = ["shgId"], onDelete = CASCADE), ForeignKey(entity = Member::class, parentColumns = ["memberId"], childColumns = ["memberId"], onDelete = CASCADE)])
data class Committee(
    @PrimaryKey(autoGenerate = true)
    @SerializedName("committeeId")
    val committeeId: Int?= null,
    @SerializedName("shgId")
    val shgId: Int?,
    @SerializedName("memberId")
    val memberId: Int?,
    @SerializedName("date")
    val date: String?
)

@Entity(tableName = "Member", foreignKeys = [ForeignKey(entity = SelfHelpGroup::class, parentColumns = ["shgId"], childColumns = ["shgId"], onDelete = CASCADE), ForeignKey(entity = Role::class, parentColumns = ["roleId"], childColumns = ["roleId"], onDelete = CASCADE)])
data class Member(
    @PrimaryKey(autoGenerate = true)
    @SerializedName("memberId")
    val memberId: Int ?= null,
    @SerializedName("shgId")
    val shgId: Int,
    @SerializedName("name")
    val name: String,
    @SerializedName("address")
    val address: String,
    @SerializedName("phoneNumber")
    val phoneNumber: String,
    @SerializedName("emailId")
    val emailId: String,
    @SerializedName("roleId")
    val roleId: Int?,
    @SerializedName("password")
    val password: String?
)

@Entity(foreignKeys = [
    ForeignKey(entity = Committee::class, parentColumns = ["committeeId"], childColumns = ["committeeId"], onDelete = CASCADE),
    ForeignKey(entity = Member::class, parentColumns = ["memberId"], childColumns = ["memberId"], onDelete = CASCADE),
])
data class Attendance(
    @PrimaryKey(autoGenerate = true)
    @SerializedName("attendanceId")
    val attendanceId: Int?= null,
    @SerializedName("committeeId")
    val committeeId: Int,
    @SerializedName("memberId")
    val memberId: Int,
    @SerializedName("isPresent")
    var isPresent: Boolean = false,
    @SerializedName("leaveApplied")
    var leaveApplied: Boolean = false
)

需求说明 #

需要查询得到包含委员会创建者信息、委员会考勤列表的List<CommitteeDetails>，目标数据结构：

data class CommitteeDetails (
    val committeeId: Int,
    val member: Member,
    val attendances: List<Attendance>,
    val dateTime: String
)

实现步骤 #

1. 定义Room关系映射数据类 #

Room通过@Embedded和@Relation注解自动处理关联查询，先定义中间映射类适配Room规则：

data class CommitteeWithCreatorAndAttendances(
    @Embedded
    val committee: Committee,
    
    // 一对一关联：委员会创建者（Committee.memberId = Member.memberId）
    @Relation(
        parentColumn = "memberId",
        entityColumn = "memberId"
    )
    val creator: Member,
    
    // 一对多关联：委员会所有考勤记录（Committee.committeeId = Attendance.committeeId）
    @Relation(
        parentColumn = "committeeId",
        entityColumn = "committeeId"
    )
    val attendances: List<Attendance>
)

2. 编写Dao层查询方法 #

在Room Dao接口中添加查询方法，确保事务一致性：

@Dao
interface CommitteeDao {
    // 查询所有委员会及关联的创建者、考勤记录
    @Transaction
    @Query("SELECT * FROM Committee")
    suspend fun getAllCommitteesWithDetails(): List<CommitteeWithCreatorAndAttendances>
}

• @Transaction保证查询在单个事务中执行，避免数据不一致
• suspend适配协程异步操作，同步场景可移除

3. 转换为目标数据结构 #

将Room返回的映射结果转换为你需要的CommitteeDetails：

// 从Dao获取原始数据
val rawCommitteeData = committeeDao.getAllCommitteesWithDetails()

// 映射为目标结构
val committees: List<CommitteeDetails> = rawCommitteeData.map {
    CommitteeDetails(
        committeeId = it.committee.committeeId ?: throw IllegalArgumentException("委员会ID不能为空"),
        member = it.creator,
        attendances = it.attendances,
        dateTime = it.committee.date ?: ""
    )
}

补充说明 #

• 如需过滤特定条件（如某SHG下的委员会），修改Dao的@Query语句即可：

  @Transaction
  @Query("SELECT * FROM Committee WHERE shgId = :shgId")
  suspend fun getCommitteesByShgId(shgId: Int): List<CommitteeWithCreatorAndAttendances>
  

• 建议业务层保证创建委员会时memberId不为空，避免映射时抛出异常，可根据实际需求调整空值处理逻辑

内容的提问来源于stack exchange，提问作者Aswin .A.S