QueryDSL WITH RECURSIVE递归查询parent字段映射异常问题

1. 实际行为与预期行为 #

尝试通过递归查询获取所有子分类，但生成的SQL中parent字段未正确映射到数据库列grade_parent_id，而普通字段nameKo能正确转换为name_ko。

实际生成的SQL #

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

预期生成的SQL #

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();

可正常运行的原生SQL（MySQL Workbench测试通过） #

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. 环境信息 #

• Spring Boot 2.4.0
• Querydsl版本：4.4.0
• Querydsl模块：querydsl-jpa
• 数据库：MySQL8
• JDK：11

4. 额外细节 #

使用相同实体进行简单JPA查询时，字段映射正常：

简单查询代码 #

queryFactory.select(QGrade.grade).from(QGrade.grade).where(QGrade.grade.parent.isNull())
        .fetch();

控制台输出SQL #

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

问题原因及解决方案 #

原因 #

递归查询中直接引用实体关联字段c.parent时，QueryDSL会将其当作实体对象处理，而非映射到对应的外键列grade_parent_id；但普通字段（如nameKo）能正确映射到数据库列。

解决方案 #

直接引用外键列的路径，而非关联实体对象。修改后的查询代码如下：

QGrade tmp1 = new QGrade("tmp1");
QGrade c = new QGrade("c");
QGrade p = new QGrade("p");

JPASQLQuery<?> q = new JPASQLQuery<>(em, MySQLTemplates.DEFAULT);
EntityPathBase<QGrade> rec = new EntityPathBase<>(QGrade.class, "tmp1");

// 替换为直接引用外键列
SQLQuery<Grade> t =
    SQLExpressions.select(c.id, c.get("grade_parent_id"), c.nameKo)
        .from(c)
        .where(c.get("grade_parent_id").isNull());

SQLQuery<Grade> t1 =
    SQLExpressions.select(p.id, p.get("grade_parent_id"), p.nameKo)
        .from(rec)
        .innerJoin(p).on(tmp1.id.eq(p.get("grade_parent_id")));

Union<Grade> union = SQLExpressions.unionAll(t, t1);

List<Tuple> fetch = q.withRecursive(rec, c.id, c.get("grade_parent_id"), c.nameKo)
    .as(union)
    .select(tmp1.id, tmp1.nameKo)
    .from(rec)
    .fetch();

如果QGrade类生成了对应grade_parent_id的字段（如gradeParentId），也可以直接用c.gradeParentId替代c.get("grade_parent_id")，写法更规范。

内容的提问来源于stack exchange，提问作者Wooyoung Tyler Kim