This HQL query has been driving me up a wall, and I hope someone can help me out. Here is my data model:
Code:
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 is some data:
Code:
Record
-------------------------------------------------
| id | name |
-------------------------------------------------
| 1 | Round Cookie |
| 2 | Square Cookie |
| 3 | Oval Cookie |
-------------------------------------------------
RecordFieldData
--------------------------------------------------------
| id | record_id | data | 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 I need is a list of Records that are sorted by the Creator's username. So the RecordFieldData.relatedUser.username, but only for type Creator. The username does not have to be returned in the query, I can get that later, but I need the sort to happen in the query that retrieves the records (otherwise pagination won't work). Keep in mind RecordFieldData of a certain type can be missing for a Record but I still want the record in the list.
I tried this query but it doesn't seem to be sorting properly:
Code:
SELECT DISTINCT r FROM Record r
LEFT JOIN r.recordFieldData AS field
LEFT JOIN field.relatedUser AS relatedUser
LEFT JOIN field.type as typeField WITH typeField.dataType = 'Creator'
ORDER BY LOWER(relatedUser.username)
Any suggestions? I really need a subquery in my JOIN statement but HQL doesn't support that.
Login
FAQ
Search