0

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? 
   }
 });
zoro74
  • 171
  • 15
  • Add an association from Property to FeatureAd to make a bidirectional association. – K.Nicholas Sep 01 '18 at 10:16
  • Thanks for the reply @K.Nicholas. The association exists already between ad and featuredAd. I don't want to add another one between featuredAd and property. – zoro74 Sep 01 '18 at 10:47
  • You have a unidirectional association but you want to go the other direction. I guess zoro's sword is getting a little dull. – K.Nicholas Sep 01 '18 at 14:32

0 Answers0