Jboss 3.0.4.
2 entities: StoreCategory and Partner.
2 relationships between them:
1)
<ejb-relation>
<ejb-relation-name>Partner - StoreCategory</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>PartnerStoreCategoryRole</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source>
<ejb-name>Partner</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>storeCategories</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>StoreCategoryPartnerRole</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>StoreCategory</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>partnerOwner</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
2)
<ejb-relation>
<ejb-relation-name>Partners - StoreCategory</ejb-relation-name>
<ejb-relationship-role>
<ejb-relationship-role-name>PartnerStoreCategoryRole</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>Partner</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>categories</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role>
<ejb-relationship-role-name>StoreCategoryPartnerRole</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source>
<ejb-name>StoreCategory</ejb-name>
</relationship-role-source>
<cmr-field>
<cmr-field-name>partners</cmr-field-name>
<cmr-field-type>java.util.Collection</cmr-field-type>
</cmr-field>
</ejb-relationship-role>
</ejb-relation>
ejbql:
SELECT OBJECT(c) FROM StoreCategory c, IN (c.partners) assignedPartner
WHERE c.id=?1 AND (assignedPartner.id=?2 OR c.partnerOwner.id=?2)
which should find specified StoreCategory participating EITHER in first relationship with specified Partner OR in second one or both.
sql generated:
SELECT t0_c.id FROM storecategory t0_c, partner t1_assignedpartner, partner_categories_stor_1cl2gdd t3_c_partners_RELATION_TABLE, partner t2_c_partnerOwner WHERE (t0_c.id = ? AND (t1_assignedpartner.id = ? OR t2_c_partnerOwner.id = ?)) AND (t0_c.partnerOwner=t2_c_partnerOwner.id AND t0_c.id=t3_c_partners_RELATION_TABLE.StoreCategory AND t1_assignedpartner.id=t3_c_partners_RELATION_TABLE.Partner)
which does inner join of all three tables (StoreCategory, Partner and M-2-M relation table). So it will by definition select StoreCategories participating ONLY in BOTH relationships at the same time.
Or maybe I missed something...
