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.