Hibernate: unexpected queries in parent/child relationship

[Vinnie Jenks]

Could someone point out what I'm doing wrong? I have a parent/child relationship between two objects, Customer and Order - a customer can have many orders. The tables are setup the same way and the two join on a pk/fk field called 'cust_id'.

So...it looks like this:

Customer-<Order

Customer.Orders = List<Order> (mapped as a bag, like so):

<hibernate-mapping>
<class name="com.portlets.bol.Customer" table="ps_customer">
  <id name="Id" column="cust_id" type="long" unsaved-value="0">
      <generator class="identity" />
    </id>
  <property name="Name" column="name1" />
  <bag 
  name="Orders" 
  table="sr_order" 
  lazy="true" 
  fetch="join"
inverse="true"
  cascade="all-delete-orphan">
  <key column="cust_id" />
  <one-to-many class="com.portlets.bol.Order" />
  </bag>
</class>
</hibernate-mapping>

I'm trying to get a list of customers between an Order sent-date range. The customers I get back are ones that have orders within the date range but when I try to view the list of orders...they're not filtered...I'm getting every order ever entered by that customer...I would have expected the orders to be filtered as well...but they're not...and that's obviously because of the queries being generated.

My HQL looks like this:

List<Customer> customers = s.createQuery("from Customer c where c.Orders.SentDate between :bd and :ed")
.setDate("bd", startDate)
.setDate("ed", endDate)
.list();

...which generates a query that looks like this:

select customer0_.cust_id as cust1_9_, customer0_.name1 as name2_9_ from ps_customer customer0_, sr_order orders1_ where customer0_.cust_id=orders1_.cust_id and (orders1_.sent_date between ? and ?)

However...when I list the orders for each customer...like so:

List<Order> orders = customers.getOrders();

for (Order o : orders)
{
response.getWriter().println("    • " + o.getId() + "<br />");
}

It generates a query that looks like this:

select orders0_.cust_id as cust4_1_, orders0_.order_id as order1_1_, orders0_.order_id as order1_10_0_, orders0_.transporter_id as transpor2_10_0_, orders0_.inv_pick_list_id as inv3_10_0_, orders0_.cust_id as cust4_10_0_, orders0_.order_date as order5_10_0_, orders0_.sent_date as sent6_10_0_, orders0_.to_name as to7_10_0_, orders0_.to_addr1 as to8_10_0_, orders0_.to_addr2 as to9_10_0_, orders0_.to_city as to10_10_0_, orders0_.to_state as to11_10_0_, orders0_.to_country as to12_10_0_, orders0_.to_postalcode as to13_10_0_ from sr_order orders0_ where orders0_.cust_id in (?, ?, ?, ?, ?)

...bingo...the Order.SentDate isn't filtered for the orders.

I know I'm missing something simple...but I've been staring at it for so long it's eluding me...can someone point out a simple work-around?

[Scott Johnson]

This is by design.

The order date criteria is just used to filter the Customer objects themselves.

Once you have a Customer, calling getOrders() will retrieve all orders for that Customer. The collections inside the Customer objects are not filtered based on your original query criteria.

Try using a collection Filter. Also see Hibernate in Action section 7.5.2.

[Vinnie Jenks]

Yep, that's exactly what I ended up doing after flipping through the "report queries" chapter in Hibernate In Action.

Sure would be nice if you could do this at the top-level w/o having to again filter the data a second time!

Thanks!