java - HQL double join query with one to many relationship -

java - HQL double join query with one to many relationship -

this hql query has been driving me wall, , hope can help me out. here info model:

public class record { private int id; private string name; private set<recordfielddata> recordfielddata; } public class recordfielddata { private int id; private string data; private record record; private recordtypefield type; private user relateduser; } public class recordtypefield { private int id; private string datatype; }

here data:

record ------------------------------------------------- | id | name | ------------------------------------------------- | 1 | round cookie | | 2 | square cookie | | 3 | oval cookie | ------------------------------------------------- recordfielddata -------------------------------------------------------- | id | record_id | info | type_id | user_id | -------------------------------------------------------- | 1 | 1 | | 1 | 1 | | 2 | 1 | round | 2 | | | 3 | 2 | | 1 | 2 | | 4 | 2 | square | 2 | | | 5 | 3 | | 1 | | | 6 | 3 | oval | 2 | | -------------------------------------------------------- recordtypefield ------------------------------------------------- | id | datatype | ------------------------------------------------- | 1 | creator | | 2 | shape | | 3 | owner | ------------------------------------------------- user ------------------------------------------------- | id | username | ------------------------------------------------- | 1 | milak | | 2 | jennifera | -------------------------------------------------

what need list of records sorted creator's username. recordfielddata.relateduser.username, type creator. username not have returned in query, can later, need sort happen in query retrieves records (otherwise pagination won't work). maintain in mind recordfielddata of type can missing record still want record in list.

i tried query doesn't seem sorting properly:

select distinct r record r left bring together r.recordfielddata field left bring together field.relateduser relateduser left bring together field.type typefield typefield.datatype = 'creator' order lower(relateduser.username)

any suggestions?

java sql hibernate hql