使用"WITH RECURSIVE"查询DSL

Question

querydsl的GitHub问题中存在此问题。

1.观察到的行为与预期行为

嗨，伙计们，我尝试使用递归查询获取所有子类别。

观察到：

Hibernate: 
with recursive tmp1 (id, parent, name_ko) as ((select
c.id,
c.parent,
c.name_ko 
from
car__grade c 
where
c.parent is null) 
union
all (select
p.id,
p.parent,
p.name_ko 
from
tmp1 
inner join
car__grade p 
on tmp1 = p.parent)) select
tmp1.id,
tmp1.name_ko 
from
tmp1

所有"parent"都是错误的。因为它必须是grade_parent_id。然而，nameKo->name_ko更改正确。

预期行为：

Hibernate: 
with recursive tmp1 (id, **grade_parent_id**, name_ko) as ((select
c.id,
**c.grade_parent_id**,
c.name_ko 
from
car__grade c 
where
**c.grade_parent_id** is null) 
union
all (select
p.id,
**p.grade_parent_id**,
p.name_ko 
from
tmp1 
inner join
car__grade p 
on **tmp1.id = p.grade_parent_id**)) select
tmp1.id,
tmp1.name_ko 
from
tmp1

2.复制的步骤

我希望这是可以理解的。

//实体

@NoArgsConstructor
@Getter
@DynamicInsert
@DynamicUpdate
@Entity
@Table(name = "car__grade")
public class Grade {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(columnDefinition = "BIGINT(20) UNSIGNED")
private BigInteger id;
@Column(name = "name_ko", length = 32, nullable = false,
columnDefinition = "VARCHAR(32)")
private String nameKo;
@OnDelete(action = OnDeleteAction.CASCADE)
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "detail_model_id", nullable = false, updatable = false,
columnDefinition = "BIGINT(20) UNSIGNED")
private DetailModel detailModel;
@QueryInit("*")
@OneToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "grade_parent_id", columnDefinition = "BIGINT(20) UNSIGNED")
private Grade parent = null;
@Builder
public Grade(String nameKo, DetailModel detailModel) {
this.nameKo = nameKo;
this.detailModel = detailModel;
}
}

//存储库代码

QGrade tmp1 = new QGrade("tmp1");
QGrade c = new QGrade("c");
QGrade p = new QGrade("p");
PathBuilder<Grade> emp = new PathBuilder<Grade>(Grade.class, "tmp1");
JPASQLQuery<?> q = new JPASQLQuery<>(em, MySQLTemplates.DEFAULT);
EntityPathBase<QGrade> rec = new EntityPathBase<>(QGrade.class, "tmp1");
SQLQuery<Grade> t =
SQLExpressions.select(Projections.fields(Grade.class, c.id, c.parent, c.nameKo)).from(c)
.where(c.parent.isNull());
SQLQuery<Grade> t1 =
SQLExpressions.select(Projections.fields(Grade.class, p.id, p.parent, p.nameKo)).from(rec)
.innerJoin(p).on(tmp1.eq(p.parent));
Union<Grade> union = SQLExpressions.unionAll(t, t1);
List<Tuple> fetch = q.withRecursive(rec, c.id, c.parent, c.nameKo).as(union)
.select(tmp1.id, tmp1.nameKo).from(rec).fetch();

c.nameKo，p.nameKo更改正确，但父级不是

//DB(此sql在mysqlworkbench上工作(

WITH RECURSIVE tmp1 (
`id`,
`grade_parent_id`,
`name_ko`
) AS (
SELECT  id, 
grade_parent_id,
name_ko
FROM    `car__grade`
WHERE   grade_parent_id IS NULL

UNION ALL

SELECT  r.id,
r.grade_parent_id,
r.name_ko
FROM    `car__grade` AS r
INNER JOIN tmp1 t
ON t.id = r.grade_parent_id
)
SELECT * FROM tmp1;

3.环境

弹簧套2.4.0

Querydsl版本：4.4.0

Querydsl模块：Querydsl jpa

数据库：MySQL8

JDK:11

4.其他详细信息

我用相同的实体测试了简单的jpa查询。

//简单的查询及其结果。

// repo
queryFactory.select(QGrade.grade).from(QGrade.grade).where(QGrade.grade.parent.isNull())
.fetch();
// console (it works normally especially fields 'parent'.)
Hibernate: 
select
grade0_.id as id1_10_,
grade0_.detail_model_id as detail_m3_10_,
grade0_.name_ko as name_ko2_10_,
grade0_.grade_parent_id as grade_pa4_10_ 
from
car__grade grade0_ 
where
grade0_.grade_parent_id is null

Answer 1

Querydsl 4.x不再维护，我建议升级到5.x。

此外，JPASQLQuery在复杂关系联接方面有许多已知的局限性，这就是为什么我通常不鼓励使用JPASQLQuery。相反，我推荐Blaze Persistence Querydsl集成来创建强大的JPQL查询(包括具有递归CTE的查询(。