Problem with join fetch

[Michael Simons <mi...@optitool.de>]

Hello,

Assume three classes A (1:N) B (1:N) C:
class A { List<B> bs; }
class B { List<C> cs; }

Why is the following query denied?

select a from A a join fetch a.bs join fetch a.bs.cs;

Also denied:
select a from A a join fetch a.bs b join fetch b.cs;

Why doesn't this create an SQL statement like

select ... from A inner join B inner join C

thanks for any hints,
Michael

Re: Problem with join fetch

[Michael Simons <mi...@optitool.de>]

Hello Catalina,

> If you define ToMany fetch type to EAGER, then a simple
>    select a from A
> will get all Bs and Cs loaded.

unfortunately, using EAGER does not solve the (N+1)-problem. If there are L instances of A each
holds M instances of B and each b has N instances of C then there will be L*M*N queries
generated to get the instances of C.
In the case that we need to solve here, there are about L=100, M=15, N=2 and there's even one
more level D with about 2 instances per each c. So there are about 6000 queries, to get
information that can be fetched in one single select.

Michael

Re: Problem with join fetch

[catalina wei <ca...@gmail.com>]

Hi Michael,
1. The JPQL syntax for join fetch support is restricted to ONE level. So
syntax-wise it is not allowed to go down second level as you did for
a.bs.cs.

If you define ToMany fetch type to EAGER, then a simple
   select a from A
will get all Bs and Cs loaded.

   or you may only need to change B(1:N) C to have EAGER fetch type, then
your JPQL
   would be:
     select a from A a join fetch a.bs

2. JPQL syntax does not allowed range variables defined over the path, that
is why the syntax error  for   join fetch a.bs b.

Because join fetch is limited to fetch related entities at one level,

    if A has (1:N) B  and  A also has (1:N) D
you can submit the following JPQL query to get all As and all Bs and Ds
belong to an  A:

   select a from A a join fetch a.bs join fetch a.ds

Hope this helps.

Catalina Wei

On Fri, May 22, 2009 at 10:09 AM, Michael Simons <michael.simons@optitool.de
> wrote:

> Hello,
>
> Assume three classes A (1:N) B (1:N) C:
> class A { List<B> bs; }
> class B { List<C> cs; }
>
> Why is the following query denied?
>
> select a from A a join fetch a.bs join fetch a.bs.cs;
>
> Also denied:
> select a from A a join fetch a.bs b join fetch b.cs;
>
> Why doesn't this create an SQL statement like
>
> select ... from A inner join B inner join C
>
> thanks for any hints,
> Michael
>