You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@openjpa.apache.org by Marc Logemann <li...@logemann.org> on 2011/02/22 19:08:24 UTC
"join fetch" doesnt work as expected
Hi,
assume this code:
Query query = getEntityManager().createQuery("SELECT b FROM Box b JOIN FETCH b.order.orderPositions WHERE b.id = ?1");
query.setParameter(1, boxId);
List<Box> boxList = query.getResultList();
The relationship is:
box <-- 1:1 ---> order <-- 1:n --> orderPositions
When doing this query, i would expect that the orderPositions are attached but they are null (order is attached to the box as expected but thats 1:1) . I checked this right after the query.getResultList() call.
What am i missing here?
thanks for infos.
---
regards
Marc Logemann
http://www.logemann.org
http://www.logentis.de
Re: "join fetch" doesnt work as expected
Posted by Craig L Russell <cr...@oracle.com>.
Hi,
*The association referenced by the right side of the FETCH JOIN clause
must be an association or element*
*collection that is referenced from an entity or embeddable that is
returned as a result of the query.*
I didn't write the specification, but the way I read it, the right
side of the FETCH JOIN clause must be a member of the entity.
It would be good to ping the expert group about this issue.
Meantime, Kevin's proposed solution (multiple FETCH JOIN clauses)
should work, as there's nothing that I can see in the specification to
disallow it.
And it's worth noting that the proposed JSR for JPA.next explicitly
calls for fetch groups and fetch plans to be addressed.
Craig
On Feb 22, 2011, at 1:56 PM, Kevin Sutter wrote:
> Hi Marc,
> If you can't tell, this question is making me curious...
>
> What if you changed your query to be like this:
>
> SELECT b FROM Box b JOIN FETCH b.order JOIN order o JOIN FETCH
> o.orderPositions WHERE b.id = ?1
>
> I don't even know if this will be processed correctly or if it's
> "legal" per
> the spec, but on paper it looks good. :-)
>
> As I dug through our mail archives, I did see where the single level
> of
> fetching is clarified:
> http://openjpa.208410.n2.nabble.com/Problem-with-join-fetch-tc2958609.html
>
> So, the basic question you posted is not a bug. But, there are
> other ways
> of getting around the issue per the posting above. Another thing
> you may
> want to look at is the OpenJPA fetch groups. This would allow you to
> specify programatically when to lazy vs eager load a relationship,
> and how
> many levels to traverse. You can find more information on fetch
> groups here
> [1]. Specifically, take a look at section 7.3 on per-field fetch
> configuration.
>
> Good luck,
> Kevin
>
> [1]
> http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_fetch
>
> On Tue, Feb 22, 2011 at 2:42 PM, Kevin Sutter <kw...@gmail.com>
> wrote:
>
>> I looked at our existing junit tests and it looks like we're
>> testing a
>> single level of fetching. That is, if b <-> orders was 1:n instead
>> of 1:1,
>> then you could do the "JOIN FETCH b.orders". It looks like we have
>> tested
>> that processing. But, not going multiple levels...
>>
>> If I look at the spec (section 4.4.5.3), it states:
>>
>> *The syntax for a fetch join is*
>>
>> *fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH
>> join_association_path_expression*
>>
>> *The association referenced by the right side of the FETCH JOIN
>> clause
>> must be an association or element*
>> *collection that is referenced from an entity or embeddable that is
>> returned as a result of the query.*
>>
>> Since you result of this query is returning "b", and your JOIN
>> FETCH is
>> attempting to access b.order.orderPositions, orderPositions are not
>> directly
>> referenced by b. The spec is not clear on whether the additional
>> levels of
>> traversal are expected.
>>
>> Any other insights from other readers?
>>
>> Kevin
>>
>>
>> On Tue, Feb 22, 2011 at 2:27 PM, Kevin Sutter <kw...@gmail.com>
>> wrote:
>>
>>> Hmmm... It's my understanding that a JOIN FETCH should preload the
>>> relationship, even if it's marked as being Lazy. Have you
>>> performed a SQL
>>> trace to see if we're even attempting to load the relationship?
>>> Unless
>>> someone else speaks up, this would seem to be a bug.
>>>
>>> Kevin
>>>
>>>
>>> On Tue, Feb 22, 2011 at 12:08 PM, Marc Logemann <li...@logemann.org>
>>> wrote:
>>>
>>>> Hi,
>>>>
>>>> assume this code:
>>>>
>>>> Query query = getEntityManager().createQuery("SELECT b FROM
>>>> Box b
>>>> JOIN FETCH b.order.orderPositions WHERE b.id = ?1");
>>>> query.setParameter(1, boxId);
>>>> List<Box> boxList = query.getResultList();
>>>>
>>>> The relationship is:
>>>>
>>>> box <-- 1:1 ---> order <-- 1:n --> orderPositions
>>>>
>>>> When doing this query, i would expect that the orderPositions are
>>>> attached but they are null (order is attached to the box as
>>>> expected but
>>>> thats 1:1) . I checked this right after the query.getResultList()
>>>> call.
>>>> What am i missing here?
>>>>
>>>> thanks for infos.
>>>>
>>>> ---
>>>> regards
>>>> Marc Logemann
>>>> http://www.logemann.org
>>>> http://www.logentis.de
>>>>
>>>>
>>>>
>>>>
>>>>
>>>
>>
Craig L Russell
Architect, Oracle
http://db.apache.org/jdo
408 276-5638 mailto:Craig.Russell@oracle.com
P.S. A good JDO? O, Gasp!
Re: "join fetch" doesnt work as expected
Posted by Kevin Sutter <kw...@gmail.com>.
Hi Marc,
If you can't tell, this question is making me curious...
What if you changed your query to be like this:
SELECT b FROM Box b JOIN FETCH b.order JOIN order o JOIN FETCH
o.orderPositions WHERE b.id = ?1
I don't even know if this will be processed correctly or if it's "legal" per
the spec, but on paper it looks good. :-)
As I dug through our mail archives, I did see where the single level of
fetching is clarified:
http://openjpa.208410.n2.nabble.com/Problem-with-join-fetch-tc2958609.html
So, the basic question you posted is not a bug. But, there are other ways
of getting around the issue per the posting above. Another thing you may
want to look at is the OpenJPA fetch groups. This would allow you to
specify programatically when to lazy vs eager load a relationship, and how
many levels to traverse. You can find more information on fetch groups here
[1]. Specifically, take a look at section 7.3 on per-field fetch
configuration.
Good luck,
Kevin
[1]
http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_fetch
On Tue, Feb 22, 2011 at 2:42 PM, Kevin Sutter <kw...@gmail.com> wrote:
> I looked at our existing junit tests and it looks like we're testing a
> single level of fetching. That is, if b <-> orders was 1:n instead of 1:1,
> then you could do the "JOIN FETCH b.orders". It looks like we have tested
> that processing. But, not going multiple levels...
>
> If I look at the spec (section 4.4.5.3), it states:
>
> *The syntax for a fetch join is*
>
> *fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH
> join_association_path_expression*
>
> *The association referenced by the right side of the FETCH JOIN clause
> must be an association or element*
> *collection that is referenced from an entity or embeddable that is
> returned as a result of the query.*
>
> Since you result of this query is returning "b", and your JOIN FETCH is
> attempting to access b.order.orderPositions, orderPositions are not directly
> referenced by b. The spec is not clear on whether the additional levels of
> traversal are expected.
>
> Any other insights from other readers?
>
> Kevin
>
>
> On Tue, Feb 22, 2011 at 2:27 PM, Kevin Sutter <kw...@gmail.com> wrote:
>
>> Hmmm... It's my understanding that a JOIN FETCH should preload the
>> relationship, even if it's marked as being Lazy. Have you performed a SQL
>> trace to see if we're even attempting to load the relationship? Unless
>> someone else speaks up, this would seem to be a bug.
>>
>> Kevin
>>
>>
>> On Tue, Feb 22, 2011 at 12:08 PM, Marc Logemann <li...@logemann.org> wrote:
>>
>>> Hi,
>>>
>>> assume this code:
>>>
>>> Query query = getEntityManager().createQuery("SELECT b FROM Box b
>>> JOIN FETCH b.order.orderPositions WHERE b.id = ?1");
>>> query.setParameter(1, boxId);
>>> List<Box> boxList = query.getResultList();
>>>
>>> The relationship is:
>>>
>>> box <-- 1:1 ---> order <-- 1:n --> orderPositions
>>>
>>> When doing this query, i would expect that the orderPositions are
>>> attached but they are null (order is attached to the box as expected but
>>> thats 1:1) . I checked this right after the query.getResultList() call.
>>> What am i missing here?
>>>
>>> thanks for infos.
>>>
>>> ---
>>> regards
>>> Marc Logemann
>>> http://www.logemann.org
>>> http://www.logentis.de
>>>
>>>
>>>
>>>
>>>
>>
>
Re: "join fetch" doesnt work as expected
Posted by Kevin Sutter <kw...@gmail.com>.
I looked at our existing junit tests and it looks like we're testing a
single level of fetching. That is, if b <-> orders was 1:n instead of 1:1,
then you could do the "JOIN FETCH b.orders". It looks like we have tested
that processing. But, not going multiple levels...
If I look at the spec (section 4.4.5.3), it states:
*The syntax for a fetch join is*
*fetch_join ::= [ LEFT [OUTER] | INNER ] JOIN FETCH
join_association_path_expression*
*The association referenced by the right side of the FETCH JOIN clause must
be an association or element*
*collection that is referenced from an entity or embeddable that is returned
as a result of the query.*
Since you result of this query is returning "b", and your JOIN FETCH is
attempting to access b.order.orderPositions, orderPositions are not directly
referenced by b. The spec is not clear on whether the additional levels of
traversal are expected.
Any other insights from other readers?
Kevin
On Tue, Feb 22, 2011 at 2:27 PM, Kevin Sutter <kw...@gmail.com> wrote:
> Hmmm... It's my understanding that a JOIN FETCH should preload the
> relationship, even if it's marked as being Lazy. Have you performed a SQL
> trace to see if we're even attempting to load the relationship? Unless
> someone else speaks up, this would seem to be a bug.
>
> Kevin
>
>
> On Tue, Feb 22, 2011 at 12:08 PM, Marc Logemann <li...@logemann.org> wrote:
>
>> Hi,
>>
>> assume this code:
>>
>> Query query = getEntityManager().createQuery("SELECT b FROM Box b
>> JOIN FETCH b.order.orderPositions WHERE b.id = ?1");
>> query.setParameter(1, boxId);
>> List<Box> boxList = query.getResultList();
>>
>> The relationship is:
>>
>> box <-- 1:1 ---> order <-- 1:n --> orderPositions
>>
>> When doing this query, i would expect that the orderPositions are attached
>> but they are null (order is attached to the box as expected but thats 1:1) .
>> I checked this right after the query.getResultList() call.
>> What am i missing here?
>>
>> thanks for infos.
>>
>> ---
>> regards
>> Marc Logemann
>> http://www.logemann.org
>> http://www.logentis.de
>>
>>
>>
>>
>>
>
Re: "join fetch" doesnt work as expected
Posted by Kevin Sutter <kw...@gmail.com>.
Hmmm... It's my understanding that a JOIN FETCH should preload the
relationship, even if it's marked as being Lazy. Have you performed a SQL
trace to see if we're even attempting to load the relationship? Unless
someone else speaks up, this would seem to be a bug.
Kevin
On Tue, Feb 22, 2011 at 12:08 PM, Marc Logemann <li...@logemann.org> wrote:
> Hi,
>
> assume this code:
>
> Query query = getEntityManager().createQuery("SELECT b FROM Box b
> JOIN FETCH b.order.orderPositions WHERE b.id = ?1");
> query.setParameter(1, boxId);
> List<Box> boxList = query.getResultList();
>
> The relationship is:
>
> box <-- 1:1 ---> order <-- 1:n --> orderPositions
>
> When doing this query, i would expect that the orderPositions are attached
> but they are null (order is attached to the box as expected but thats 1:1) .
> I checked this right after the query.getResultList() call.
> What am i missing here?
>
> thanks for infos.
>
> ---
> regards
> Marc Logemann
> http://www.logemann.org
> http://www.logentis.de
>
>
>
>
>