Query for restricting associated entities

Question

I would like to form a query where an associated collection has been restricted, ideally with Hibernate Criteria or HQL, but I'd be interested in how to do this in SQL. For example, say I have a Boy class with a bidirectional one-to-many association to the Kites class. I want to get a List of the Boys whose kites' lengths are in a range.

The problem is that the HQL/Criteria I know only gets me Boy objects with a complete (unrestricted) Set of Kites, as given in these two examples (the HQL is a guess). I.e., I get the Boys who have Kites in the right range, but for each such Boy I get all of the Kites, not just the ones in the range.

select new Boy(name) from Boy b 
       inner join Kite on Boy.id=Kite.boyId 
             where b.name = "Huck" and length >= 1;

Criteria crit = session.createCriteria(Boy.class);
crit.add(Restrictions.eq("name", "Huck"))
    .createCriteria("kites")
    .add(Restrictions.ge("length", new BigDecimal(1.0)));
List list = crit.list();

Right now the only way I have to get the correct Kite length Sets is to iterate through the list of Boys and for each one re-query Kites for the ones in the range. I'm hoping some SQL/HQL/Criteria wizard knows a better way. I'd prefer to get a Criteria solution because my real "Boy" constructor has quite a few arguments and it would be handy to have the initialized Boys.

My underlying database is MySQL. Do not assume that I know much about SQL or Hibernate. Thanks!

Answer 1

I'm no hibernate expert, but as you say you're interested in the SQL solution as well...:

In SQL, I assume you mean something like (with the addition of indices, keys, etc):

CREATE TABLE Boys (Id INT, Name VARCHAR(16))
CREATE TABLE Kites(Length FLOAT, BoyID INT, Description TEXT)

plus of course other columns &c that don't matter here.

All boys owning 1+ kites with lenghts between 1.0 and 1.5:

SELECT DISTINCT Boys.*
FROM Boys
JOIN Kites ON(Kites.BoyID=Boys.ID AND Kites.Length BETWEEN 1.0 AND 1.5)

If you also want to see the relevant kites' description, with N rows per boy owning N such kites:

SELECT Boys.*, Kites.Length, Kites.Description
FROM Boys
JOIN Kites ON(Kites.BoyID=Boys.ID AND Kites.Length BETWEEN 1.0 AND 1.5)

Hope somebody can help you integrate these with hybernate...!

Answer 2

It turns out that this is best done by reversing the join:

Criteria crit = session.createCriteria(Kite.class);
crit.add(Restrictions.ge("length", new BigDecimal(1.0))
.createCriteria("boy")
.add(Restrictions.eq("name", "Huck")));
List<Kite> list = crit.list();

Note that the list's Kites need to be aggregated into Boys, this can be done easily with a HashMap.