How to make an optimal hibernate query with three objets ?

Question

I have a query but I dont know how to make it optimal. I have three objects.

• SCP
• Question : it has a field that points SCP (SCP_id)
• Answer : it has a field that points Question (question_id)

How can I make a query that counts the number of answer within a given SCP

Answer 1

What about this:

select count(a) 
from Answer a 
where a.question.scp.id = :id

Which generates the following SQL:

select
  count(answer0_.id) as col_0_0_ 
 from
  Answer answer0_,
  Question question1_ 
 where
  answer0_.question_id=question1_.id 
  and question1_.scp_id=?

Seems pretty efficient.