Re: [eclipselink-users] multible fetch joins

[Michael Simons <michael.simons@xxxxxxxxxxx>]

Hi Gordon,

Gordon Yorke schrieb:
>  Is there an eager relationship to the "localized_article" that is not
> joined in the relationship? 
No there isn't.

> Also unless you actually need multiple levels of fetching (which you are
> not using in your example) you could simply write the query as:
> "SELECT a FROM Article a JOIN FETCH a.locals"
Actually there are other joins. The whole code looks this:
Query query = em.createQuery ("SELECT a from Article a left join fetch a.packings");
query.setHint (QueryHints.FETCH, "a.product");
query.setHint (QueryHints.LEFT_FETCH, "a.packings.packing");
query.setHint (QueryHints.LEFT_FETCH, "a.locals");
query.setHint (QueryHints.REFRESH, HintValues.TRUE);
Collection<?> c = query.getResultList ();
...

The generated SQL is:
SELECT t1.article_id, [...]
FROM article t1
LEFT OUTER JOIN article_packing t0 ON (t0.article_id = t1.article_id)
LEFT OUTER JOIN article_packing t3 ON (t3.article_id = t1.article_id)
LEFT OUTER JOIN packing t4 ON (t4.packing_id = t3.packing_id)
LEFT OUTER JOIN localized_article t5 ON (t5.article_id = t1.article_id),
product t2
WHERE (t2.product_id = t1.product_id)

That looks ok for me, so far. So I will remind not to use a "standalone" join in a query hint.

Thanks, Michael

> --Gordon
> 
> Michael Simons wrote:
>> Hi Tom,
>>
>> so now I'm using hints on queries like
>> query.setHint("eclipselink.join-fetch", "e.address");
>>
>> Unfortunately this doesn't work like expected, at least not always.
>>
>> (Model: Article(1:N)LocalizedArticle
>>
>> The following code
>> 		query = em.createQuery ("SELECT a from Article a");
>> 		query.setHint ("eclipselink.left-join-fetch", "a.locals");
>> 		query.getResultList ();
>>
>> leads to the following SQL:
>>
>> [EL Fine]: 2009-12-01
>> 18:42:20.748--ServerSession(8279577)--Connection(7438069)--Thread(Thread[main,5,main])--SELECT
>> t1.article_id, t1.needs_addtank, t1.ext_id, t1.jdo_version, t1.alt_ex_id, t1.product_id,
>> t1.caution_id, t0.localized_article_id, t0.nme, t0.jdo_version, t0.article_id, t0.lnguage_id
>> FROM article t1 LEFT OUTER JOIN localized_article t0 ON (t0.article_id = t1.article_id)
>>
>> [EL Fine]: 2009-12-01
>> 18:42:20.763--ServerSession(8279577)--Connection(7438069)--Thread(Thread[main,5,main])--SELECT
>> localized_article_id, nme, jdo_version, article_id, lnguage_id FROM localized_article WHERE
>> (article_id = ?)
>>
>> The second statement is repeated for each Article.
>>
>> I don't understand why the second statement is generated at all. All columns are already part of
>> the first statement.
>>
>> Might this be a bug? I guess it might because this happens not always.
>> I assured that the association is FetchType=LAZY in both directions.
>> Any help would be appreciated.
>>
>> -Michael
>>
>>
>> Tom Ware schrieb:
>>   
>>> Hi Michael,
>>>
>>>   The limitation on JPQL fetch joins comes from the JPA specification.
>>>
>>>   Have a look at the following link to see how you can get multiple
>>> fetch joins in all versions of EclipseLink.
>>>
>>> http://wiki.eclipse.org/Using_EclipseLink_JPA_Extensions_%28ELUG%29#Join_Fetch
>>>
>>>
>>>   We are currently considering what the feature set will be for
>>> EclipseLink 2.1 and the following enhancement request covers expanding
>>> EclipseLink's JPQL capabilities beyond the JPA spec.  Please feel free
>>> to vote for it and add any comments you have.
>>>
>>> https://bugs.eclipse.org/bugs/show_bug.cgi?id=219814
>>>
>>> -Tom
>>>
>>>
>>>
>>> Michael Simons wrote:
>>>     
>>>> Hello [EclipseLink 1.2.0, database=MySQL 5],
>>>>
>>>> I'm trying to port a client/server application first to JPA to enable
>>>> the 3-tier migration.
>>>> At the moment I try to use EclipseLink as JPA implementation.
>>>>
>>>> EclipseLink 1.2.0 does not support multible fetch joins like
>>>> select a from A a fetch join A.b b ...
>>>> because the alias 'b' is already denied.
>>>>
>>>> Will this be supported in the 2.x of EclipseLink?
>>>>
>>>> Kind Regards,
>>>> Michael
>>>>
>>>> _______________________________________________
>>>> eclipselink-users mailing list
>>>> eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>>       
>>> _______________________________________________
>>> eclipselink-users mailing list
>>> eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>>
>>>     
>>
>> _______________________________________________
>> eclipselink-users mailing list
>> eclipselink-users@xxxxxxxxxxx <mailto:eclipselink-users@xxxxxxxxxxx>
>> https://dev.eclipse.org/mailman/listinfo/eclipselink-users
>>   
> 
> ------------------------------------------------------------------------
> 
> _______________________________________________
> eclipselink-users mailing list
> eclipselink-users@xxxxxxxxxxx
> https://dev.eclipse.org/mailman/listinfo/eclipselink-users