Query q: how to select parents based on child's value

Randy Kahle · **Joined:** Mon Apr 19, 2004 7:10 pm **Posts:** 7

I have parent and child objects:

Code:

public class Parent
  {
  Set children = new HashSet();

  ...
   }

public class Child
  {
  String name;

  ...
   }

with all the appropriate setters and getters, mapping files, etc.

My question is about the HQSL syntax and capabilities.

I would like to select all Parent objects that have one or more children with the name "Ron".

I tried:

Code:

Parent parent where parent.children.name='Ron'

But that won't work.

Any tips appreciated!

steve · **Posted:** Mon Apr 19, 2004 10:05 pm

randy from az?

"parent.children" is a convenience syntax for joins across single-multiplicity associations. However, your Parent.children association defines a many-multiplicty association, so you have to specify the join explicitly. Try this:

from Parent as p inner join p.children as c where c.name = :childName

Randy Kahle · **Joined:** Mon Apr 19, 2004 7:10 pm **Posts:** 7

Yes, Tucson, AZ... How did you know?

Thanks for the reply.

I got some very interesting and unexpected results.

The exact query is

Code:

String shipname = "Extra photos";
List result = session.find( "from User as u inner join u.shipments as s where s.name = :shipName ", shipname, Hibernate.STRING);

The result of this is a List of arrays, each of which contains two Objects, a Parent (i.e., User) and a Child (i.e., Shipment).

For example, the following code calls the toString() method on each object:

Code:

iter = result.iterator();
while( iter.hasNext())
  {
  Object[] oa = (Object[])iter.next();
  System.out.println( oa.length);
  for (int i = 0; i < oa.length; i++)
    {
    System.out.println(oa[i]);
    }
  }

With the following output:

Code:

2
<User>Id: [40288134fc05daa300fc05daab0c0001], Name: [Randy Kahle], Email: [randy@foo.org], Shipments: [[<Shipment>Id: [40288134fc05daa300fc05daab480009], Name: [Trip Photos], Priority: [null]</Shipment>, <Shipment>Id: [40288134fc05daa300fc05daab4a000a], Name: [Extra photos], Priority: [null]</Shipment>]]</User>
<Shipment>Id: [40288134fc05daa300fc05daab4a000a], Name: [Extra photos], Priority: [null]</Shipment>

Questions:

* Is a List of arrays the expected result?

* How might the query be changed to return just the Parent (i.e. User) object?

christian · **Posted:** Mon Apr 19, 2004 11:13 pm

This is expected. Read the documentation about the FROM and SELECT clauses in HQL.

steve · **Posted:** Mon Apr 19, 2004 11:27 pm

its steve ebersole from austin. :)

Randy Kahle · **Joined:** Mon Apr 19, 2004 7:10 pm **Posts:** 7

Thanks, I just reviewed it again. Yes, it is clear that if multiple items are selected, they must be returned via an array mechanism.

What I a wondering is if the query can be reformulated to only return the Parent (i.e. User) objects.

It would seem that some sort of sub-query should work. I tried figuring out a sub-query for this example, but I did not have any luck.

I am hoping that the soon-to-be-published book on Hibernate has a comprehensive explanation of HSQL.

christian · **Posted:** Mon Apr 19, 2004 11:30 pm

It's HQL, and you get what you want with a simple "select u" in your query.

Randy Kahle · **Joined:** Mon Apr 19, 2004 7:10 pm **Posts:** 7

Steve, hi! -- please send me an email to randy.kahle @ variantia.net.

Randy Kahle · **Joined:** Mon Apr 19, 2004 7:10 pm **Posts:** 7

Christian -- very cool.

It worked exactly as you described, and as needed.

Thank you very much.