How to write "JPQL" query with "IN" between SELECTs?

Question

I wrote this JPQL query and expect a result as List<Question>:

@Query("SELECT q FROM Question q WHERE q.id IN (SELECT qc.questions FROM QCard qc WHERE qc.id IN (SELECT ct.qCards FROM CTest ct WHERE ct.id=:id))")

These are my classes:

class CTest {
  id, List<QCard>
}

class QCard{
  id, List<Question>
}

class Question{
  id
}

I expected all questions as return for given CTest.id. But I got a compiler-error with message:

SQLSyntaxErrorException

I tried use ":" before "select" words but it had not helped.

What is wrong?

Answer 1

Test your SQL query in an SQL-client (e.g. Squirrel, DbVisualizer, etc.).

Assuming your foreign keys are named:

• question_id in table QCard
• card_id in table CTest

you could have an SQL with subselects like:

SELECT q.id
FROM Question q 
WHERE q.id IN (
  SELECT qc.question_id
  FROM QCard qc
  WHERE qc.id IN (
    SELECT ct.card_id
    FROM CTest ct
    WHERE ct.id = 1  -- example test id
  )
)

Does it return the expected result or are there syntax errors?

Then subsequently replace your subselects by JOINs.

1. Question and Cards

SELECT q.id, c.id
FROM Question q 
JOIN QCard c ON c.question_id = q.id

2. Cards and Tests

SELECT c.id, t.id
FROM QCard c 
JOIN CTest t ON t.card_id = c.id

3. All together

SELECT q.id, c.id, t.id
FROM Question q 
JOIN QCard c ON c.question_id = q.id
JOIN CTest t ON t.card_id = c.id

Note: add WHERE clauses like WHERE t.id = 1 if needed.

Experiment with the FROM/JOIN order as it makes sense.

Then translate the running SQL query to JPQL. For example:

@Query("SELECT q"
+ " FROM CTest test" 
// a test has many cards (1:n)
+ " JOIN QCard card ON card.id = test.card_id"  // associated cards
// a card has many questions (1:n)
+ " JOIN Question q ON q.id = card.question_id"  // associated questions
+ " WHERE test.id = :id")
public List<Question> findQuestionsByTestId(String id);