如何为Junction表添加额外信息？Worker-Job申请状态存储方案

我有一个关联Worker与Job的数据库，每个Job包含多个Poster，且每个Poster仅能分配给一个人。
业务流程：Worker需先申请Job，获得雇佣后才能选择要处理的Poster。
我希望在Job与Worker的关联表中新增字段标记申请/批准状态，而非使用两个独立关联表（避免列表管理混乱）。

Job实体 #

@Entity(
    foreignKeys = [
        ForeignKey(
            entity = Owner::class,
            parentColumns = ["owner_id"],
            childColumns = ["owner_id_map"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class Job (
    @PrimaryKey val job_id: Long,
    val status: Int,
    @ColumnInfo(index = true)
    val owner_id_map: Long,
    val title: String,
    val city: String
) : Serializable

JobPW实体（关联Worker和Poster的Job组合类） #

data class JobPW(
    @Embedded val job: Job,
    @Relation(
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(JobWorkerCrossRef::class)
    )
    val workers: List<Worker>,

    @Relation(
        entity = Poster::class,
        parentColumn = "job_id",
        entityColumn = "job_id_map"
    )
    val posters: List<PosterWithWorker>
) : Serializable

Worker实体 #

@Entity
class Worker(
    @PrimaryKey
    @ColumnInfo(name = "worker_id")
    val worker_id: Long,
    val workerName: String,
) : Serializable

JobWorkerCrossRef实体（原始交叉表） #

@Entity(
    primaryKeys = ["job_id","worker_id"], /* 复合主键 */
    foreignKeys = [
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class JobWorkerCrossRef(
    val job_id: Long,
    @ColumnInfo(index = true)
    val worker_id: Long,
)

原始查询语句 #

@Transaction
@Query("SELECT * FROM job")
fun getJobsFlow(): Flow<List<JobPW>>

@Transaction
@Query("SELECT * FROM job")
suspend fun getJobs(): List<JobPW>

核心是在交叉表JobWorkerCrossRef中新增状态字段，同时调整关联查询逻辑以获取状态信息。

1. 更新JobWorkerCrossRef实体 #

新增applicationStatus字段标记申请状态（可自定义状态值，比如0=申请中，1=已批准）：

@Entity(
    primaryKeys = ["job_id","worker_id"], /* 复合主键 */
    foreignKeys = [
        ForeignKey(
            entity = Job::class,
            parentColumns = ["job_id"],
            childColumns = ["job_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        ),
        ForeignKey(
            entity = Worker::class,
            parentColumns = ["worker_id"],
            childColumns = ["worker_id"],
            onDelete = ForeignKey.CASCADE,
            onUpdate = ForeignKey.CASCADE
        )
    ]
)
data class JobWorkerCrossRef(
    val job_id: Long,
    @ColumnInfo(index = true)
    val worker_id: Long,
    // 默认值设为申请中，可根据业务调整
    val applicationStatus: Int = 0
)

2. 创建Worker与状态的组合类 #

用来同时承载Worker信息和对应的申请状态：

data class WorkerWithApplicationStatus(
    @Embedded val worker: Worker,
    @ColumnInfo(name = "applicationStatus")
    val applicationStatus: Int
)

3. 修改JobPW实体 #

将原有的workers列表替换为workersWithStatus，并通过@Relation的projection参数指定要获取交叉表中的状态字段：

data class JobPW(
    @Embedded val job: Job,
    @Relation(
        parentColumn = "job_id",
        entityColumn = "worker_id",
        associateBy = Junction(
            value = JobWorkerCrossRef::class,
            parentColumn = "job_id",
            entityColumn = "worker_id"
        ),
        // 从交叉表中获取状态字段
        projection = ["applicationStatus"]
    )
    val workersWithStatus: List<WorkerWithApplicationStatus>,

    @Relation(
        entity = Poster::class,
        parentColumn = "job_id",
        entityColumn = "job_id_map"
    )
    val posters: List<PosterWithWorker>
) : Serializable

4. 调整查询语句（可选） #

如果需要针对状态做过滤，可新增DAO方法，原有查询方法可直接复用（会自动包含状态信息）：

@Transaction
@Query("SELECT * FROM job")
fun getJobsFlow(): Flow<List<JobPW>>

@Transaction
@Query("SELECT * FROM job")
suspend fun getJobs(): List<JobPW>

// 示例：获取指定Job下已批准的Worker
@Transaction
@Query("SELECT * FROM job WHERE job_id = :jobId")
suspend fun getJobWithApprovedWorkers(jobId: Long): JobPW?

// 示例：获取指定Worker的所有申请记录（含状态）
@Transaction
@Query("SELECT * FROM job JOIN jobworkercrossref ON job.job_id = jobworkercrossref.job_id WHERE jobworkercrossref.worker_id = :workerId")
fun getWorkerApplicationsFlow(workerId: Long): Flow<List<JobPW>>

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