使用jpa order by 错误的表名 Unknown column ‘XX.id‘ in ‘order clause Spring Data sorting on wrong Table

项目场景：

使用jpa在做复杂的关联查询的时候，使用sort排序，但orderby的字段，不是我想要的那张表

---

问题描述：

我原来的代码是这样的：

Sort sort = new Sort(Sort.Direction.DESC, "id");
Pageable pageable = new OffsetBasedPageRequest(offset, limit,sort);// 这个OffsetBasedPageRequest是jpa实现使用偏移量来查询数据的方法。
scanResultDatas = scanResultDataRespository.findAllByScanResultIdGroupBy(scanResultId,scanJobId,pageable);


// 这是我respository 代码


@Query(value = "select *,IF((select count(1) as num from whitelist_data a where a.scan_job_id = ?2  AND (a.index_colname = b.index_colname AND a.database_path = b.database_path))>0,1,0) as include from scan_result_data b where b.scan_result_id = ?1 GROUP BY database_path,index_colname \n#pageable\n",
            countQuery = "select count(*),IF((select count(1) as num from whitelist_data a where a.scan_job_id = ?2  AND (a.index_colname = b.index_colname AND a.database_path = b.database_path))>0,1,0) as include  from scan_result_data b where b.scan_result_id = ?1 GROUP BY database_path,index_colname",
            nativeQuery = true)
Page<ScanResultDataEntity> findAllByScanResultIdGroupBy(long scanResultId,long scanJobId, Pageable pageable);


报错如下：Unknown column 'a.id' in 'order clause'
// 

我想要的效果：

........... order by b.id

实际上jpa拼装出来的是：

........... ORDER BY a.id

---

原因分析：

说实话，我没有找到原因是什么，同样的，我好像很少有人和我有这样一样的问题。和我遇到一样问题的人：https://stackoverflow.com/questions/30387371/spring-data-sorting-on-wrong-table?answertab=oldest#tab-top

---

解决方案：

将pageable的sort去掉，自己在sql里实现orderby的功能，
修改后：

//Sort sort = new Sort(Sort.Direction.DESC, "id");
// 不用jpa的sort的方法了
Pageable pageable = new OffsetBasedPageRequest(offset, limit); 

@Query(value = "select *,IF((select count(1) as num from whitelist_data a where a.scan_job_id = ?2  AND (a.index_colname = b.index_colname AND a.database_path = b.database_path))>0,1,0) as include from scan_result_data b where b.scan_result_id = ?1 GROUP BY database_path,index_colname order by id desc \n#pageable\n",
            countQuery = "select count(*),IF((select count(1) as num from whitelist_data a where a.scan_job_id = ?2  AND (a.index_colname = b.index_colname AND a.database_path = b.database_path))>0,1,0) as include  from scan_result_data b where b.scan_result_id = ?1 GROUP BY database_path,index_colname",
            nativeQuery = true)
Page<ScanResultDataEntity> findAllByScanResultIdGroupBy(long scanResultId,long scanJobId, Pageable pageable);