Search code examples
sqlpostgresqljpajpql

Select one row with same id and match value in another column from another table

I'm currently working on a Spring Boot Webapp where I want to retreive tasks with JPA. A Task can have multiple requirements and my customer creates requirement_answers which are connected to his wedding. I now want to select all tasks where all the requirement.answer_value are answered with 'true'.

My relevant Database Schema is:

enter image description here

My current query is this:

enter image description here

I now want to check that the task with the same uuid has all requirement_answer with true? How can I achieve this?

Greetings

EDIT: My Solution, filtered in Code instead of jpql as I could not get it working

@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>?
}

Here is the filtering:

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 }
    }
Solution

  • With SQL you can do use subselects to compare the counts:

    select t.*
from task t
         join task_requirement tr on t.uuid = tr.task_id
         join requirement r on tr.requirement_id = r.id
         join requirement_answer ra1 on r.id = ra1.requirement_id
         join wedding_requirement_answer wra1 on ra1.id = wra1.requirement_answer_id
where wra1.wedding_id = 1
  and ( (select ra2.requirement_id
         from requirement_answer ra2
                  join wedding_requirement_answer wra2 on ra2.id = wra2.requirement_answer_id
         where wra2.wedding_id = wra1.wedding_id
           and ra2.requirement_id = ra1.requirement_id))
    =
      (select ra3.requirement_id
       from requirement_answer ra3
                join wedding_requirement_answer wra3 on ra3.id = wra3.requirement_answer_id
       where wra3.wedding_id = wra1.wedding_id
         and ra3.requirement_id = ra1.requirement_id
         and ra3.answer_value = 'true');