HQL连接查询加入到单对多关系的行中 [英] HQL join query to join to a single row of many to one relationship

问题描述

这个HQL查询一直在把我拉上一堵墙，我希望有人能帮助我。这里是我的数据模型：

  public class Record {
 private int id; 
私人字符串名称; 
 private Set< RecordFieldData> recordFieldData; 
} 
 
 public class RecordFieldData {
 private int id; 
私有字符串数据; 
私人记录记录; 
私人RecordTypeField类型; 
} 
 
 public class RecordTypeField {
 private int id; 
 private String dataType; 
 
 $ / code> 

以下是一些数据：



 记录
 -------------------------------- ----------------- 
 | id |名称| 
 ---------------------------------------------- --- 
 | 1 | Abc | 
 | 2 | 123 | 
 | 3 | Xyz | 
 ---------------------------------------------- --- 
 
 RecordFieldData 
 ---------------------------------- --------------- 
 | id | record_id |数据| type_id | 
 ---------------------------------------------- --- 
 | 1 | 1 |蓝色| 1 | 
 | 2 | 1 | Round | 2 | 
 | 3 | 2 |红色| 1 | 
 | 4 | 2 | Square | 2 | 
 | 5 | 3 |白色| 1 | 
 | 6 | 3 |椭圆| 2 | 
 ---------------------------------------------- --- 
 
 RecordTypeField 
 ---------------------------------- --------------- 
 | id | dataType | 
 ---------------------------------------------- --- 
 | 1 |颜色| 
 | 2 |形状| 
 ---------------------------------------------- --- 
  

我需要的是按照RecordField.data排序的记录列表类型。例如，对RecordField.data中的记录进行排序，但仅对'color'类型的RecordFieldData进行排序。 RecordFieldData.data不必在查询中返回，我可以在稍后获取，但我需要在检索记录的查询中进行排序（否则分页将不起作用）。记住Record的某个类型的RecordFieldData可能会丢失，但我仍然希望列表中的记录。

我试过这个查询，但我得到重复记录，因为它加入了我不想要的RecordFieldData行：

  SELECT r FROM记录r 
 LEFT JOIN r.recordFieldData AS字段
 LEFT JOIN字段类型AS typeField WITH typeField.dataType ='color'
 ORDER BY LOWER（field.data）
  

有什么建议吗？

解决方案

编辑 您需要返回所有记录的要求。因此，我最初建议的 LEFT JOIN 替换为 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记录r 
 LEFT JOIN FETCH r.recordFieldData AS字段
 LEFT JOIN FETCH field.type AS typeField WITH typeField.dataType ='color'
 ORDER BY LOWER（field.data）
  

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?

解决方案

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)

这篇关于HQL连接查询加入到单对多关系的行中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！