You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Michael Shea <mi...@nitido.com> on 2008/08/25 20:50:21 UTC
Using SQLTemplate to prefetch relationships.
Hi,
I am attempting to use a SQLTemplate to execute a query and prefetch
relationships. I have been unable, so far, to get any relationships that
are one-to-many to be prefetched. Is there any further documentation
about prefetching dependencies with SQLTemplate? So far, all I have seen
is this:
http://cayenne.apache.org/doc/prefetching.html
This basically just indicates that SQLTemplate uses JOINT semantics to
prefetch, which makes sense. Presumably, the query must manually
retrieve all the desired relationships, and I should call
template.addPrefetch(...) for every relationship I want retrieved?
How should I retrieve the actual properties of the relationship objects?
So far, I have been using:
select ...
#result('granteeId' 'String' )
...
where granteeId is a property of an object related to the root object of
the query.
However, I get back errors like this:
org.apache.cayenne.CayenneRuntimeException: [v.2.0.4 October 8 2007]
Null value for 'granteeId'. Snapshot:
org.apache.cayenne.DataRow@d61aef[values={permissionItemId=aa869cb60ca18870:70f1774a:11bfb2de554:-8000,
granteeId=null, permissionType=null, isGrantable=null},
version=-9223372036854775793, replaces=-9223372036854775808]. Prefix: null
at
org.apache.cayenne.access.ObjectResolver.createObjectId(ObjectResolver.java:278)
...
When I run the query myself, manually, I can see that the value of
granteeId returned by the query isn't null.
I have also tried calling template.setFetchingDataRows( false ) and then
calling context.objectsFromDataRows() on the result of executing the
query, but so far that has just generated me one object per datarow (so
if a root object has 4 related dependent objects, I end up getting back
4 distinct root objects, rather than 1 root with 4 objects in the
appropriate relationship).
Any suggestions? If this is unclear, please let me know and I will try
to clarify =)
Oh, I am using version 2.0.4 of Cayenne.
Thanks!
Mike Shea.
Re: Using SQLTemplate to prefetch relationships.
Posted by Andrus Adamchik <an...@objectstyle.org>.
Actually there is a trick involved in naming columns from the joined
tables. So you'd still call 'addPrefetch', but also prefix each joined
column with a DB relationship name. E.g.:
#result('granteeId' 'String' 'myRel.granteeId')
(Note to self - document this under "Advanced SQLTemplate")
Andrus
On Aug 25, 2008, at 9:58 PM, Mike Kienenberger wrote:
> I'm not an expert on prefetching.
>
> However, my guess is that you need to use addPrefetch() and let the
> framework do the right thing rather than trying to put it into your
> sql-template.
>
>
> On 8/25/08, Michael Shea <mi...@nitido.com> wrote:
>> Hi,
>>
>> I am attempting to use a SQLTemplate to execute a query and prefetch
>> relationships. I have been unable, so far, to get any relationships
>> that are
>> one-to-many to be prefetched. Is there any further documentation
>> about
>> prefetching dependencies with SQLTemplate? So far, all I have seen
>> is this:
>>
>> http://cayenne.apache.org/doc/prefetching.html
>>
>> This basically just indicates that SQLTemplate uses JOINT semantics
>> to
>> prefetch, which makes sense. Presumably, the query must manually
>> retrieve
>> all the desired relationships, and I should call
>> template.addPrefetch(...)
>> for every relationship I want retrieved?
>>
>> How should I retrieve the actual properties of the relationship
>> objects? So
>> far, I have been using:
>>
>> select ...
>> #result('granteeId' 'String' )
>> ...
>>
>> where granteeId is a property of an object related to the root
>> object of
>> the query.
>>
>> However, I get back errors like this:
>>
>> org.apache.cayenne.CayenneRuntimeException: [v.2.0.4
>> October 8 2007] Null value for 'granteeId'. Snapshot:
>> org
>> .apache
>> .cayenne
>> .DataRow@d61aef[values={permissionItemId=aa869cb60ca18870:70f1774a:
>> 11bfb2de554:-8000,
>> granteeId=null, permissionType=null, isGrantable=null},
>> version=-9223372036854775793,
>> replaces=-9223372036854775808]. Prefix: null
>> at
>> org
>> .apache
>> .cayenne.access.ObjectResolver.createObjectId(ObjectResolver.java:
>> 278)
>> ...
>> When I run the query myself, manually, I can see that the value of
>> granteeId returned by the query isn't null.
>>
>> I have also tried calling template.setFetchingDataRows( false ) and
>> then
>> calling context.objectsFromDataRows() on the result of executing
>> the query,
>> but so far that has just generated me one object per datarow (so if
>> a root
>> object has 4 related dependent objects, I end up getting back 4
>> distinct
>> root objects, rather than 1 root with 4 objects in the appropriate
>> relationship).
>>
>> Any suggestions? If this is unclear, please let me know and I will
>> try to
>> clarify =)
>>
>> Oh, I am using version 2.0.4 of Cayenne.
>>
>> Thanks!
>>
>>
>> Mike Shea.
>>
>
Re: Using SQLTemplate to prefetch relationships.
Posted by Mike Kienenberger <mk...@gmail.com>.
I'm not an expert on prefetching.
However, my guess is that you need to use addPrefetch() and let the
framework do the right thing rather than trying to put it into your
sql-template.
On 8/25/08, Michael Shea <mi...@nitido.com> wrote:
> Hi,
>
> I am attempting to use a SQLTemplate to execute a query and prefetch
> relationships. I have been unable, so far, to get any relationships that are
> one-to-many to be prefetched. Is there any further documentation about
> prefetching dependencies with SQLTemplate? So far, all I have seen is this:
>
> http://cayenne.apache.org/doc/prefetching.html
>
> This basically just indicates that SQLTemplate uses JOINT semantics to
> prefetch, which makes sense. Presumably, the query must manually retrieve
> all the desired relationships, and I should call template.addPrefetch(...)
> for every relationship I want retrieved?
>
> How should I retrieve the actual properties of the relationship objects? So
> far, I have been using:
>
> select ...
> #result('granteeId' 'String' )
> ...
>
> where granteeId is a property of an object related to the root object of
> the query.
>
> However, I get back errors like this:
>
> org.apache.cayenne.CayenneRuntimeException: [v.2.0.4
> October 8 2007] Null value for 'granteeId'. Snapshot:
> org.apache.cayenne.DataRow@d61aef[values={permissionItemId=aa869cb60ca18870:70f1774a:11bfb2de554:-8000,
> granteeId=null, permissionType=null, isGrantable=null},
> version=-9223372036854775793,
> replaces=-9223372036854775808]. Prefix: null
> at
> org.apache.cayenne.access.ObjectResolver.createObjectId(ObjectResolver.java:278)
> ...
> When I run the query myself, manually, I can see that the value of
> granteeId returned by the query isn't null.
>
> I have also tried calling template.setFetchingDataRows( false ) and then
> calling context.objectsFromDataRows() on the result of executing the query,
> but so far that has just generated me one object per datarow (so if a root
> object has 4 related dependent objects, I end up getting back 4 distinct
> root objects, rather than 1 root with 4 objects in the appropriate
> relationship).
>
> Any suggestions? If this is unclear, please let me know and I will try to
> clarify =)
>
> Oh, I am using version 2.0.4 of Cayenne.
>
> Thanks!
>
>
> Mike Shea.
>