如何将postgresSql查询重写为Spring-data jpa查询

问题描述

此刻，我在尝试将下一个查询作为spring-data JPA查询编写时陷入困境：

with recursive s as (
select *
from t
where file_id = '12345'
union all
select dfs.*
from t dfs
join s on s.file_id = dfs.parent_folder_id
)
select * from s;

我已经尝试下一个：

@Query(value = "with recursive subfiles as (
select * from t where file_id=?1 
union all 
dfs.* from t dfs join subfiles s 
on s.file_id = dfs.parent_folder_id) 
select file_id from subfiles", nativeQuery = true)

但是出现下一个错误：

Method threw 'org.springframework.dao.InvalidDataAccessResourceUsageException' exception.
could not extract ResultSet; SQL [n/a]
org.hibernate.exception.SQLGrammarException: could not extract ResultSet
org.postgresql.util.PSQLException: ERROR: syntax error at or near "dfs"

查询应为特定ID列出所有直接或间接依赖的子代。 （类似的帖子here）

思路：

我设法使用以下格式修复它：

@Query(nativeQuery = true,
value = "with recursive subfiles as " +
  "(select * " +
  "from t " +
  "where file_id=?1 " +
  "union all " +
  "select dfs.* " +
  "from t dfs " +
  "join subfiles s " +
  "on s.file_id = dfs.parent_folder_id) " +
"select file_id from subfiles")
List listAllByParentId(String folderId);