CODEWITHSUNDEEP
jpatoplink
toimcio
toimcio

Reputation: 23

JPA query with left join and "not exists"

I am writing a JPA query using TopLink which involves the following three entities.

@Entity
@Table(name = "OFFERS")
public class Offers implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO, generator="offers_seq_gen")
    @SequenceGenerator(name="offers_seq_gen", sequenceName="OFFERS_SEQ")
    @Basic(optional = false)
    @Column(name = "OFFERID")
    private Long offerid;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "offers", fetch = FetchType.LAZY)
    private List<Coupons> couponsList;
}

@Entity
@Table(name = "COUPONS")
public class Coupons implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO, generator="coupons_seq_gen")
    @SequenceGenerator(name="coupons_seq_gen", sequenceName="COUPONS_SEQ")
    @Basic(optional = false)
    @Column(name = "COUPONID")
    private Long couponid;

    @Basic(optional = false)
    @Column(name = "ISSUED", columnDefinition="TIMESTAMP DEFAULT CURRENT_TIMESTAMP")
    @Temporal(TemporalType.TIMESTAMP)
    private Date issued;

    @JoinColumn(name = "USERID", referencedColumnName = "USERID")
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Users users;

    @JoinColumn(name = "OFFERID", referencedColumnName = "OFFERID")
    @ManyToOne(optional = false, fetch = FetchType.LAZY)
    private Offers offers;

@Entity
@Table(name = "USERS")
public class Users implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.AUTO, generator="users_seq_gen")
    @SequenceGenerator(name="users_seq_gen", sequenceName="USERS_SEQ")
    @Basic(optional = false)
    @Column(name = "USERID")
    private Long userid;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "users", fetch = FetchType.LAZY)
    private List<Coupons> couponsList;

I need to find all the Offers who either have no coupons for a given user or all the coupons for the user were issued more than a day ago. I have tried many different approaches and the only query I have come up with so far, which does not crash the server on deployment is:

SELECT o 
FROM Offers o 
LEFT JOIN o.couponsList c 
WHERE 
    c.users.userid = :userid AND c.issued < :yesterday 
    OR 
    NOT EXISTS 
        (SELECT c1 
         FROM Coupons c1 
         WHERE c1.offers = o AND c1.users.userid = :userid)

But it does not return the Offer when the Coupons entry does not exist.

Upvotes: 0

Views: 2411

Answers (1)

toimcio
toimcio

Reputation: 23

I managed to find a working query. Leaving it here for reference if anyone had similar issues:

SELECT o FROM Offers o WHERE 
    NOT EXISTS 
        (SELECT c FROM Coupons c WHERE c.users.userid = :userid 
             AND c.issued > :yesterday AND c.offers = o) 
    OR NOT EXISTS 
        (SELECT c1 FROM Coupons c1 WHERE c1.offers = o 
             AND c1.users.userid = :userid)

Upvotes: 1

Related Questions