Selecting from grandchildren?

Ayende Rahien · **Joined:** Sat Sep 24, 2005 11:25 am **Posts:** 198

I've a situation similar to the following:

Blog - one to many - Posts - lazy
Post - one to many - Comments - not lazy (right now)

I've a page that needs to scan through all the comments for the blog, but it needs to do it through the Blog object.

The issue is that I've got two SQL Statements for the blog & the posts, and then a sql query per each post, for all the comments.

The code I've right now does something like:

Code:

foreach(Post post in blog.Posts)
{
//Do processing on the post
  foreach(Comment comment in post.Comments)
  {
// do processing on the comment
  }

}

P.S:
There is no connection between the comment & the blog, so I can't use HQL directly to do that (I think).

P.S.S:
I may need to take it another level or two down, (and here the blog analogy breaks), so it's important to find a way to do it.

P.S.S:
I _think_ that I can write a manual query that loads all the comments when I have all the posts, but that would require a big assed IN(), and I understand that this is not performant.

Thanks in advance,
Ayende Rahien

xor · **Posted:** Mon Nov 28, 2005 1:58 pm

Ayende Rahien wrote:

Blog - one to many - Posts - lazy
Post - one to many - Comments - not lazy (right now)

I've a page that needs to scan through all the comments for the blog, but it needs to do it through the Blog object.

In fact, you can do it with one HQL query. I could be wrong with the following variant, but it's anyway similar to the thruth:

Code:

select c
from Blog b
inner join b.Posts p
inner join p.Comments c

Ayende Rahien · **Joined:** Sat Sep 24, 2005 11:25 am **Posts:** 198

Facinating, thanks.

Ayende Rahien · **Joined:** Sat Sep 24, 2005 11:25 am **Posts:** 198

But can I do it like:

Code:

select b
from Blog b
inner join b.Posts p
inner join p.Comments c
where b.Id  =1

Which is more or less what I need

xor · **Posted:** Tue Nov 29, 2005 4:30 am

Should be able to.

s.rottem · **Posted:** Tue Nov 29, 2005 6:45 am

Quote:

Code:

select b
from Blog b
inner join b.Posts p
inner join p.Comments c
where b.Id  =1

Except that this query will only return the Blog #1. The join semantics are only useful if you are going to reference them in the SELECT clause, WHERE clause or are using the FETCH keyword to force population of lazy loaded child entites.

I'm not entirely clear on what you're trying to accomplish, but if you're after the Comment objects associated with the Post objects for a specific Blog then you might be looking for:

Code:

SELECT c
FROM Blog b
INNER JOIN b.Posts p
INNER JOIN p.Comments c
WHERE b.id = :Id

Cheers,

Symon.

Ayende Rahien · **Joined:** Sat Sep 24, 2005 11:25 am **Posts:** 198

That gives me all the comments from the blog.
What I want to get is a Blog with the Posts initialized, and each Post' Comments initialized.

I tried:

select b,p,c
from Blog b
join fetch b.Posts p
join fetch p.Comments c
where b.Id = :id

But it complains about only one fetch allowed per statement.

sergey · **Posted:** Wed Nov 30, 2005 10:21 am

You can try this:

Code:

select b from
  Post p
    join fetch p.Blog b
    join fetch p.Comments c
where b.Id = :id

(possibly using left joins there instead of default inner joins). And use UniqueResult instead of List. The underlying query will return a list of Blog references which will all reference the same Blog object, and UniqueResult will convert it to a single instance (in 1.0.1).

This will leave the b.Posts collection uninitialized though... but all the objects will be loaded.

cneuwirt · **Joined:** Tue May 31, 2005 9:55 am **Posts:** 67

This works great, but what about the situation in which you want multiple results to be returned. In this case UniqueResult won't work since it will only return 1.

craig