Hi Andrus;
Thanks for that –– is there a resource book/web-site/cheat-sheet on EJBQL which
you might be able to recommend? My Googling has only really dredged up poor
material thus far. I am a new to EJBQL so am not sure on the significance of
the "c" in this old query I was running, but adding "FETCH" seems to not be
able to parse it;
...JOIN u.someToMany c WHE...
For this reason, I have removed the "c". I tried both;
SELECT u FROM FooBar u LEFT OUTER JOIN FETCH u.someToMany WHERE
u.userType = :userType ORDER BY u.username
SELECT u FROM FooBar u LEFT JOIN FETCH u.someToMany WHERE u.userType =
:userType ORDER BY u.username
I guess the latter "LEFT JOIN FETCH" makes more sense to me in this case. In
both cases I find that the to-many relationship is loaded, but is empty. If I
remove the JOIN FETCH and restart the application server it will produce the
correct result.
The SQL it runs seems to gather all the columns for "u" (above) and then
additional rows from the to-many. The data is there when I run the query in a
console, but the to-many seems to be empty in the java environment when I get
the "u" objects back from Cayenne.
Maybe I am doing something wrong?
cheers.
> Yes, the syntax is something like this:
> [LEFT[OUTER]|INNER]JOIN FETCH
> So you are looking for "LEFT JOIN FETCH", not just "LEFT JOIN".
> I.e. "FETCH" is what does the prefetching of a relationship, and the rest is
> specifying the join semantics and can be used with or without prefetching.
...
>> Thanks for the suggestion. I did try that with this;
>> SELECT u FROM FooBar u OUTER JOIN u.someToMany c WHERE u.userType =
>> :userType ORDER BY u.username
>> I get the following exception;
...
>> I also tried a LEFT JOIN which I think would do what I want and it certainly
>> picks up the rows as I would anticipate from the SQL log, but the to-many
>> doesn't seem to freshen.
...
>>> IIRC there's also an OUTER fetch join that handles this case.
>> ...
>>>>> yeah, you can use fetch joins (since 3.0). something like
>>>>> select f FROM FooBar f inner join fetch f.bars b ...
>> ...
>>>> I tried that and it produces a query which excludes objects from the
>>>> result-set where there is nothing in the join. The behaviour of
>>>> "query.addPrefetch(..)" would be to run two SELECT database queries to get
>>>> the additional data and freshen-up the relationship if my thinking is
>>>> correct.
___
Andrew Lindesay
www.silvereye.co.nz
