具有左联接和"not exists"的 JPA 查询

Question

我正在使用涉及以下三个实体的topLink编写JPA查询。

@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;

我需要找到所有没有给定用户优惠券的优惠，或者在一天前发行了所有用户的优惠券。我尝试了许多不同的方法，到目前为止，我提出了唯一的查询，这并没有崩溃部署时的服务器是：

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)

，但是当优惠券条目不存在时，它不会返回报价。

Answer 1

我设法找到了一个工作查询。如果有人有类似的问题，请将其留在此处参考：

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)