Hello Martin,
The specification does not allow multiple levels within the JOIN FETCH clause (ie no : m.mailRecipients.user).
The specification also does not allow defining variable on the JOIN FETCH clause(ie no: JOIN FETCH m.mailRecipients r)
(see section 4.14)
What should be possible is :
SELECT m FROM MailMessage m JOIN m.mailRecipients r JOIN FETCH m.mailRecipients JOIN FETCH r.users WHERE ...
This syntax is correct but there is currently an outstanding known issue where the SQL will not be generated correctly.
--Gordon
-----Original Message-----
From: Martin Bayly [mailto:mbayly_at_telus.net]
Sent: Friday, January 26, 2007 3:53 PM
To: Glassfish Persistence List
Subject: Is it possible to Traverse relationships with FETCH JOIN?
FETCH JOIN is a good optimization to load dependent objects without
issuing N+1 selects.
However, it only seems to allow you to pre-load child objects of the
main entity being created. I can't find a way to cause it to load the
child objects of a child of the entity being queried. I took a quick
look at the JPA spec and it doesn't seem to specifically include or
exclude that scenario.
For example I have the following model.
A MailMessage has OneToMany MailRecipents which have OneToOne User
MailMessage 0--->m MailRecipients 1 ---> 1 User
If I want a query to preload all the recipients for each mail message I
can do this:
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
However, I could not find a way to to prefetch the user object for each
mail recipient.
This approach throws an exception complaining about a Syntax error
parsing query, due to an Unexpected Token [.]
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients
LEFT JOIN FETCH m.mailRecipients.user
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
This approach throws an exception complaining about a Syntax error
parsing query, due to an Unexpected Token [r]
SELECT m
FROM MailMessage m
LEFT JOIN FETCH m.mailRecipients r
LEFT JOIN FETCH r.user
WHERE m.course = :course
AND m.sender = :user
AND m.messageStatus = :status
The Hibernate Entity Manager reference manual has an example of the
latter type of query, though I haven't tried to see if it actually
works. Also the Java Persistence with Hibernate book claims that the
second approach described above is valid too.
select cat from Cat as cat
inner join fetch cat.mate
left join fetch cat.kittens child
left join fetch child.kittens
Can you clarify if what I am trying to do is even permitted by JPA QL
I'm currently using b26 - I have not tried this with a later build.
Thanks
Martin