How to add a where clause to a Hibernate @ManyToMany relationship?

Question

Given two entities:

• PurchaseProductGroup
• PurchaseProduct, which has a status column that can be 'A' (active) or 'D' (deleted)

and a many-to-many relationship defined in PurchaseProductGroup:

/** The purchase products linked to this group. */
@ManyToMany
@JoinTable(name = "purchaseprodgrp_purchaseprod",
           joinColumns = @JoinColumn(name = "ppg_id"),
           inverseJoinColumns = @JoinColumn(name = "ppr_id"))
private List<PurchaseProduct> purchaseProducts;

How can I restrict this so that purchaseProducts with a status of 'D' are excluded?

Things I've tried

Have tried adding the following just below the @ManyToMany annotation but both failed with an "invalid identifier" exception for the column:

1. @Where(clause = "status <> 'D'")
2. @WhereJoinTable(clause = "purchaseProduct.status <> 'D'")

Also tried using adding @Where(clause = "status <> 'D'") at the entity level but this doesn't seem to affect the contents of relationship collections - as backed up by this question.

Answer 1

Please try the following code:

@ManyToMany
@JoinTable(name = "purchaseprodgrp_purchaseprod",
       joinColumns = @JoinColumn(name = "ppg_id"),
       inverseJoinColumns = @JoinColumn(name = "ppr_id"))
@Where(clause = "ppr_status <> 'D'")
private List<PurchaseProduct> purchaseProducts;

This may be similar to something you have tried before, but a critical point to get this working is that ppr_status is the actual column name. Hence the PurchaseProduct entity should have the following:

@Column(name="ppr_status")
public String getStatus() {
    return status;
}

If you had named the field ppr_status then the @Column may not be necessary. But based on your comments above we need to tell Hibernate how to map this column.

Reference: Hibernate annotations. @Where vs @WhereJoinTable