这个HQL查询已经把我逼上了墙,我希望有人能帮助我。下面是我的数据模型:
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;
}
下面是一些数据:
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 |
-------------------------------------------------
我需要的是由RecordField排序的记录列表。某种类型的数据。例如,对RecordField中的记录进行排序。数据,但只用于RecordFieldData类型的'color'。RecordFieldData。数据不一定要在查询中返回,我可以稍后得到,但是我需要在检索记录的查询中进行排序(否则分页将不起作用)。请记住,RecordFieldData的某种类型可能会丢失的记录,但我仍然希望在列表中的记录。
我尝试了这个查询,但我得到重复的记录,因为它正在加入我不想要的RecordFieldData行:
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)
有什么建议吗?
EDIT
刚刚看到您需要返回所有记录的要求。因此,我最初建议用JOIN
代替LEFT JOIN
是行不通的。
试试用DISTINCT
代替
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)
编辑2
我认为LEFT JOIN FETCH
需要使用,虽然我不确定为什么它给了你一个错误的最后一次。也许像这样
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)