I have the following entities:
@Entity
@Table(name = "place_revision")
public class PoiRevision {
@OneToMany(mappedBy = "pk.revision", cascade = {CascadeType.ALL})
private Collection<PoiRevisionCategory> categoryMapping;
// ...
}
@Entity
@Table(name = "place_revision__category")
@AssociationOverrides({
@AssociationOverride(name = "pk.revision",
joinColumns = @JoinColumn(name = "place_revision_id")),
@AssociationOverride(name = "pk.category",
joinColumns = @JoinColumn(name = "category_id"))
})
public class PoiRevisionCategory {
@EmbeddedId
private PoiRevisionCategoryId pk = new PoiRevisionCategoryId();
// ...
}
@Embeddable
public class PoiRevisionCategoryId implements Serializable {
@ManyToOne
private PoiRevision revision;
@ManyToOne
private Category category;
// ...
}
@Entity
@Table(name = "category")
public class Category {
@ManyToMany(targetEntity = Section.class, cascade = {CascadeType.PERSIST, CascadeType.MERGE}, fetch = FetchType.LAZY)
@JoinTable(
name = "category__section",
joinColumns = @JoinColumn(name = "category_id"),
inverseJoinColumns = @JoinColumn(name = "section_id")
)
private Collection<Section> sections;
// ...
}
And want to select PoiRevisions that have Categories that have some Sections.
I'm using Spring-data Specification to query the database for these entities.
My intent is to write something like:
Specification<PoiRevision> spec = new Specification<PoiRevision>() {
@Override
public Predicate toPredicate(Root<PoiRevision> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
List<Predicate> conditions = new ArrayList<>(CONDITION_COUNT);
CollectionJoin<PoiRevision, PoiRevisionCategory> mapping = root.join(PoiRevision_.categoryMapping);
// BROKEN here as we cannot use nested path for joins
Join<PoiRevisionCategory, Category> categories = mapping.join("pk.category");
conditions.add(categories.get("sections").in(sections));
// ...
return cb.and(conditions.toArray(new Predicate[] {}));
}
};
But we cannot use nested path for such joins as JPA provider (Hibernate, in my case) looks only for direct properties of PoiRevisionCategory class. And we cannot "join" embedded Id to our result set because it's not a manageable entity.
I'm really stuck with this issue which seems to be far from complicated when translated into SQL yet it has some complexity on the ORM-side.
Any idea is much appreciated.
