Given two entities:
CardPurchaseProductGroup, 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'").
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).
