How to order by list Index in HQL query?

Question

This is situation I have:
I have two entities with one-to-many relationship mapped like that (only relevant parts are given):

    <class name="xyz.Survey">
        <list name="answers" inverse="true" lazy="true" fetch="select" 
cascade="all-delete-orphan">
            <key column="OPA_OP_ID" not-null="true" />
            <list-index column="OPA_SORT_ORDER" />
            <one-to-many class="xyz.Answer" />
        </list>
    </class>
    <class name="xyz.Answer">
        <many-to-one name="survey" class="xyz.Survey" fetch="select">
            <column name="OPA_OP_ID" not-null="true" />
        </many-to-one>
there is no mapping for column OPA_SORT_ORDER, I let Hibernate take care of that
    </class>

My question is: I want to perform HQL query on the answers for single survey and I want those answers sorted by OPA_SORT_ORDER. This query doesn't work like that:

select new AnswerDTO(a.id, a.answerText, 0) 
from Survey p 
join p.answers a
where p.id = ? 

In log I can see that there is no ORDER BY in SQL query generated by this HQL. How can I do this? Do I need to add sortOrder property to Answer entity?

Answer 1

No, you don't need to add a property, HQL has a built-in function index for this:

select new AnswerDTO(a.id, a.answerText, 0) 
from Survey p 
join p.answers a
where p.id = ? 
order by index(a)