EXISTS for child objects

imchi · **Joined:** Tue May 02, 2006 10:04 am **Posts:** 29

I have a "Parent" and a "Child" classes in a simple one-to-many relationship. I want to fetch all Parents that contain at least one Child with a given property (e.g. "name"). In SQL, it would be something like this:

Code:

select p.*
from parent p
where exists
(select c.childId from child c
where c.parentId = p.parentId and c.name=:name)

.

I also want a fetch join (i.e. I want to force the eager population of the Children collection in a Parent with *all* its children).

How do I do it in HQL? I tried something like this:

Code:

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

But it does not populate *all* children in the Parent, only those that
match the :name parameter.

I need to get the full collection.

Any help would be greatly appreciated!

jbosseur · **Posted:** Tue May 30, 2006 5:16 pm

Hibernate documentation chapter : 14.12 subqueries:

Code:

from Cat as cat
where not exists (
from Cat as mate where mate.mate = cat
)

So you would get something like:

Code:

from Parent p 
       inner join fetch p.children c
      where  EXISTS (
            From Child c2 
             where c.parent = c2.parent
            AND   c2.name=:name
      )

Good luck

steve · **Posted:** Tue May 30, 2006 6:07 pm

This is not quite correct. It is generally not a good idea to restrict the result of a fetch join to a collection association; doing so will cause the collection to be only partial populated.

The correct solution would be the same basic thing you would do in sql. Assuming a bidirectional association:

from Parent p
inner join fetch p.children as c
where exists (
select c2.id
from Child as c2
where c2.parent = p
and c2.name = :name
)

jbosseur · **Posted:** Tue May 30, 2006 6:16 pm

steve wrote:

This is not quite correct. It is generally not a good idea to restrict the result of a fetch join to a collection association; doing so will cause the collection to be only partial populated.

The correct solution would be the same basic thing you would do in sql. Assuming a bidirectional association:

from Parent p
inner join fetch p.children as c
where exists (
select c2.id
from Child as c2
where c2.parent = p
and c2.name = :name
)

I tried it both ways and got the same result I can imagine there would be a difference using in but since i am using exists It doesn´t seem to matter...
am I correct???

imchi · **Joined:** Tue May 02, 2006 10:04 am **Posts:** 29

I finally was able to solve the problem with this kind of statement:

Code:

from Parent p inner join p.children c
where exists (from p.children c2 where c2.name=:name)

The two statements posted above generated syntax errors. I guess this is
because I am using the classic query translator, not the v3 translator.

Thanks everyone for help.

jbosseur · **Posted:** Wed May 31, 2006 2:07 pm

imchi wrote:

Code:

from Parent p inner join p.children c
where exists (from p.children c2 where c2.name=:name)

The two statements posted above generated syntax errors. I guess this is
because I am using the classic query translator, not the v3 translator.

Thanks everyone for help.

That one is quit nice.....