How to add a where clause to a Hibernate @OneToMany explicit join table entity?

Question

Given two entities:

• Card
• PurchaseProductGroup, which has a ppg_status column (field named as status on the entity) that can be 'A' (active) or 'D' (deleted)

These conceptually have a many-to-many relationship but an explicitly defined join table entity named PurchaseProductGroupCard is used (so an external ID can be assigned for each mapping). So both Card and PurchaseProductGroup have a @OneToMany relationship to PurchaseProductGroupCard, e.g. in Card there is the following:

@OneToMany(mappedBy = "card")
private Set<PurchaseProductGroupCard> purchaseProductGroups;

This needs to be restricted so that purchaseProductGroups with a status of 'D' are excluded. One approach that seems to work is to put a @Where annotation just below the @OneToMany:

@Where(clause = "exists (select * from purchase_product_group ppg
                         where ppg.ppg_id = ppg_id AND ppg.ppg_status <> 'D')")

...But is there a better way to do this? Would ideally prefer Hibernate to join the tables and have a clause like "purchaseProduct.status <> 'D'".

Answer 1

I turned on SQL logging and examined the query output. For the above case it was this:

/* load one-to-many com.prepaytec.pacasso.common.model.Card.purchaseProductGroups */
select
    * /* the actual field list has been omitted for brevity */
from
    pacasso.purchaseprodgrp_card purchasepr0_
inner join
    pacasso.purchase_product_group purchasepr1_
        on purchasepr0_.ppg_id=purchasepr1_.ppg_id
where
    (
        exists (
            select
                *
            from
                purchase_product_group ppg
            where
                ppg.ppg_id = purchasepr0_.ppg_id
                AND ppg.ppg_status <> 'D'
        )
    )
    and purchasepr0_.crd_id=?

So the necessary join is already included and it looks like all that would be needed is this:

@Where(clause = "ppg_status <> 'D'")

However, it turns out that doesn't work as Hibernate prepends the wrong table alias:

where
    (
        purchasepr0_.ppg_status <> 'D'
    )
    and purchasepr0_.crd_id=?

Unfortunately once an alias is assigned to a table, it isn't possible to use the original table name - so purchase_product_group.ppg_status <> 'D' wouldn't work. And I'm not aware of a way to determine the alias name used by Hibernate programmatically - so at present the choice seems to be either hard-code the alias name that is found to be used by Hibernate (i.e. purchasepr1_.ppg_status <> 'D') or to use the exists method described in the question.

UPDATE: On further investigation it turns out that hard-coding the alias names isn't always workable. Here is a criteria query where this wouldn't work:

/* criteria query */
select
    * /* the actual field list has been omitted for brevity */
from
    pacasso.merchant_acquirer this_ 
left outer join
    pacasso.purchaseprod_merchant_acquirer purchasepr2_ 
        on this_.mac_id=purchasepr2_.mac_id 
        and (
            // This wouldn't work with any alias since the required
            // table is pacasso.purchase_product purchasepr3_, which
            // is joined below.
            purchasepr2_.ppr_status <> 'D' 
        )  
left outer join
    pacasso.purchase_product purchasepr3_ 
        on purchasepr2_.ppr_id=purchasepr3_.ppr_id 
where
    this_.mac_code=? 
    and this_.cst_id=?

In the end I abandoned the @Where approach and used @Filter instead, which seems much better as it can accept HQL rather than database field names and when applied at the entity level will affect relationships (unlike @Where).