I have the following entities

@Entity
class TaskEntity(
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "parent_task_id")
    override var parentTask: TaskEntity? = null,
) {
    @Id
    @GeneratedValue
    override var id: UUID? = null

    @OneToMany(
        fetch = FetchType.LAZY,
        cascade = [CascadeType.ALL],
        mappedBy = "task",
        orphanRemoval = true
    )
    var items: MutableSet<TaskItemEntity> = mutableSetOf()
}

@Entity
class TaskItemEntity(
    @ManyToOne(fetch = FetchType.LAZY)
    var task: TaskEntity,
    var text: String
) {
    @Id
    @GeneratedValue
    override var id: UUID? = null
}

Given a task id, I want to fetch all descendant tasks from the database. Since executing recursive queries using HQL is impossible, I resorted to using the native query.

session.createNativeQuery(
    """
        WITH RECURSIVE descendants AS (
            SELECT *
            FROM task
            WHERE id = :id
            UNION
                SELECT t.*
                FROM task t
                INNER JOIN descendants d ON d.id = t.parent_task_id
        ) 
        SELECT d.id as taskId, d.parent_task_id, i.id as itemId, i.text
        FROM descendants d
        INNER JOIN task_item i on i.task_id = d.id;
    """.trimIndent(),
    TaskEntity::class.java
).setParameter("id", taskId)

However, to initialize the whole Task aggregate properly I have to fetch all task item entities as well, therefore I have to include the join statement in the native query. This means that the result of the native query would look something like this

+--------+----------------+--------+--------+
| taskId | parent_task_id | itemId |  text  |
+--------+----------------+--------+--------+
|      0 | null           |      0 | "Text" |
|      0 | null           |      1 | "Text" |
|      0 | null           |      2 | "Text" |
|      1 | null           |      3 | "Text" |
+--------+----------------+--------+--------+

Naturally, multiple rows share the same task id as they have multiple task items. What I want from the hibernate is to group the rows by task id, and create TaskEntity with an initialized collection of task items. Basically, I want to reproduce the join fetch behavior but with a native query.

I tried:

I can explicitly call Mutiny.fetch(task.items) for each task in the resultant collection, this will work but will result in N SQL queries which in the case of thousands of tasks is not an option. I also gave up and tried to use 2 queries. The first one was to get all the descendants and the second to get all the items, and then in the code group the items and attach them to the TaskEntity, this didn’t work either as the new collection (created by me) wasn’t managed by hibernate.