JPA criteria multiple joins for same target class

Viewed 26

I'm trying to migrate an SQL query to JPA criteria. The query is used to display the results of a search form and I'm currently blocked on the migration of the JOIN statement.

Here are my simplified Parent and Child entities.

public class Parent {
    
    @OneToOne(cascade = ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "published_id")
    private Child published;
    
    @OneToOne(cascade = ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "draft_id")
    private Child draft;
    
    @OneToOne(cascade = ALL, fetch = FetchType.LAZY)
    @JoinColumn(name = "cancelled_id")
    private Child cancelled;
    
    @OneToMany(cascade = ALL)
    @JoinColumn(name = "parent_id")
    @Builder.Default
    private Set<Child> historic = new HashSet<>();
}

public class Child {

    @ManyToOne(cascade = {PERSIST, MERGE}, fetch = LAZY)
    @JoinColumn(name = "parent_id")
    private Parent parent;

    @OneToOne(cascade = ALL)
    @JoinColumn(name = "organisation_id")
    @ToString.Include
    private Organisation organisation;

    ....
}

Here's the part of the query I'm blocking on :

SELECT DISTINCT ON (parent.id) {child.*},{parent.*}
FROM parent
    JOIN child
ON parent.draft_id = child.id
    OR parent.cancelled_id = child.id
    OR parent.published_id = child.id
    OR child.historic_parent_id = parent.id
    JOIN organisation o ON o.id = child.organisation_id
.....
WHERE child.name = 'Didier'

I've tried to extract the different statements related to each JOIN but I need a way to merge them together so It can be used in the where clause.

Join<Parent, Child> draft = parentRoot.join(Parent_.draft, JoinType.LEFT);
Join<Parent, Child> cancelled = parentRoot.join(Parent_.cancelled, JoinType.LEFT);
Join<Parent, Child> published = parentRoot.join(Parent_.published, JoinType.LEFT);
SetJoin<Parent, Child> historic = parentRoot.join(Parent_.historic, JoinType.LEFT);

Is there a way of migrating the JOIN statement to criteria?

Thank you.

0 Answers
Related