Hello,

Having the mapping below, I can't manage to have the sql-query producing results. I checked all conditions in isolation and everything else works except for the join table PRODUCT_ORDER_REL_ when I try doing the inner join Product / ProductOrder / Order I get no rows even though loading an Order loads all associated products. The PRODUCT_ORDER_REL_ table does not have any mapping or declaration ... is that a problem?

TIA,
regards

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping package="perfectjpattern.jee.database.datamodel">

<!-- Customer is a strong entity that contains Orders -->
<class name="Customer" table="CUSTOMER_" >
<id name="id" column="ID" >
<generator class="native" />
</id>

<property name="name" column="NAME" update="false" />

<list name="orders" cascade="save-update" >
<key column="CUSTOMER_ID" />
<list-index column="ID" />
<one-to-many class="Order" />
</list>

</class>

<!-- Order is a weak entity that depends on Customer and contains Products -->
<class name="Order" table="ORDER_" >
<id name="id" column="ID">
<generator class="native" />
</id>

<many-to-one name="customer" class="Customer" column="CUSTOMER_ID" />

<set name="products" table="PRODUCT_ORDER_REL_" cascade="save-update, delete"
inverse="true" >
<key column="ORDER_ID" />
<many-to-many class="Product" column="PRODUCT_ID" />
</set>

<property name="date" column="DATE" update="false" />
</class>

<!-- Product is a strong entity and references the orders -->
<class name="Product" table="PRODUCT_" >
<id name="id" column="ID" >
<generator class="native" />
</id>

<property name="name" column="NAME" update="false" />
<property name="listPrice" column="LIST_PRICE" update="true" />

<set name="orders" table="PRODUCT_ORDER_REL_" cascade="save-update, delete" >
<key column="PRODUCT_ID" />
<many-to-many class="Order" column="ORDER_ID" />
</set>
</class>

<!-- Named SQL Query -->
<sql-query name="Customer.findByTotalSpendingWithinPeriod">
<return alias="c1" class="perfectjpattern.jee.database.datamodel.Customer"/>
<![CDATA[SELECT c1.*
FROM CUSTOMER_ c1
WHERE c1.ID IN
(SELECT c2.ID
FROM CUSTOMER_ c2,
ORDER_ o,
PRODUCT_ORDER_REL_ por,
PRODUCT_ p
WHERE o.date BETWEEN :Begin AND :End AND
c2.ID=o.CUSTOMER_ID AND
o.ID=por.ORDER_ID AND
por.PRODUCT_ID=p.ID
GROUP BY c2.ID
HAVING SUM(p.LIST_PRICE) > :Total
)
]]>
</sql-query>

</hibernate-mapping>