I have the below entities and wondering how I can create the below query using JPA specifications.
SELECT p.*
FROM property p
INNER JOIN ad
ON ad.id = p.ad_id
LEFT JOIN featured_ad fad
ON fad.id = ad.id
ORDER BY fad.start_date DESC,
ad.created_ts DESC
Entities (methods and other properties were removed to simplify the classes):
public class Ad {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne
private User user;
}
public class Property implements {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@OneToOne
@JoinColumn(unique = true)
private Ad ad;
@OneToOne
@JoinColumn(unique = true)
private Location location;
}
public class FeaturedAd {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@NotNull
@Enumerated(EnumType.STRING)
@Column(name = "status", nullable = false)
private FeaturedAdStatus status;
@Column(name = "start_date")
private Instant startDate;
@Column(name = "end_date")
private Instant endDate;
@ManyToOne
private Ad ad;
}
I could join property with add without issue but joining with featuredAd then order by its start_date is the one I couldn't figure out.
specification = specification.and(new Specification<Property>() {
@Override
public Predicate toPredicate(Root<Property> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
final Join<Ad, Property> adProperty = root.join("ad", JoinType.INNER);
//how to join to featuredAd the order by startDate?
// what to return here?
}
});