HQL join query to join to a single row of many to

Question

This HQL query has been driving me up a wall, and I hope someone can help me out. Here is my data 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;
}

public class RecordTypeField {
    private int id;
    private String dataType;
}

Here is some data:

Record
-------------------------------------------------
| id      | name                                |
-------------------------------------------------
| 1       | Abc                                 |
| 2       | 123                                 |
| 3       | Xyz                                 |
-------------------------------------------------

RecordFieldData
-------------------------------------------------
| id      | record_id  | data         | type_id |
-------------------------------------------------
| 1       | 1          | Blue         | 1       |
| 2       | 1          | Round        | 2       |
| 3       | 2          | Red          | 1       |
| 4       | 2          | Square       | 2       |
| 5       | 3          | White        | 1       |
| 6       | 3          | Oval         | 2       |
-------------------------------------------------

RecordTypeField
-------------------------------------------------
| id      | dataType                            |
-------------------------------------------------
| 1       | Color                               |
| 2       | Shape                               |
-------------------------------------------------

What I need is a list of Records that are sorted by RecordField.data of a certain type. For example, sort the Records on RecordField.data but only for RecordFieldData of type 'color'. RecordFieldData.data 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 I am getting duplicate records because it is joining RecordFieldData rows that I do not want:

SELECT r FROM Record r 
LEFT JOIN r.recordFieldData AS field 
LEFT JOIN field.type AS typeField WITH typeField.dataType = 'color' 
ORDER BY LOWER(field.data)

Any suggestions?

Answer 1

EDIT

Just saw your requirement of needing to return all records. So replacing LEFT JOIN with JOIN as I initially suggested won't work.

Try using DISTINCT instead

SELECT DISTINCT r FROM Record r 
LEFT JOIN r.recordFieldData AS field 
LEFT JOIN field.type AS typeField WITH typeField.dataType = 'color' 
ORDER BY LOWER(field.data)

EDIT 2

I think LEFT JOIN FETCH needs to be used, though I'm not sure why it gave you an error the last time. Maybe something like this

SELECT DISTINCT r FROM Record r 
LEFT JOIN FETCH r.recordFieldData AS field 
LEFT JOIN FETCH field.type AS typeField WITH typeField.dataType = 'color' 
ORDER BY LOWER(field.data)