Hibernate version:
2.1

Name and version of the database you are using:
mysql 4.0.2

This is more of an abstract question. It appears not much documentation talks about returning and object and only a subset of items in a collection that the object contains. Using the wonderful box and balls analogy, if I have a box full of different colored balls, and I want to return all boxes - and if they have balls ONLY RED ONES, I would try:

"from Box box left join Box.balls ball where ball.color = 'red'"

However this doesn't put a condition in the ON clause, only the where clause, so it returns only boxes that have at least one red ball. I can't say

"from Box box left join Box.balls ball where ball.color = 'red' or ball is null"

because there might be blue or green balls in the box.

This works:

Boxes = "from Box".list()

Iterator itr = Boxes.iterator();

while (itr.hasNext()) {

box = itr.next();
box.setBalls(session.createFilter(box.getBalls(), "where this.color = 'red'").list());

}

and also doing it in pure mysql is easy because you just attach the red ball condition to the ON clause.

Any tips would be appreciated.