Spring Boot+JPA筛选所有关联需求答案全为true的Task

需求说明 #

开发Spring Boot Web应用，通过JPA检索Task数据。Task与Requirement为多对多关联，用户会创建与婚礼关联的RequirementAnswer，需筛选出**所有关联Requirement对应的answer_value均为'true'**的Task。

数据库表结构 #

表间关联关系：

• Task与Requirement为多对多关联
• Requirement与RequirementAnswer为一对多关联
• RequirementAnswer关联对应的Wedding

当前查询现状 #

现有查询尝试关联Task、Requirement、RequirementAnswer和Wedding，但未实现验证同一Task对应的所有RequirementAnswer均为true的逻辑。

实现方案：JPQL查询+代码过滤 #

1. JPQL查询代码 #

@Query("""
    select t, ra 
    from 
      Task t, 
      RequirementAnswer ra, 
      Requirement r, 
      Wedding w 
    where 
      ra.requirement = r and 
      w.id = :weddingId and 
      t member of r.tasks
""")
fun findByWedding(weddingId: Long): List<Tuple>?

2. 过滤逻辑代码 #

fun getTasksByWedding(wedding: Wedding?): List<Task> {
    val tasks: MutableMap<Task,String> = mutableMapOf()
    wedding?.id?.let { taskRepository.findByWedding(it) } ?.map {
        val task = it.get(0) as Task
        val requirementAnswer = it.get(1) as RequirementAnswer
        tasks[task]?.let { taskAnswer ->
            if(taskAnswer != requirementAnswer.answerValue){
                tasks.remove(task)
            }
        }?: let {
            if(requirementAnswer.answerValue == "true"){
                tasks[task] = requirementAnswer.answerValue
            }
        }
    } ?: throw ResponseStatusException(HttpStatus.BAD_REQUEST, "Wedding doesn't exist")
    return tasks.map { it.key }
}

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